sql - Retrieve Oracle last inserted IDENTITY

Question

Since Oracle 12c we can use IDENTITY fields.

Is there a way to retrieve the last inserted identity (i.e. select @@identity or select LAST_INSERTED_ID() and so on)?

Answer 1

Well. Oracle uses sequences and default values for IDENTITY functionality in 12c. Therefore you need to know about sequences for your question.

First create a test identity table.

CREATE TABLE IDENTITY_TEST_TABLE
(
  ID NUMBER GENERATED ALWAYS AS IDENTITY 
, NAME VARCHAR2(30 BYTE) 
);

First, lets find your sequence name that is created with this identity column. This sequence name is a default value in your table.

Select TABLE_NAME, COLUMN_NAME, DATA_DEFAULT from USER_TAB_COLUMNS
where TABLE_NAME = 'IDENTITY_TEST_TABLE';

for me this value is "ISEQ$$_193606"

insert some values.

INSERT INTO IDENTITY_TEST_TABLE (name) VALUES ('atilla');
INSERT INTO IDENTITY_TEST_TABLE (name) VALUES ('ayd?n');

then insert value and find identity.

INSERT INTO IDENTITY_TEST_TABLE (name) VALUES ('atilla');
 SELECT "ISEQ$$_193606".currval from dual; 

you should see your identity value. If you want to do in one block use

declare
   s2 number;
 begin
   INSERT INTO IDENTITY_TEST_TABLE (name) VALUES ('atilla') returning ID into s2;
   dbms_output.put_line(s2);
 end;

Last ID is my identity column name.