Oracle Auto Increment Columns – Part 2

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.

20 thoughts on “Oracle Auto Increment Columns – Part 2”

  1. Pingback: Life After Coffee » How to Create Auto Increment Columns in Oracle

  2. And what if I do :

    INSERT INTO "_SMDBA_"."_PERSONNEL_" (CODE,FNAME,NAME) VALUES ('SUPERTOTO','Super','TOTO');

    The following won’t work :

    SELECT "_SMDBA_"."_PERSONNEL__SEQUENCE".CURRVAL FROM DUAL;

    SELECT "_PERSONNEL__SEQUENCE".CURRVAL FROM DUAL;

    SELECT "_SMDBA_._PERSONNEL__SEQUENCE".CURRVAL FROM DUAL;

  3. Dodot,

    The important detail is you cannot grab a currval unil you have select nextval. I’m guessing that’s why you’re getting the failures.

    Hope this helps.

  5. That’s as wrong as Matt’s original code. What if another session inserts a row to the table between your insert and selecting currval. You need to read about SELECT..RETURNING.

  6. Neil,

    nextval and currval are local to your session. So let’s say you select nextval and get the value 27 from the sequence. You can now select currval and get 27 since it’s the current value for your session.

    Now let’s say I log in and select nextval from the same sequence and get 28. I now have a currval of 28 for my session, but you will still see a currval of 27 in yours.

    You can test this by opening two sessions to a database and grab nextval in both then select currval in both. It took me a long time to realize how all this works out, but it does.

  7. I have a table with a two-columns key (A, B).

    How would be possible to create separate auto-increment values of B for each value of A?

    E.g.
    A B
    ———–
    1 1
    1 2
    1 3
    2 1
    2 2
    3 1
    3 2

    And now I want to automatically assign 4 to B for a new row with A=1.

    Any ideas?

  8. Jesus,

    This is a somewhat strange way to key a table, but here’s how I’d do it.

    Through either the logic on the insert or a trigger build your insert like this:

    insert into mytable (a, b) values (1, (select max(b)+1 from mytable where a=1));

    This should work but won’t be too efficient if you’re talking about a huge volume of inserts on this table. You might want to consider keying the table differently.

    Hope this helps.

  9. small correction:

    using this way:

    insert into mytable (a, b) values (1, (select NVL(max(b),0)+1 from mytable where a=1));

    will ensure it works for the first time and everytime 🙂
    as Jon rightly said, this query will kill the performance.

  10. Is there a way to have the seqeuncer to start afresh(from 0 or from the previous value of the table in question) for each table, for instance I have 2 tables with auto_increment fields.I insert into table 1 and then into table 2 using seq_name.nextval.

    Now the id for T1 will be 1 and id for T2 will start from 2

    ofcourse I can avoid this situation by having 2 sequencer one each for T1 and T2, but just wanted to check if there is a alternative

  11. Saravanan,

    The only way I know to do this is to create a sequence for each table. There are few instances where you would want to use one sequence for two different tables. Sequences take up very little resources so there is no reason not to have one per table.

  12. i create a table. in this table one coloum id is primary key . i want to increment this value automaticaly. user use this application without insert these value. in database this value automaticaly increment.

  13. Hi Jon,

    Thanks for your valuable tips. My table A has two columns which are ID and Name. I used sequence to increment the ID number sequentially. “Name” column is with not null constraint. My problem in here is that if a user insert data into table “A” without giving a value for “Name” column then the trigger will fire and sequence value will be increment by 1. in the next time if I insert name value,then ID numbers are not sequential. How I can handle this problem from DB level.

    Thanks,
    Krishantha.

  14. Krishantha,

    How are you activating the trigger? You might be able to prevent false incrementing by changing to an ‘after insert’ trigger. More likely you’ll want to make the trigger check the name column and raise an error if it’s null.

    Hope this helps.

  15. Hi Jon,

    Here is my trigger.

    CREATE TABLE test1 (id NUMBER PRIMARY KEY,name VARCHAR2(30) NOT NULL)
    /
    CREATE SEQUENCE test1_sequence START WITH 1 INCREMENT BY 1
    /
    CREATE OR REPLACE TRIGGER test1_trigger
    AFTER INSERT
    ON test1
    REFERENCING NEW AS NEW
    FOR EACH ROW
    BEGIN
    SELECT test1_sequence.nextval INTO :NEW.ID FROM dual;
    END;
    /
    commit;
    /

    After trying to insert data to the table with NULL name value. ID sequence got changed.

    SQL> select * from test1;

    ID NAME
    ———- ——————————
    1 kkTEST
    2 kkTEST11
    3 kkTEST12
    5 kkTEST14555

    I update the “before insert” as “after insert” and executed the trigger. This time I got the following error.

    ORA-04084: cannot change NEW values for this trigger type

    Any help will be greatly appreciated.

    Thanks,
    Krishantha.

  16. Sorry little typo error. In the above post. “AFTER INSERT” on Trigger definition should be be changed as “BEFORE INSERT”

    Thanks.
    Krishantha.

  17. This code is not safe, if you have a database that is being updated by multiple processes you will receive the wrong value…

  18. Quick,

    Actually, Oracle is clever enough to maintain currval for your existing session so you will get the correct value even if someone else is accessing or updating the sequence.

  19. I use the example as I want to auto increment id for a database but it works fine the first time, but many occasions after, the id increments at different values like by 2 or even does not start at 1 and when I checked the sequence it starts with 1 and increments by one. I don’t know what the problem is but I have redone it a couple of times even closed oracle and reopened but that does not help. What could be the problem?

  20. Is there anyway to perform a JDBC batch insert and retrieve all of the key sequences from each row at the same time?

Leave a Reply

Your email address will not be published. Required fields are marked *