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