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 :
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;