Oracle NEXTVAL function tips

The Oracle NEXTVAL function is used to retrieve the next value in a sequence. The Oracle NEXTVAL function must be called before calling the CURRVAL function, or an error will be thrown.  No current value exists for the sequence until the Oracle NEXVAL function has been called at least once.

SQL> create sequence pubs1;

Sequence created.

SQL> select pubs1.nextval from dual;

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

SQL> select pubs1.nextval from dual;

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

When creating a sequence, there is a lot of flexibility in how the sequence generates the next number using the Oracle NEXTVAL function:

SQL> create sequence pubs2
  2  start with 8
  3  increment by 2
  4  maxvalue 10000
  5  cycle
  6  cache 5; 

Sequence created.

SQL> select pubs2.nextval from dual; 

   NEXTVAL
----------
         8 

In the sequence example, the Oracle NEXTVAL function would return 10 as the next number in the sequence.

 

---