How to get the id of the added row in Oracle

Question

Hello, I need to translate a script from tsql to plsql, something like:

DECLARE @temp_id int
INSERT INTO Table (col1, col2) VALUES (1, 2)
SET @temp_id = @@identity

but, I am having trouble to find something similar to global variable @@identity

Oracle expert anyone?

Answer 1

You need use sequences. (http://psoug.org/reference/sequences.html)

SequenceName.NEXTVAL next value, sequenceName.CURRVAL - latest used value (like @@Identity)

INSERT INTO Table (Id, col1, col2) VALUES (Sequence.NEXTVAL, 1, 2);

SELECT sequence.CURRVAL INTO Temp_ID from dual;

Answer 2

Presuming you have some kind of trigger to populate the primary key column with a sequence, and you want to get the assigned value...

INSERT INTO Table (col1, col2) VALUES (1, 2) 
RETURNING pk_col INTO temp_id
/

Note that the RETURNING syntax only works with single row inserts.