Inserting into Oracle and retrieving the generated sequence ID

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.

Solution 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, ...);

Solution 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".