I wrote the snippet of PL/SQL today:
declare first_id number; second_id number; begin insert into table (sort_nr, text_id, unit_id) values(..., table_seq.nextval, table_seq.nextval) returning text_id, unit_id into first_id, second_id; dbms_output.put_line(first_id); dbms_output.put_line(second_id); end;
and received a unique constaint violation. After futher examination, I discovered that
second_id had the same value in it.
My question is: Is there any restriction calling the same sequence multiple times in the same statement and receiving a subsequent number? From my point of view it seems like the
nextval is invoked only once in the scope of the query and cached.
Just a side note, I cannot change that crappy schema to avoid using the same sequence in two columns.