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.

Elmoelmore

Those are some great answers. Now that I'm getting to the task it looks like another layer using a "package", setting a cursor to a "ROWTYPE" and returning some of the data through a procedure or function inside the package will be the last step to returning the data as a resultset so the caller can perform a SELECT but pass the data needing to be inserted as parameters to the package-function. New to it so hopefully that all makes sense. – Elmoelmore

Answer 1

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 => 'Batch 1',
        v_batchtype => 'A',
        v_source => 'Z',
        v_intarea => 1,
        v_batchid => l_batchid);
    dbms_output.put_line('Generated id: ' || l_batchid);

    insert_batch(v_batchname => 'Batch 99',
        v_batchtype => 'B',
        v_source => 'Y',
        v_intarea => 9,
        v_batchid => 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, ...);

Salyers

I have been avoiding row-level triggers since 8i due to performance issues when doing larger inserts, but I also have to admit I haven't tried it since. How does it perform with 11g or say 10g? – Bettinabettine

@Ronnis: haven't used them with large batch inserts enough to comment. I wasn't aware of any issues, but I guess the context switching (having to select from dual) may be significant in some circumstances. And I suppose the trigger is rather pointless if you always do the insert from the procedure anyway, so this is slightly over-complicated... – Salyers

Answer 2

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.

Edit: Two years after this answer was written Oracle introduced "identity columns".

Bettinabettine

What if another table inserts immediately before you call my_sequence.currval, Sequence next number will be diffrent. – Dentifrice

currval is valid only after nextval has been called in your session, and the value is kept per session basis. If session A calls nextval and get ID = 10, and session B calls nextval 1000 times, session A will still get ID = 10 if currval is called. – Bettinabettine

Oracle does have auto-incrementing features. e.g. IDENTITY docs.oracle.com/en/database/other-databases/nosql-database/19.1/… – Mama

Answer 3

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;

Macerate

I get: SP2-0552: Bind variable "INSERTED_VALUE" not declared. 0 rows inserted. – Wargo

@Nick. You need to declare a variable called "Inserted_Value" first before executing this code – Macerate

Then why didn't you show the declaration as well? Answers need to be complete. – Innsbruck

how do u define inserted value here when multiple records are inserted ? any help appreciated – Mclaurin

Answer 4

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.

Toothache

You don't even need that many steps; no point declaring some_seq, you can do insert into your_tab (col1, col2, col3) values (your_seq.next_val, val2, val3) returning col1 into lv_seq;. Also worth noting that you can put the return value straight into an out parameter. – Salyers

Answer 5

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;

Individually

Answer 6

A more elegant solution could be using RETURNING statement:

DECLARE
V_ID_RETURN NUMBER;
BEGIN

INSERT into Batch(
BatchName,
BatchType,
Source,
Area
) Values (
@strBatchName,
@strType,
@strSource,
@intArea
)
RETURNING BatchID INTO V_ID_RETURN;

DBMS_OUTPUT.PUT_LINE('V_ID_RETURN:'||V_ID_RETURN);

END;

I hope it help you

Fetlock