Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Rahul.

Asked: August 04, 2017 - 10:28 am UTC

Last updated: August 04, 2017 - 3:13 pm UTC

Version: 12.1

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I just wanted to know,
How to find next value from identity for any particular column ?

with LiveSQL Test Case:

and Chris said...

You can lookup the name of the underlying sequence by checking the data_default in *_tab_cols. Then find the current value for this and add the increment value.

create table t (
  x int generated as identity
);

insert into t values (default);

select * from t;

X  
1  

select column_name, data_default from user_tab_cols
where  table_name = 'T';

COLUMN_NAME  DATA_DEFAULT                     
X            "CHRIS"."ISEQ$$_197255".nextval 

select ISEQ$$_197255.currval + increment_by 
from   user_sequences
where  sequence_name = 'ISEQ$$_197255';

ISEQ$$_197255.CURRVAL+INCREMENT_BY  
2

insert into t values (default);

select * from t;

X  
1  
2 


Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.