0

What is Oracle's counterpart to SQL Server's @@IDENTITY?

Michael Petrotta
  • 59,888
  • 27
  • 145
  • 179
user1990383
  • 125
  • 3
  • 9
  • 20
  • 1
    Since Oracle really doesn't have the concept of `IDENTITY` columns, it has no equivalent. In Oracle, you typically use a `SEQUENCE` to get consecutive, unique numbers for use in your tables. – marc_s Apr 03 '13 at 05:17
  • answer https://stackoverflow.com/a/51051121/3104267 helped me for my requirements as a work around for the equivalent. – user3104267 Jun 05 '20 at 11:14

1 Answers1

2

Given that Oracle does not have identity columns, you would normally create a sequence for each table and use that sequence to populate the primary key. Assuming you have done that, you can get the currval of the sequence to get the most recently generated sequence value for the current session.

SQL> create table foo(
  2    col1 number primary key,
  3    col2 varchar2(10)
  4  );

Table created.

SQL> create sequence foo_seq;

Sequence created.

SQL> ed
Wrote file afiedt.buf

  1  create or replace trigger foo_trg
  2    before insert on foo
  3    for each row
  4  begin
  5    :new.col1 := foo_seq.nextval;
  6* end;
SQL> /

Trigger created.

SQL> insert into foo( col2 )
  2    values( 'foo' );

1 row created.

SQL> insert into foo( col2 )
  2    values( 'bar' );

1 row created.

SQL> select foo_seq.currval
  2    from dual;

   CURRVAL
----------
         2
Justin Cave
  • 227,342
  • 24
  • 367
  • 384