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

As I've written in this blog post, you could fetch all the current identity values of your schema with a single query:

with
  function current_value(p_table_name varchar2) return number is
    v_current number;
  begin
    for rec in (
      select sequence_name
      from user_tab_identity_cols
      where table_name = p_table_name
    )
    loop
      execute immediate 'select ' || rec.sequence_name || '.currval from dual'
      into v_current;
      return v_current;
    end loop;

    return null;
  end;
select *
from (
  select table_name, current_value(table_name) current_value
  from user_tables
)
where current_value is not null
order by table_name;
/

Answer 2

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.