Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite
1

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.

share|improve this question

3 Answers

up vote 3 down vote accepted

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
share|improve this answer
Perfect, that's evidence. – Michael-O Oct 19 '12 at 10:36
up vote 3 down vote

There is no restriction on using the same sequence multiple times in the same statement. However, all the calls for a single row will return the same value.

SQL> ed
Wrote file afiedt.buf

  1  select foo_seq.nextval, foo_seq.nextval, foo_seq.nextval
  2    from dual
  3* connect by level <= 5
SQL> /

   NEXTVAL    NEXTVAL    NEXTVAL
---------- ---------- ----------
        22         22         22
        23         23         23
        24         24         24
        25         25         25
        26         26         26

If you are trying to generate both a text_id and a unit_id, it would generally make more sense to have two sequences, i.e. test_id_seq and unit_id_seq, that are both referenced in your INSERT statement. You could also populate first_id and second_id before executing the INSERT statement by making separate calls to table_seq.nextval and use the first_id and second_id variables in your INSERT statement rather than referencing the sequence though that is likely a touch less efficient.

share|improve this answer
I am aware of all that the issue is, why is the sequence not incremented the second time? – Michael-O Oct 18 '12 at 20:50
up vote 1 down vote

I'd go with Justin Cave's suggestion and use a separate sequence, but if you really must use the same sequence, then you can change the INCREMENT value to two and then simply add one in your insert like this:

DROP SEQUENCE S1;
DROP TABLE T1;
CREATE SEQUENCE S1 INCREMENT BY 2 START WITH 1;
CREATE TABLE T1 (C1 Number(10), C2 Number(10));

INSERT INTO T1 VALUES (S1.nextval, S1.nextval + 1);
INSERT INTO T1 VALUES (S1.nextval, S1.nextval + 1);
INSERT INTO T1 VALUES (S1.nextval, S1.nextval + 1);
COMMIT;

SELECT * FROM T1;

Another option with it's own limiations would be to use the negative of the sequence value for the second entry.

share|improve this answer
That won't help in that case, the sequence is used in several tables unfortunately. Therefore I cannot increment by 2 and add 1 for the second column. – Michael-O Oct 18 '12 at 20:51
I don't see why that would matter unless you have the additional requirement that the other tables not have gaps. – Leigh Riffel Oct 18 '12 at 20:57
Rethinking the issue, your approach makes sense. But it still does not cover my question: Why does the sequence not generate a second number on the second nextval. – Michael-O Oct 18 '12 at 21:21
1  
@Michael-O I'll counter your question by asking how you would implement the use of nextval when splattered liberally throughout a 1000 line query that consists of all manner of complicated SQL. What order would be enforced when selecting each nextval? It really is impossible to do. – Phil Oct 18 '12 at 22:04
That's obscurity. Using two sequences would be much clearer. – Martin Schröder Oct 19 '12 at 9:22
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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