Oracle - return newly inserted key value.

Question

We have a table with a Primary Key that gets populated on insert by a trigger on the table - the trigger gets the next sequence number from a sequence we created for the table and uses that for the value of the key on insert. Now we would like to be able to return that value in our insert procedure (PL\SQL), similar to select @@scope_identity in SQL Server. I have been googling all day and basically come up with nothing - anyone been successful with this before?

Thanks

Answer 1

I think you are looking for a Callable Statement. Here's javadoc if you are trying to get to it from Java.

Answer 2

why not just return the_sequence.currval ?

Answer 3

I don't know if it works with triggers but the RETURNING clause may be what you're looking for:

INSERT INTO my_table (col_1, col_2)
  VALUES ('foo', 'bar')
  RETURNING pk_id INTO my_variable;