Using the same sequence twice in a PL/SQL statement

oracleplsqlsequence

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.

Best Answer

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

Related Solutions

Using a sequence created in the same script in Oracle

Something like this:

declare 
   counter integer;
begin
   select count(*) 
      into counter
   from user_sequences
   where sequence_name = 'MY_SEQUENCE';

   if (counter = 0) then 
      execute immediate 'create sequence my_sequence';
   end if;
end;
/

insert into my_table (id, some_col)
values
(my_sequence.nextval, 42)
/

EDIT: If the insert is in the same block as the create then it will need to be done as an execute immediate as well else the Sequence won't be recognised. If they're in separate blocks then it should work fine.

Reset every Oracle sequence to the MAX value of its target table PK

I made this script as I did not find a script online that dynamically sets all my sequences to the current highest ID. Tested on Oracle 11.2.0.4.

DECLARE
  difference         INTEGER;
  sqlstmt            VARCHAR2(255) ;
  sqlstmt2           VARCHAR2(255) ;
  sqlstmt3           VARCHAR2(255) ;
  sequenceValue      NUMBER;
  sequencename       VARCHAR2(30) ;
  sequencelastnumber INTEGER;
  CURSOR allseq
  IS
     SELECT sequence_name, last_number FROM user_sequences ORDER BY sequence_name;
BEGIN
  DBMS_OUTPUT.enable(32000) ;
  OPEN allseq;
  LOOP
    FETCH allseq INTO sequencename, sequencelastnumber;
    EXIT
  WHEN allseq%NOTFOUND;
    sqlstmt  := 'ALTER SEQUENCE ' || sequencename || ' INCREMENT BY ';
    --Assuming: <tablename>_id is <sequencename>
    sqlstmt2 := 'select (nvl(Max(ID),0) - :1)+1 from ' || SUBSTR(sequencename, 1, LENGTH(sequencename) - 3) ;
    --DBMS_OUTPUT.PUT_LINE(sqlstmt2);
    --Attention: makes use of user_sequences.last_number --> possible cache problems!
    EXECUTE IMMEDIATE sqlstmt2 INTO difference USING sequencelastnumber;
    IF difference > 0 THEN
      DBMS_OUTPUT.PUT_LINE('EXECUTE IMMEDIATE ' || sqlstmt || difference) ;
      EXECUTE IMMEDIATE sqlstmt || difference;
      sqlstmt3 := 'SELECT ' || sequencename ||'.NEXTVAL from dual';
      DBMS_OUTPUT.PUT_LINE('EXECUTE IMMEDIATE ' || sqlstmt3 || ' INTO sequenceValue') ;
      EXECUTE IMMEDIATE sqlstmt3 INTO sequenceValue;
      DBMS_OUTPUT.PUT_LINE('EXECUTE IMMEDIATE ' || sqlstmt || 1) ;
      EXECUTE IMMEDIATE sqlstmt || 1;
      DBMS_OUTPUT.PUT_LINE('') ;
    END IF;
  END LOOP;
  CLOSE allseq;
END;

Best Answer

Related Solutions

Using a sequence created in the same script in Oracle

Reset every Oracle sequence to the MAX value of its target table PK

Related Question