Learning is not a spectator sport

It is commonplace to use a sequence to populate a surrogate key in your table. Whether its a manually created sequence or an IDENTITY column (which is still backed by a sequence), most of us know that we can use the RETURNING clause to collect the value that was used on INSERT.

SQL> insert into people values (seq.nextval, 'Connor')
  2  returning pk into :n;

1 row created.

SQL> print n

         N
----------
    341223

Now that is all well and good, but what if you forgot? What if you have just done an INSERT and forgot the RETURNING clause?

SQL> insert into people values (seq.nextval, 'Connor');

1 row created.

You might think that you are stuck. But all is not lost. A little known features of sequences is that you can use CURRVAL to get the last value used for a sequence.

SQL> select seq.currval from dual;

   CURRVAL
----------
    341224

Even nicer is that this value is session specific. If I go into a new session and run this

SQL> select seq.nextval from dual connect by level <= 10;

   NEXTVAL
----------
    341225
    341226
    341227
    341228
    341229
    341230
    341231
    341232
    341233
    341234

10 rows selected.

and then come back to my original session, we’re still good

SQL> select seq.currval from dual;

   CURRVAL
----------
    341224

Ho Ho Ho! Merry Christmas.