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.

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.