In Oracle how do I save a sequence.nextval in a variable to be reused in multiple inserts?

I think it goes like this

DECLARE
    ENABLED_USER_ID PLS_INTEGER;
    DISABLED_USER_ID PLS_INTEGER;
BEGIN
    ENABLED_USER_ID := SEQ.NEXTVAL;
    DISABLED_USER_ID := SEQ.NEXTVAL;

    INSERT INTO USERS (ID, USR_NAME)
    VALUES  (ENABLED_USER_ID, 'ANDREW');

    INSERT INTO CAR (CAR_ID, CAR_NAME, USR_ID)
    VALUES (CARSEQ.NEXTVAL, 'FORD', ENABLED_USER_ID);

    INSERT INTO USERS (ID, USR_NAME)
    VALUES  (DISABLED_USER_ID, 'ANDREW');

    INSERT INTO CAR (CAR_ID, CAR_NAME, USR_ID)
    VALUES (CARSEQ.NEXTVAL, 'FORD', DISABLED_USER_ID);
END;
/

You would do this with the RETURNING clause in your first INSERT statement.

UPDATE: Happened to write about this in my blog recently.


You will need a block if you are declaring variables

With 11g, support for sequences has been improved so you can use them like:

ENABLED_USER_ID := SEQ.NEXTVAL;

rather than using a select statement (though both will work)

Other options for persisting the values include saving them to a table or creating a context, but I think sequence.currval is really the 'right answer' here