Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)
);
share|improve this question

5 Answers 5

up vote 7 down vote accepted

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;
/
share|improve this answer
SELECT seq.nextval 
   INTO ENABLED_USER_ID
FROM dual;
share|improve this answer
    
ENABLED_USER_ID should go in a declare block right? –  spaceCamel May 27 '11 at 14:27
    
@Xan: yes, variables can only be defined in a DECLARE section which in turn is only valid if you have a PL/SQL block –  a_horse_with_no_name May 27 '11 at 14:44
    
I'd add +1 but ain't got enough reputation... :/ –  spaceCamel May 27 '11 at 15:08

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

share|improve this answer
    
I'd give +1 but don't have enough reputation yet. Thanks though. I don't like sequence.currval because it's harder to find where the id was generated in a long script. A variable is more clear and informative. –  spaceCamel May 27 '11 at 13:24
    
What are you you using to run your script? If it is SQL*Plus (or SQLDeveloper) you can define variables at the client-side, eg: define owner='sparky';, select '&owner' from dual; –  Jack Douglas May 27 '11 at 13:37
    
I'm using the Oracle JDBC library. –  spaceCamel May 27 '11 at 14:28
    
What is the scripting language? Java? –  Jack Douglas May 27 '11 at 14:35
    
Can you just store the value in a Java variable? –  Jack Douglas May 27 '11 at 14:51

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

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

share|improve this answer

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

INSERT INTO USERS
    (ID,      USR_NAME)
VALUES  (SEQ.NEXTVAL, 'ANDREW');
INSERT INTO CAR
   (CAR_ID,         CAR_NAME, USR_ID)
VALUES (CARSEQ.NEXTVAL, 'FORD',   SEQ.PREVVAL);
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.