Database Reference
In-Depth Information
accounting systems (e.g., where perhaps tax laws require all numbers to
exist as transactions).
22.1.3.1
Using the CURRVAL and NEXTVAL Pseudocolumns
Whenever referring to a sequence within a session, use of the CURRVAL
pseudocolumn must be preceded by using the NEXTVAL pseudocolumn.
NEXTVAL initializes the sequence for the current session. The very first
time a sequence is accessed, NEXTVAL will return its initial value; every
subsequent access will return its next incremental value.
Let's use, for example, the ARTIST_ID_SEQ sequence in the MUSIC
schema. This sequence is used to generate a primary key identifier value for
every row in the ARTIST table. Let's try to find its current value using the
following query. See the result in Figure 22.6.
SELECT ARTIST_ID_SEQ.CURRVAL FROM DUAL;
Looking at Figure 22.6, we can see that we get an error. A sequence
must always be initialized for a session using the NEXTVAL pseudocolumn
before the CURRVAL pseudocolumn can be used.
Now let's change the previous command and add a first use of the NEX-
TVAL pseudocolumn into the SQL*Plus Worksheet session before use of
the CURRVAL pseudocolumn on the ARTIST_ID_SEQ sequence. The
following script has its result in Figure 22.7. The actual number you see
may be different if other DML commands are accessing the sequence con-
currently.
SELECT ARTIST_ID_SEQ.NEXTVAL FROM DUAL;
SELECT ARTIST_ID_SEQ.CURRVAL FROM DUAL;
Figure 22.6
NEXTVAL
Required before
CURRVAL to
Initialize the
Sequence for the
Session.
