CODEWITHSUNDEEP
sqloracle-database
Lahiru Chandima
Lahiru Chandima

Reputation: 24068

Get the unique id the record got when inserting to a database table

I have an oracle table with a unique id column. When I insert data into the table, I increment the last id.

insert into my_table vlaues((select max(id) from my_table), 'etc', 'etc');

There can be multiple processes who writes into this table simultaneously.

Auto commit is on.

This table does not have any other unique column except id.

When I insert a record to the table, is there a way to get the id value the record got, after I insert the record?

As I can see, if I use select max(id) from my_table after insert, I may not get the id used in the insert statement since someone else could have inserted another record before I issue select.

Upvotes: 1

Views: 1484

Answers (4)

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

When I insert a record to the table, is there a way to get the id value the record got, after I insert the record?

Simply use the RETURNING clause.

For example -

RETURNING identity_id INTO variable_id;

Test case -

SQL> set serveroutput on
SQL> CREATE TABLE t
  2    (ID NUMBER GENERATED ALWAYS AS IDENTITY, text VARCHAR2(50)
  3    );

Table created.

SQL>
SQL> DECLARE
  2    var_id NUMBER;
  3  BEGIN
  4    INSERT INTO t
  5      (text
  6      ) VALUES
  7      ('test'
  8      ) RETURNING ID INTO var_id;
  9    DBMS_OUTPUT.PUT_LINE('ID returned is = '||var_id);
 10  END;
 11  /
ID returned is = 1

PL/SQL procedure successfully completed.

SQL>

SQL> select * from t;

        ID TEXT
---------- --------------------------------------------
         1 test

SQL>

You could use the same technique of RETURNING for pre-12c release too, where you do not have IDENTITY columns. The returning clause will return the values that you are inserting into the table.

Upvotes: 0

Rahul Tripathi
Rahul Tripathi

Reputation: 172378

You can try this:

CREATE TABLE myTable( ID RAW(16) DEFAULT SYS_GUID())

Also it is preferred to use Sequence to get the auto incremented id.

If you want to get the sequence id after the insert you can try like this:

declare
x  number;
id number;
begin
x := your_sequence.nextval;
insert into mytable (column1, column2, column3) 
values (x, value2, value3) returning x into id;

dbms_output.put_line(to_char(id));
end;

Upvotes: 2

Shepherdess
Shepherdess

Reputation: 651

It's best to use sequences in this scenario (although you may end up with gaps in numbers). You can get their current/next values using seq_abc1.currval/nextval .

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269493

Oracle 12 finally has built-in identity columns. So, if you are using the most recent version of Oracle, you can just use that. Read about it here.

In earlier versions, it is best to use a sequence. This guarantees uniqueness, even in a multi-threaded environment. I always implement triggers to update the id column. Here is an answer to a similar question, that explains how to do this.

Upvotes: 5

Related Questions