Interesting Oracle sequence issue

Our rookie had a simple task on the SQL lab of the university he attends. Take a column in table A and copy it to table B, while adding a column id to B from a sequence C.

This seems like a matter of a single relatively simple SQL statement, and so thought their teacher. The solution we all thought to be simple looks like:

INSERT INTO B (ID, COL) VALUES (SELECT C.nextval, COL FROM A)

Nice and easy. This seems that Oracle only evaluates a the sequence operations in a statement once, thus when you take the next value from a sequence you will always get the same value within the statement. This will either result in a constraint violation (if you have one defined) or table A’s ID column filled with the same number.

So solutions like:

INSERT INTO B (ID, COL) VALUES (SELECT (SELECT C.nextval FROM DUAL), COL FROM A)

would not work either, as the expression is still evaluated once.

I see two solutions to this really simple problem, either create a trigger on table B for this excercise, which I consider an overkill, or use a LOOP. Oracle states on it’s documentation, that the loop content on every iteration.

3 thoughts on “Interesting Oracle sequence issue”

  2. For DB2 it works properly as well.

    create table a (col char(10))
    create table b (id int, col char(10))
    create sequence c
    insert into a values('aa'),('bb'),('cc')

    select next value for c, a.col from a

    shows the required

    1 aa
    2 bb
    3 cc

    and

    insert into b select next value for c, a.col from a

    inserts the values in the table correctly.

  3. it’s funny lajos, I was trying to do this but always used the sytax containing the word “values”… of course it didn’t work 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.