SELECT IDENT_CURRENT(‘tablename’) in Oracle

Question

Hello, I'm new with PL/SQL and I need last inserted id in data table after insert statement.

Like on MS SQL SELECT IDENT_CURRENT(‘tablename’)

Thanks in advance! Goran

Answer 1

There is no built-it autoincrement fields in Oracle, you create it using sequences:

CREATE TABLE some_tab(
      rec_id      INTEGER,
      some_data   VARCHAR2(300)
);

CREATE SEQUENCE some_tab_seq;

CREATE OR REPLACE TRIGGER trig_BI
   BEFORE INSERT
   ON some_tab
   FOR EACH ROW
BEGIN
   IF :NEW.rec_id IS NULL
   THEN
      :NEW.rec_id := some_tab_seq.NEXTVAL ;
   END IF;
END;

Then in PL/SQL you can fetch current value of the sequence by

your_var := some_tab_seq.CURRVAL

Also in older version of Oracle you can't directly read NEXTVAL / CURRVAL into var and have to do:

SELECT some_tab_seq.CURRVAL
  INTO your_var
  FROM DUAL;

Answer 2

You can use the RETURNING clause:

insert into mytable(x,y) values (1,2) returning id into v_id;

This works well when there is a trigger that fills an id column, so you don't need the "select seq.currval from dual" thing.

Answer 3

Oracle does not implement identity columns. It uses Sequences which generate unique numbers which can be used as PK values in any table.

So the expression IDENT_CURRENT('my_table') is best translated into MySequence.CURRVAL of the sequence feeding the table's PK.

Typically, you'd retrieve the inserted PK value in MS SQL by:

INSERT INTO MyTable ...
SELECT @PK = SCOPE_IDENTITY()

In Oracle, use the INSERT RETURNING clause to achieve similar functionality

DECLARE PK ...;
INSERT INTO MyTable
RETURNING TableID INTO PK;