Using the same sequence twice in a PL/SQL statement

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 first_id and 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.

This is the expected behaviour of nextval as documented:


Within a single SQL statement containing a reference to NEXTVAL, Oracle increments the sequence once:

• For each row returned by the outer query block of a SELECT statement

[...] If any of these locations contains more than one reference to NEXTVAL, then Oracle increments the sequence once and returns the same value for all occurrences of NEXTVAL.

This means that you won't be able to use plain SQL to overcome this limitation: you will need some PL/SQL. Either a trigger that populates both fields or a function that wraps the sequence call. Here's an example with such a function with 11g:

SQL> create sequence table_seq;

Sequence created.

SQL> create table test (sort_nr number, text_id number, unit_id number);

Table created.

SQL> create or replace function getid return number is begin return table_seq.nextval; end;
  2  /

Function created.

SQL> insert into test values (1, getid, getid);

1 row created.

SQL> select * from test;

   SORT_NR    TEXT_ID    UNIT_ID
---------- ---------- ----------
         1          1          2

StackExchange Database Administrators Q#27180, answer score: 9