You dont need that sequence number

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!!!” 🙂

4 thoughts on “You dont need that sequence number

  1. How about the situation where I need the same sequence number for a single insert statement? I would need to put it into a variable so that I don’t have a different sequence for each row as compared to a bunch or rows.

    • That assumes you’re doing a single row insert, but if you’ve multiple rows that are being inserted at the same time, all with the same sequence number, then I could see that needing to store the seq val in a variable once, and then use the variable to do the insert is a valid case. But that’s a bit different from the case you were talking about in your post, I think!

      • I think Sai is talking about repeating the same value within the same row. In that case a .currval will do nicely.
        INSERT INTO MY_TABLE (seq_col, next_col)
        VALUES (my_sequence.nextval, my_sequence.currval)

        Boneist might be talking about the insert all statement? That works with the currval too.

        Please correct me if I’m wrong.

        Regards,

        Arian

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s