CODEWITHSUNDEEP
sqloracle-database
joelc
joelc

Reputation: 2761

SELECT the last ID after INSERT in Oracle

I'm aware of using RETURNING in an INSERT statement but need a way to either have it returned as a result of the INSERT statement (or enclosing transaction) or be able to retrieve that value using SELECT. Is there a way to do this? I cannot use DBMS_OUTPUT. Thanks!

Upvotes: 1

Views: 530

Answers (1)

APC
APC

Reputation: 146309

RETURNING clause is the easiest way to guarantee getting the value of the ID generated by an INSERT statement. Querying for max(id) is unreliable in a multi-user environment, especially if you're using RAC.

If the ID is populated from a sequence you can get the current value of the sequence by running this in the same session as the INSERT:

 select your_sequence.currval from dual;

This means you need to know the name of the sequence, which isn't always obvious, even more so if you're using 12c Identity columns.

Basically, if you want the ID use RETURNING.

Upvotes: 2

Related Questions