Tuesday, June 26, 2012

Sequences (Part 4)


This was tested on Oracle 11.2. If you create a sequence and tell it to cycle, it goes back to the start again as soon as it reaches its maximum value:

SQL> create sequence andrew
  2  cache 2
  3  cycle
  4  maxvalue 3
  5  /

Sequence created.

SQL> select andrew.nextval from dual
  2  /

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

SQL> select andrew.nextval from dual
  2  /

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

SQL> select andrew.nextval from dual
  2  /

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

SQL> select andrew.nextval from dual
  2  /

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

SQL>

If you tell the sequence not to cycle and it reaches its maximum value, you get an ORA-08004 when you try to select the next term:

SQL> alter sequence andrew nocycle
  2  /

Sequence altered.

SQL> select andrew.nextval from dual
  2  /

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

SQL> select andrew.nextval from dual
  2  /

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

SQL> select andrew.nextval from dual
  2  /
select andrew.nextval from dual
*
ERROR at line 1:
ORA-08004: sequence ANDREW.NEXTVAL exceeds MAXVALUE
and cannot be instantiated

SQL>

No comments: