0

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.

2 Answers 2

4

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

1

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.