Tag: pl/sql

Three separate people have commented on my previous article on How to Create Auto Increment Columns in Oracle asking how they can retrieve the value of an auto increment column for use later in their code. Well Daniel, Shaun and Zach, here’s the answer.

After you have referenced sequence.NEXTVAL for a particular sequence (or it is referenced on your behalf by, say, a trigger), you can then reference sequence.CURRVAL to get the value just used for NEXTVAL.

To illustrate this we’ll use the table, sequence, and trigger created in my previous article.

If we insert a row into the table test, the trigger test_trigger automatically calls test_sequence.NEXTVAL.

SQL> insert into test (name) values ('Matt');

1 row created.

We now have test_sequence.CURRVAL available in that session.

SQL> select test_sequence.currval from dual;

CURRVAL
----------
8

In this simple example we can confirm this is the same value just used with this simple query:

SQL> select * from test
where name='Matt';

ID NAME
---------- ------------------------------
8 Matt

Now if we wanted to use this value in another SQL statement, say for an insert on a table which uses this as a foreign key constraint, we can include it on our insert like this:

SQL> insert into tool (owner_id, tool)
values (test_sequence.CURRVAL, 'hammer');

1 row created.

SQL> select * from tool;

OWNER_ID TOOL
---------- ------------------------------
8 hammer

Of course, if we just want to see the value of test_sequence.CURRVAL we can select it from our favorite table dual.

SQL> select test_sequence.currval from dual;

CURRVAL
----------
8

For the table, sequence and trigger used here see my original article on auto increment fields.