I’ve lost track of the number of times I see this sequence (no pun intended) of actions in code:
SELECT my_sequence.nextval INTO :some_variable FROM dual; INSERT INTO my_table VALUES (:some_variable, ....);
My question is always “Why?” What was it that made you so desperate in need of that sequence value that you needed it before you inserted it ? It is simply, easier, and more efficient just to get it back from the insert statement itself.
INSERT INTO MY_TABLE (seq_col, ...) VALUES (my_sequence.nextval, ...) RETURNING seq_col INTO :some_variable;
And with 12c, sequences can now be nominated as part of the DEFAULT value for a column, so you don’t need to refer to it at all…and you STILL can have the number
INSERT INTO MY_TABLE ( ) VALUES ( ...) RETURNING seq_col INTO :some_variable;
So say it with pride … “I DONT WANT TO KNOW WHAT MY SEQUENCE VALUES ARE!!!” 🙂