This discussion is archived
7 Replies Latest reply: Aug 4, 2013 3:35 AM by JohnWatson RSS

how to access CURRVAL NEXTVAL on IDENTITY Oracle 12c columns?

user537222 Newbie
Currently Being Moderated

Using Oracle 12c I defined  an identity column:

create table test(col1 number generated by default as identity (start with 1 increment by 1), col2 varchar2(100));

Oracle then generates a sequence for my identity column automatically.

Is there a way (function/call/etc..) to access the CURRVAL / NEXTVAL for that sequence given I don't have the sequence name? (without having to query the system catalog).

I'm trying to avoid the manual creation of a sequence for each of the tables that need auto-numbering and let Oracle administer automatically the sequence creation but I need access to the number just inserted in my identity column when inserting in the row, basically the CURRVAL. Looking forward to use IDENTITY columns...

thank you!

  • 1. Re: how to access CURRVAL NEXTVAL on IDENTITY Oracle 12c columns?
    rp0428 Guru
    Currently Being Moderated

    user537222 wrote:

     

    Using Oracle 12c I defined  an identity column:

    create table test(col1 number generated by default as identity (start with 1 increment by 1), col2 varchar2(100));

    Oracle then generates a sequence for my identity column automatically.

    Is there a way (function/call/etc..) to access the CURRVAL / NEXTVAL for that sequence given I don't have the sequence name? (without having to query the system catalog).

    I'm trying to avoid the manual creation of a sequence for each of the tables that need auto-numbering and let Oracle administer automatically the sequence creation but I need access to the number just inserted in my identity column when inserting in the row, basically the CURRVAL. Looking forward to use IDENTITY columns...

    thank you!

    You can do that the way you normally would: by using PL/SQL and the RETURNING clause. Here is an Oracle-base article that shows the use of a RETURNING clause. Just ignore the part that explicitly inserts the sequence value since the IDENTITY column will do that for you.j

    http://www.oracle-base.com/articles/misc/dml-returning-into-clause.php

    >

    SET SERVEROUTPUT ON

    DECLARE

      l_id t1.id%TYPE;

    BEGIN

      INSERT INTO t1 VALUES (t1_seq.nextval, 'FOUR')

      RETURNING id INTO l_id;

      COMMIT;

     

      DBMS_OUTPUT.put_line('ID=' || l_id);

    END;

    /

    ID=4

     

    PL/SQL procedure successfully completed.

    >

  • 2. Re: how to access CURRVAL NEXTVAL on IDENTITY Oracle 12c columns?
    user537222 Newbie
    Currently Being Moderated

    I was trying to find if Oracle delivered a way to access this directly like a variation of NEXTVAL and CURRVAL, since I haven't been able to find something in the documentation  it seems that is not the case.

    The workaround above will work if we modify the inserts in the application.....

    We use triggers and sequences so I was looking to replace the current code that uses NEXTVAL or CURRVAL.

    I'm guessing we won't be the only ones trying to replace the trigger-sequence combination.

    If anyone knows if Oracle delivered something specific for IDENTITY columns  it would be very nice to know. Otherwise the workaround suggested will work. I will wait a little bit more for more possible answers before marking it as the correct answer.

    thanks!!

  • 3. Re: how to access CURRVAL NEXTVAL on IDENTITY Oracle 12c columns?
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated

    I don't think there's an official API as yet, but the RETURNING clause suggested by rp0428 is the way you ought to handle your requirement - don't do a round trip if you don't need to.

     

    If you need to examine the sequence details for a table identity for other reasons then it looks as if the sequence name is ISEQ$$_{nnnnn} where the {nnnnn} is the object_id (not data_object_id) of the table.

     

    Regards

    Jonathan Lewis

  • 4. Re: how to access CURRVAL NEXTVAL on IDENTITY Oracle 12c columns?
    rp0428 Guru
    Currently Being Moderated

    user537222 wrote:

     

    I was trying to find if Oracle delivered a way to access this directly like a variation of NEXTVAL and CURRVAL, since I haven't been able to find something in the documentation  it seems that is not the case.

    The workaround above will work if we modify the inserts in the application.....

    We use triggers and sequences so I was looking to replace the current code that uses NEXTVAL or CURRVAL.

    I'm guessing we won't be the only ones trying to replace the trigger-sequence combination.

    If anyone knows if Oracle delivered something specific for IDENTITY columns  it would be very nice to know. Otherwise the workaround suggested will work. I will wait a little bit more for more possible answers before marking it as the correct answer.

    thanks!!

    It sounds like you are using PL/SQL code to create PARENT/CHILD rows and you want to get the sequence value of a parent you create so you can create the child records.

     

    If so, you do that the way it has always been done, like I showed in my first reply. You create a parent row and use the RETURNING clause to get the new key value and then use that value when you create the child rows. That is how you do it with current code that assigns the keys in a trigger using a sequence.

     

    I think you would agree that if you just insert 10 PARENT rows then there isn't any way to use CURRVAL to find out the sequence number of the first nine rows; CURRVAL would only represent the last row.

     

    So it isn't clear to me why you need to manually access CURRVAL for the sequence.

     

    No - you can NOT simply rename the sequence Oracle creates for the identity column. Well - you can certainly rename it but then your identity column won't work because the metadata is still referring to the original name. And you can't change the original name by modifying the DEFAULT for the identity column since the user can't specify the DEFAULT for an IDENTITY column.

     

    The following appears to work just fine

    >

    CREATE TABLE SCOTT.DEPT_IDENTITY
    (
      ID     NUMBER GENERATED AS IDENTITY (START WITH 23 CACHE 20),
      NUM1   NUMBER                                 DEFAULT "SCOTT"."DEPT_COPY_SEQ"."NEXTVAL",
      NUM2   NUMBER                                 DEFAULT "SCOTT"."DEPT_COPY_SEQ"."NEXTVAL",
      NUM3   NUMBER                                 DEFAULT "SCOTT"."DEPT_COPY_SEQ"."NEXTVAL",
      NUM4   NUMBER                                 DEFAULT "SCOTT"."DEPT_COPY_SEQ"."NEXTVAL",
      DNAME  VARCHAR2(14 BYTE),
      LOC    VARCHAR2(13 BYTE)
    )

    INSERT INTO DEPT_IDENTITY (DNAME) VALUES ('TEST1')

    INSERT INTO DEPT_IDENTITY (DNAME) VALUES ('TEST2')

     

    SELECT * FROM DEPT_IDENTITY

     

    ID,NUM1,NUM2,NUM3,NUM4,DNAME,LOC

    841,43,43,43,43,TEST1,

    842,44,44,44,44,TEST2,

     

    SELECT DEPT_COPY_SEQ.CURRVAL FROM DUAL

     

    CURRVAL

    44

     

    SELECT ISEQ$$_92488.CURRVAL FROM DUAL

     

    CURRVAL

    842

    >

    NOTE:  dropping the table does NOT drop the sequence that Oracle created to support the identity column even if you use CASCADE CONSTRAINTS.

    If fact, I'm haven't figured out yet HOW to drop them. If you just issue a DROP as either the table owner or SYS you get an exception.

    >

    ORA-32794: cannot drop a system-generated sequence

    >

    Probably a bug.

  • 5. Re: how to access CURRVAL NEXTVAL on IDENTITY Oracle 12c columns?
    user537222 Newbie
    Currently Being Moderated

    Thanks it is useful to know how the sequence is named to access it directly just in case.

  • 6. Re: how to access CURRVAL NEXTVAL on IDENTITY Oracle 12c columns?
    user537222 Newbie
    Currently Being Moderated

    Thanks to rp0428 for answering so fast and providing good examples.

  • 7. Re: how to access CURRVAL NEXTVAL on IDENTITY Oracle 12c columns?
    JohnWatson Guru
    Currently Being Moderated

    rp0428 wrote:

     

    <snip>

    >

    NOTE:  dropping the table does NOT drop the sequence that Oracle created to support the identity column even if you use CASCADE CONSTRAINTS.

    If fact, I'm haven't figured out yet HOW to drop them. If you just issue a DROP as either the table owner or SYS you get an exception.

    >

    ORA-32794: cannot drop a system-generated sequence

    >

    Probably a bug.

     

    Sequences for identity columns are protected as long as the table has been only renamed, not purged:

     

    orclz>

    orclz> create table t1(c1 number generated as identity);

     

    Table created.

     

    orclz> select object_name,object_type from user_objects;

     

    OBJECT_NAME          OBJECT_TYPE

    -------------------- --------------------

    T1                   TABLE

    ISEQ$$_90487         SEQUENCE

     

    orclz> drop table t1;

     

    Table dropped.

     

    orclz> drop sequence ISEQ$$_90487;

    drop sequence ISEQ$$_90487

                  *

    ERROR at line 1:

    ORA-32794: cannot drop a system-generated sequence

     

     

    orclz> purge user_recyclebin;

     

    Recyclebin purged.

     

    orclz> select object_name,object_type from user_objects;

     

    no rows selected

     

    orclz>

     

    Makes sense, but it would be nice if the sequence were renamed on dropping the table, as indexes are.