Inserting into Oracle and retrieving the generated sequence ID

Question

I have a handful of raw SQL queries for SQL Server which use SCOPE IDENTITY to retrieve the generated ID for a specific INSERT immediately after that INSERT occurs all in one execution     INSERT into Batch  BatchName  BatchType  Source  Area   Values    strBatchName   strType   strSource   intArea     SELECT SCOPE IDENTITY   BatchID    The question is     What   s the best way to do that for an Oracle database   Can this be done on Oracle through standard SQL or do I have to switch this to use a stored procedure and place something similar in the body of the stored proc    If it must be a stored proc  then what is the de-facto standard way for retrieving the last generated sequence number  taking care to consider there will likely be overlapping executions on multiple threads so this mechanism will need to retrieve the right generated ID and not necessarily the absolute last generated ID     If two execute simultaneously then each must return the correct generated ID from each respective call   Notice I   m not using SQL Server s      IDENTITY    because of that multithreaded nature of the calls   I would rather keep it as raw SQL if possible since that   s much easier for me to manage across platforms  single file containing each platform s SQL block separated by DBMS identifying tags    Stored procs are a bit more work for me to manage  but I can go that way if it s the only way possible

User · Answer

You can use the below statement to get the inserted Id to a variable-like thing.

INSERT INTO  YOUR_TABLE(ID) VALUES ('10') returning ID into :Inserted_Value;

Now you can retrieve the value using the below statement

SELECT :Inserted_Value FROM DUAL;

User · Answer

There are no auto incrementing features in Oracle for a column  You need to create a SEQUENCE object  You can use the sequence like   insert into table batch id       values my sequence nextval            to return the next number  To find out the last created sequence nr  in your session   you would use   my sequence currval   This site has several complete examples on how to use sequences

User · Answer

Expanding a bit on the answers from  Guru and  Ronnis  you can hide the sequence and make it look more like an auto-increment using a trigger  and have a procedure that does the insert for you and returns the generated ID as an out parameter   create table batch batchid number      batchname varchar2 30       batchtype char 1       source char 1       intarea number     create sequence batch seq start with 1    create trigger batch bi before insert on batch for each row begin     select batch seq nextval into  new batchid from dual  end     create procedure insert batch v batchname batch batchname TYPE      v batchtype batch batchtype TYPE      v source batch source TYPE      v intarea batch intarea TYPE      v batchid out batch batchid TYPE  as begin     insert into batch batchname  batchtype  source  intarea      values v batchname  v batchtype  v source  v intarea      returning batchid into v batchid  end      You can then call the procedure instead of doing a plain insert  e g  from an anoymous block   declare     l batchid batch batchid TYPE  begin     insert batch v batchname   gt   Batch 1           v batchtype   gt   A           v source   gt   Z           v intarea   gt  1          v batchid   gt  l batchid       dbms output put line  Generated id       l batchid        insert batch v batchname   gt   Batch 99           v batchtype   gt   B           v source   gt   Y           v intarea   gt  9          v batchid   gt  l batchid       dbms output put line  Generated id       l batchid   end     Generated id  1 Generated id  2   You can make the call without an explicit anonymous block  e g  from SQL Plus   variable l batchid number  exec insert batch  Batch 21    C    X   7   l batchid         and use the bind variable  l batchid to refer to the generated value afterwards   print l batchid  insert into some table values  l batch id

User · Answer

Doing it as a stored procedure does have lot of advantages  You can get the sequence that is inserted into the table using syntax insert into table name values returning   Like   declare some seq val  number  lv seq        number  begin some seq val    your seq nextval  insert into your tab  col1  col2  col3   values  some seq val  val2  val3  returning some seq val into lv seq   dbms output put line  The inserted sequence is     to char lv seq    end      Or just return some seq val  In case you are not making use of SEQUENCE  and arriving the sequence on some calculation  you can make use of returning into effectively

User · Answer

You can do this with a single statement - assuming you are calling it from a JDBC-like connector with in out parameters functionality   insert into batch batchid  batchname   values  batch seq nextval   new batch   returning batchid into  l batchid    or  as a pl-sql script   variable l batchid number   insert into batch batchid  batchname   values  batch seq nextval   new batch   returning batchid into  l batchid   select  l batchid from dual

[sql] Inserting into Oracle and retrieving the generated sequence ID

Examples related to sql

Examples related to oracle

Examples related to stored-procedures

Examples related to identity-column

Examples related to scope-identity