jueves, 5 de marzo de 2009

Como resetear y como obtener el próximo valor de una secuencia

Varias veces me preguntaron como reiniciar (resetear) una secuencia de modo de que comience a generar valores desde el principio nuevamente. Esto suele ser util en la etapa de testing de aplicativos ya que en las pruebas se necesita probar desde cero varias veces. Oracle, al menos hasta ahora, no provee un forma nativa de reiniciar una secuencia (por ejemplo algo como alter sequence reset) pero si permite cambiar el incremento, que como default es 1, y setearlo a un valor negativo, por lo tanto en el próximo nextval va a restar (decrementar) el valor. Si hacemos un decremento que logre que pase de su valor actual a 1 conseguiriamos hacer que el contador se resetee. Les voy a mostrar como hacerlo creando un stored procedure:


rop@DESA10G> create sequence seq;

Secuencia creada.

rop@DESA10G> create or replace procedure reset_seq( p_seq_name in varchar2 ) is
2 l_val number;
3 begin
4 execute immediate
5 'select ' || p_seq_name || '.nextval from dual' INTO l_val;
6 execute immediate
7 'alter sequence ' || p_seq_name || ' increment by -' || l_val || ' minvalue 0';
8 execute immediate
9 'select ' || p_seq_name || '.nextval from dual' INTO l_val;
10 execute immediate
11 'alter sequence ' || p_seq_name || ' increment by 1 minvalue 0';
12 end;
13 /

Procedimiento creado.

Ahora voy a incrementar el valor de la secuencia varias veces:

rop@DESA10G> select seq.nextval from dual;

NEXTVAL
----------
1

rop@DESA10G> select seq.nextval from dual;

NEXTVAL
----------
2

rop@DESA10G> /

NEXTVAL
----------
3

rop@DESA10G> /

NEXTVAL
----------
4

rop@DESA10G> /

NEXTVAL
----------
5

rop@DESA10G> /

NEXTVAL
----------
6

Si hicieramos nextval el próximo valor será el 7. Ahora vamos a reiniciar la secuencia usando el procedimiento creado mas arriba.

rop@DESA10G> exec reset_seq('SEQ');

Procedimiento PL/SQL terminado correctamente.

rop@DESA10G> select seq.nextval from dual;

NEXTVAL
----------
1

rop@DESA10G> /

NEXTVAL
----------
2

Como observamos en el ejemplo, la secuencia comenzó a generar valores nuevamente.


Ahora voy a mostrarles como obtener el próximo valor de una secuencia. En principio uno piensa en obtener ese dato desde una vista de catalogo, pero si la sequencia utiliza cache mayor a 0 (el default es 20) no nos va mostrar el próximo valor real sino que nos va a mostrar el próximo no cacheado. Para ser mas claro les voy a mostrar un ejemplo
 
rop@DESA10G> create sequence seq;

Secuencia creada.

rop@DESA10G> select seq.nextval from dual;

NEXTVAL
----------
1

rop@DESA10G> /

NEXTVAL
----------
2

rop@DESA10G> /

NEXTVAL
----------
3

rop@DESA10G> /

NEXTVAL
----------
4

rop@DESA10G> /

NEXTVAL
----------
5


rop@DESA10G> select * from user_sequences where sequence_name = 'SEQ';

SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ---------- ---------- ------------ - - ---------- -----------
SEQ 0 1.0000E+27 1 N N 20 21

1 filas seleccionadas.


Consultando la vista USER_SEQUENCES vemos que la columna last_number podría servirnos para saber cual será el próximo valor, pero vemos que el valor es 21 y el próximo valor deberia ser 6, entonces no nos sirve esa vista para lo que queremos. Para poder conocer exactamente el valor siguiente que nos entregará una secuencia tenemos que usar v$_sequences que no es visible desde ningun usuario y no tiene sinonimo publico. Lo que voy a hacer es presentar esa vista a todos los usuarios para que se pueda consultar:



sys@ROP10G> desc v$_sequences
Name Null? Type
----------------------------------------------------- -------- ------------------------------
SEQUENCE_OWNER VARCHAR2(64)
SEQUENCE_NAME VARCHAR2(1000)
OBJECT# NUMBER
ACTIVE_FLAG VARCHAR2(1)
REPLENISH_FLAG VARCHAR2(1)
WRAP_FLAG VARCHAR2(1)
NEXTVALUE NUMBER
MIN_VALUE NUMBER
MAX_VALUE NUMBER
INCREMENT_BY NUMBER
CYCLE_FLAG VARCHAR2(1)
ORDER_FLAG VARCHAR2(1)
CACHE_SIZE NUMBER
HIGHWATER NUMBER
BACKGROUND_INSTANCE_LOCK VARCHAR2(1)
INSTANCE_LOCK_FLAGS NUMBER


sys@ROP10G> create view v$sequences as select * from v$_sequences;

View created.

sys@ROP10G> create public synonym v$sequences for sys.v$sequences;

Synonym created.

sys@ROP10G> grant select on v$sequences to public;

Grant succeeded.

Ahora que ya publicamos la vista podemos hacer la siguiente consulta para obtener el próximo valor:

rop@DESA10G> ed
Escrito file afiedt.buf

1 select b.object_name,a.nextvalue
2 from v$sequences a, user_objects b
3 where a.object# = b.object_id and
4* b.object_name = 'SEQ'
rop@DESA10G> /

OBJECT_NAME NEXTVALUE
----------------------------
SEQ 6

Como se observa, el valor retornado fue lo que esperabamos. Ahora verificamos haciendo un nextval:


rop@DESA10G> select seq.nextval from dual;

NEXTVAL
----------
6

rop@DESA10G>


Efectivamente el próximo valor era 6. No se si esto pueda tener gran utilidad pero la idea era mostrarles como hacerlo.

3 comentarios:

  1. Muy bueno amigo, realmente me ayudaste con el tema de resetear el start with de una sequence....

    ResponderEliminar
  2. Conciso y puntual.
    Excelente

    ResponderEliminar
  3. Excelente post!!!! muchas gracias

    ResponderEliminar