How to get last ID from oracle database using SQL command?

Question

Is there a mechanism to get last inserted ID in Oracle 12c Enterprise edition by using SQL command? In DB2 world, I would use:

SELECT IDENTITY_VAL_LOCAL() AS IDENTITY FROM SYSIBM.SYSDUMMY1

This would get me the last generated ID from current connection (session). This would mean that no matter if other inserts occur from other users (or same user but in different session) I would get the latest ID only from current session.

The only answer I could find for Oracle was the one using anonymous block.

DECLARE
    V_ID NUMBER;
BEGIN
    INSERT INTO OBJECT_EXAMPLE(NAME) VALUES ('Some name') returning ID into V_ID;
    DBMS_OUTPUT.put_line(V_ID);
END;

However, this does not return result set. Since I'm using IBM BPM product I cannot define output parameter when executing anonymous block (I get "OUT parameters are only valid when used with a call operation"). Also, I cannot get "output" section like in DBeaver (I get empty record).

So what I really need is:

• something that can insert a row into a table and get the latest ID form that table (or latest generated id from session) but only for that opened connection or
• something that acts like select but also does the insert

Answer 1

Oracle tables with automatically assigned unique integer columns use sequence objects to generate those unique values.

Look at the table definition or your INSERT query. One of them will show you the name of a sequence object for the unique id column.

Then use the sequence, via sequence.nextval, to create a unique id to INSERT into a table.

Then immediately do SELECT sequence.currval FROM dual to get the latest value, right after using the sequence to assign a unique value. For race-condition-safe operations do it placing the SELECT in a transaction with the INSERT.

This sequence stuff is often used in a series of INSERT operations something like this.

 INSERT INTO person (person_id, given, surname, title)
            VALUES  (personid.nextval, 'Larry', 'Ellison', 'boss');
 INSERT INTO phone  (phone_id, person_id, type, value)
            VALUES  (phoneid.nextval, personid.currval, 'home', '555-1212');
 INSERT INTO phone  (phone_id, person_id, type, value)
            VALUES  (phoneid.nextval, personid.currval, 'office', '555-3434');

This gets us a person row with two associated phone rows. Notice the use of personid.currval for both phone rows.