How to get next value of Sequence dynamically in Oracle

Posted on

Question :

I used to a SQL guy and trying my hands on Oracle front.

I am getting dynamic values of sequence in a variable. I like to fetch next value for that sequence object in some output variable. How to handle this in Oracle.

I wrote something same for SQL like this:

SET @v_SQLString = 'SELECT @v_new_seq = next value for '+@v_table_name
SET @v_ParmDefinition = '@v_new_seq NUMERIC(19) OUTPUT'

EXECUTE sp_executesql
    @v_SQLString,
    @v_ParmDefinition,
    @v_new_seq = @v_new_seq OUTPUT

Looking for something same in Oracle.

Answer :

My_Sequence_Name.NextVal – check here and here. Psoug is a great site for Oracle.

I have found answer in same forum somewhere. Posting it here again so it can be useful to somebody.

v_lc_table_name NVARCHAR2(30);
v_new_id    NUMBER(19,0);

v_select := 'select '||v_lc_table_name||'.nextval from dual';
      execute immediate v_select into v_new_id;

Leave a Reply

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