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

Question

I'm writing a script to populate some tables with data for testing.

I would like to write something like the following but I don't know how to do it (I'm Oracle 11g)

SET ENABLED_USER_ID = SEQ.NEXTVAL; // PSEUDOCODE
SET DISABLED_USER_ID = SEQ.NEXTVAL; // PSEUDOCODE

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);

I know I could rearrange the queries and use the sequence.currval reference, but I'd prefer to have the id saved in properly named variables.

Maybe I should just wrap the script in a DECLARE ... BEGIN ... END; but I'm hoping there is a more concise way to do it.

---

Addition 27 May 2011 15:31

It seems that in any case I have to declare the variables in a DECLARE block. So I'm trying with

DECLARE
  USER_ID NUMBER(10,0) := 1;
BEGIN   
  insert into TEST_USER
  values (user_id, 'andrew', sysdate);   
END;

but I get the following error

Caused by: java.sql.SQLException: ORA-06550: **line 2, column 27:**
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

  * & = - + ; < / > at in is mod remainder not rem
  <an exponent (**)> <> or != or ~= >= <= <> and or like like2
  like4 likec between || multiset member submultiset

That points to the variable declaration.

I'm using java to load the script from a file and running it using the Oracle JDBC driver (ojdbc14-10.2.0.4.0.jar) on a Oracle 11g server.

The table TEST_USER has been created with

create table TEST_USERS (
    id number(10, 0) not null,
    name varchar2(100),
    date_ins date default sysdate,
    primary key (id)
);

Answer 1

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;
/

Answer 2

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

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

Answer 3

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

Answer 4

SELECT seq.nextval 
   INTO ENABLED_USER_ID
FROM dual;

Answer 5

I think you can actually get away without any extra variables by using currval:

INSERT INTO USERS
    (ID,      USR_NAME)
VALUES  (SEQ.NEXTVAL, 'ANDREW');
INSERT INTO CAR
   (CAR_ID,         CAR_NAME, USR_ID)
VALUES (CARSEQ.NEXTVAL, 'FORD',   SEQ.CURRVAL);