Oracle Auto Increment Columns – Part 2 - lifeaftercoffee.com

How they can retrieve the value of an auto increment column for use later in their code. 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

Sources: lifeaftercoffee.com-part2