Inserting into Oracle and retrieving the generated sequence ID

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_bibefore insert on batchfor each rowbegin    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)asbegin    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: 1Generated 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, ...);

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.

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:

declaresome_seq_val  number;lv_seq        number;beginsome_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.