How do I do an insert in oracle, and get the ID of the inserted row?

Question

Possible Duplicate:
Get ID of last inserted record in oracle db

I am brand new to oracle, having used SQL Server in the past. I have a stored procedure, and I am trying to do two INSERTs one after the other. The second INSERT requires the ID of the first INSERT. Could someone explain how to do it?

I'm seeing stuff about SEQUENCEs and nextvalue/curval ?

I guess in SQL Server I'd just declare a variable, and use SCOPE_IDENTITY, so I'd be looking to do that.

looks about right, but I can't figure out how to declare a variable, except in the procedure declaration. — NibblyPig, Oct 22 '12 at 19:44

GolezTrol · Accepted Answer · 2023-02-09T15:46:33.040

There are two possibilities. If you are using sequences for number generation, you can get the value from the sequence, like this:

select SequenceName.currval into AVariable from dual

or this

AVariable := SequenceName.currval;

But it's probably better to use the returning clause like this:

declare
  AVariable int;
begin
  insert into yourtable(columns) 
  values (values) 
  returning id into AVariable;
  
  insert into anotherTable(columns)
  values(AVariable, othervalues);
end;

This way, it will work regardless of implementation details. You don't have to know the name of the sequence, and it will also work with the later introduced identity columns.

The only thing it won't work for is views with an instead of trigger, but that's a special case altogether.

score 0 · Answer 2 · answered Oct 22 '12 at 19:47

There are a few approaches. One option is to use the RETURNING clause, i.e.

DECLARE
  l_generated_id INTEGER;
BEGIN
  INSERT INTO table_name( <<column list>> )
    VALUES( <<values list>>
    RETURNING <<name of primary key column>>
         INTO l_generated_id;
  INSERT INTO other_table( <<column list>> )
    VALUES( l_generated_id, <<other values>> );
END;

If you know that the primary key is populated via a sequence (with or without a trigger) and you know the name of that sequence, you can use the sequence_name.currval in your second INSERT statement (the first INSERT statement would, either directly or via a trigger reference the sequence_name.nextval to generate the new primary key).

How do I do an insert in oracle, and get the ID of the inserted row?

2 Answers2