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>