4

I have a table column declared like this:

file_id number(10) generated always as identity primary key,

Is it possible to programatically get a currval/nextval from its backing sequence without actually looking into the SYS. tables to obtain the name of the sequence and then using execute immediate on that name?

Matěj Zábský
  • 16,909
  • 15
  • 69
  • 114

1 Answers1

6

Is it possible to programatically get a currval/nextval from its backing sequence without actually looking into the SYS

Yes, if you really need to do so. You can simply look up that sequence name in the USER_SEQUENCES data dictionary view, or better yet USER_TAB_IDENTITY_COLS data dictionary view, and reference it in your query. Here is an example:

create table t1(
  c1 number  generated always as identity primary key
);

insert into t1 values(default);

select *  from t1;

C1
-----
    1

In my case, a sequence's name Oracle has created for the identity column is ISEQ$$_92984. 

select "ISEQ$$_92984".nextval from dual;

NEXTVAL
-------
     2

insert into t1 values(default);

select * from t1;

 C1
 ---------
         1
         3
Nick Krasnov
  • 26,886
  • 6
  • 61
  • 78
  • This is basically the same as looking into sys tables (since you need to first extract name of the sequence into a variable and then query the sequence by variable name), assuming I only know name of the table and column at the time of writing the plsql code. I was hoping for something simple, like TABLE.COLUMN.NEXTVAL :) – Matěj Zábský May 22 '17 at 12:47