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.

Answer 2

Please check

INSERT INTO yourtable (....)
  VALUES (...)
  RETURNING pk_id INTO yourtable;

It will help you to retrieve last inserted row

Answer 3

IDENTITY column uses a SEQUENCE “under the hood” - creating and dropping sequence automatically with the table it uses. Also, you can specify start with and increment parameters using start with 1000 and increment by 2. It's really very convenient to use IDENTITY when you don't want to operate it's values directly.

But if you need to somehow operate sequence directly you should use another option available in Oracle 12c - column default values. Sutch default values could be generated from sequence nextval or currval. To allow you to have a comprehensible sequence name and use it as "identity" without a trigger.

create table my_new_table
(id number default my_new_table_seq.nextval not null)

You will be always able to call: my_new_table_seq.currval.

It is possible to get ID generated from SEQUENCE on insert statement using RETURNING clause.

For example, create a temporary table:

create global temporary table local_identity_storage ("id" number) on commit delete rows

Make some insert saving this value in the temporary table:

CREATE TABLE identity_test_table (
  id_ident          NUMBER GENERATED BY DEFAULT AS IDENTITY,
  same_value VARCHAR2(100)
);

declare
  v_id number(10, 0);
begin  
  INSERT INTO identity_test_table
    (same_value)
  VALUES
    ('Test value')
  RETURNING id_ident INTO v_id;

  insert into local_identity_storage ("id") values (v_id);
  commit;
end;

Now you have "local" inserted id.

select "id" from local_identity_storage

Answer 4

It seems that Oracle implemented IDENTITY just to say that they support identities. Everything is still implemented using SEQUENCES and sometimes you need to access the SEQUENCE to make some of the work (i.e. retrieve the latest inserted IDENTITY).

There is not a way to retrieve the IDENTITY similar to MySQL, SQL Server, DB2, and so on, you have to retrieve it using the SEQUENCE.

Answer 5

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 6

What is your scope, global or last user inserted? If global just use

SELECT mytable_seq.nextval MyTableID FROM DUAL

https://www.sitepoint.com/community/t/oracle-last-insert-id-question/1402

If specific encapsulate your inserts & query within a transaction.

Answer 7

the last insert will be the highest value of the column. so I think that the easiest way to do it is with the max() method. something like this

select max(id) from table_name