How to get the id of the added row in Oracle

I need to translate a script from tsql to plsql, something like:

DECLARE @temp_id int

INSERT INTO Table (col1, col2) VALUES (1, 2)

SET @temp_id 开发者_开发知识库= @@identity

but, I am having trouble to find something similar to global variable @@identity

Oracle expert anyone?

---

Presuming you have some kind of trigger to populate the primary key column with a sequence, and you want to get the assigned value...

INSERT INTO Table (col1, col2) VALUES (1, 2) 
RETURNING pk_col INTO temp_id
/

Note that the RETURNING syntax only works with single row inserts.

---

The answer of Michael Pakhantsov is only usable in a single user single tasking environment. The insert and select statements are separate statements! What happens in a multi user multi process environment?

Process 1 insert
Process 2 insert
Process 2 select returns the is the id by process 2 insert
Process 1 select returns the is the id by process 2 insert NOT the process 1 insert

Don't ever program this way, don't even think about it. You need an atomic operation, which means it will not be affected by task switching.

The answer of APC would be:

create table FOO (
  id number primary key,
  name varchar2(100)    
);

create sequence FOO_seq;

create or replace trigger FOO_trg
before insert on FOO
for each row
begin
  select FOO_seq.nextval into :new.id from dual;
  dbms_output.put_line('inside trigger '||:new.id||' '||:new.name);
end;
/

declare
  temp_id number:=10;
begin  
  INSERT INTO FOO (id, name) VALUES (null, 'Vicky') RETURNING id INTO temp_id;
  dbms_output.put_line(temp_id);
  rollback;
  INSERT INTO FOO (id, name) VALUES (null, 'Joël') RETURNING id INTO temp_id;
  dbms_output.put_line(temp_id);
  commit;
end;  
/

select * from FOO;

drop table FOO;
drop sequence FOO_seq;

It would output:

table FOO created.
sequence FOO_SEQ created.
TRIGGER FOO_TRG compiled
anonymous block completed
    ID NAME
------ --------
     2 joël        


table FOO dropped.
sequence FOO_SEQ dropped.

The dbms_output would be:

inside trigger 1 Vicky
1
inside trigger 2 Joël
2

Remember you can only use this for inserting one row at a time:

insert all
  into foo(id,name) values(null,'Vicky')
  into foo(id,name) values(null,'Joël')
  SELECT null,'none'  FROM dual  RETURNING id INTO temp_id;

Gives a PL/SQL: ORA-00933: SQL command not properly ended error, omit the RETURNING id INTO temp_id.

In Oracle 12 you can use the identity column and get something similar to SQLServer and MySql.

CREATE TABLE foo (
  id   NUMBER GENERATED ALWAYS AS IDENTITY,
  name VARCHAR2(30)
);
/

declare
  temp_id varchar2(100);
begin
  INSERT INTO foo(name) VALUES ('Vicky') RETURNING id||' '||name INTO temp_id;
  dbms_output.put_line(temp_id);
  INSERT INTO foo(name) VALUES ('Joël') RETURNING id||' '||name INTO temp_id;
  dbms_output.put_line(temp_id);
end;
/

drop table foo;
purge recyclebin;

dbms_output would be:

1 Vicky
2 Joël

One note added: When you create a table using identity, a system-generated sequence will be generated. This sequence will continue to exists even after dropping the table! Even the sysdba cannot drop this sequence! After the drop table statement you need the purge recyclebin to remove them.

---

You need use sequences. (http://psoug.org/reference/sequences.html)

SequenceName.NEXTVAL next value, sequenceName.CURRVAL - latest used value (like @@Identity)

INSERT INTO Table (Id, col1, col2) VALUES (Sequence.NEXTVAL, 1, 2);

SELECT sequence.CURRVAL INTO Temp_ID from dual;