Oracle PL/SQL/Sequence/Sequence Properties

A CYCLE sequence

   <source lang="sql">

SQL> SQL> -- A CYCLE sequence SQL> SQL> SQL> CREATE SEQUENCE StudentNumSeq

 2    INCREMENT BY 50000
 3    START WITH 50000
 4    MAXVALUE   99999
 5    NOCACHE
 6    CYCLE;

Sequence created. SQL> SQL> SQL> select studentNumSeq.nextVal from dual;

  NEXTVAL

---

    50000

SQL> select studentNumSeq.nextVal from dual;

  NEXTVAL

---

        1

SQL> select studentNumSeq.nextVal from dual;

  NEXTVAL

---

    50001

SQL> SQL> SQL> drop sequence studentNumSeq; Sequence dropped. SQL> SQL>

      </source>
   
  

A NOCYCLE sequence

   <source lang="sql">

SQL> -- A NOCYCLE sequence SQL> SQL> SQL> CREATE SEQUENCE StudentNumSeq

 2    INCREMENT BY 50000
 3    START WITH 50000
 4    MAXVALUE   99999
 5    NOCACHE
 6    NOCYCLE;

Sequence created. SQL> SQL> SQL> select studentNumSeq.nextVal from dual;

  NEXTVAL

---

    50000

SQL> select studentNumSeq.nextVal from dual; select studentNumSeq.nextVal from dual

                                 *

ERROR at line 1: ORA-08004: sequence STUDENTNUMSEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated

SQL> select studentNumSeq.nextVal from dual; select studentNumSeq.nextVal from dual

ERROR at line 1: ORA-08004: sequence STUDENTNUMSEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated

SQL> SQL> SQL> drop sequence studentNumSeq; Sequence dropped. SQL> SQL>

      </source>
   
  

Sequence cache option

   <source lang="sql">

SQL> SQL> -- the cache option specifies how many sequence values will be stored in memory for faster access SQL> SQL> SQL> CREATE SEQUENCE mySequence

 2      MINVALUE 1
 3      MAXVALUE 999999999999999999999999999
 4      START WITH 1
 5      INCREMENT BY 1
 6      CACHE 20;

Sequence created. SQL> SQL> select mySequence.nextval from dual;

  NEXTVAL

---

        1

SQL> select mySequence.nextval from dual;

  NEXTVAL

---

        2

SQL> select mySequence.nextval from dual;

  NEXTVAL

---

        3

SQL> select mySequence.nextval from dual;

  NEXTVAL

---

        4

SQL> SQL> drop sequence mySequence; Sequence dropped. SQL> SQL>

      </source>