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

Question

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.

This is not a safe way to have autoincremented IDs - simultaneous access will cause duplicate IDs (possibly causing unique key violations). — Luaan, May 14 '15 at 12:26
possible duplicate of [Autoincrement in oracle to already created table](http://stackoverflow.com/questions/28037303/autoincrement-in-oracle-to-already-created-table) — Lalit Kumar B, May 14 '15 at 12:31
Also check out [IDENTITY column autoincrement functionality in Oracle 12c](http://lalitkumarb.wordpress.com/2015/01/20/identity-column-autoincrement-functionality-in-oracle-12c/) and [Auto-increment primary key in Pre 12c releases](http://lalitkumarb.wordpress.com/2015/01/20/auto-increment-primary-key-in-pre-12c-releases-identity-functionality/) — Lalit Kumar B, May 14 '15 at 12:32
This question is not just about auto incrementing. I need to fetch the auto incremented value after inserting a reord — Lahiru Chandima, May 14 '15 at 12:32
Then look at [Returning the sequence number of Identity column after Insert](http://lalitkumarb.wordpress.com/2015/04/07/returning-the-sequence-number-of-identity-column-after-insert/) — Lalit Kumar B, May 14 '15 at 12:34
If you are using PL/SQL to insert, then you could use **RETURNING CLAUSE** See http://stackoverflow.com/questions/28472118/returning-the-value-of-identity-column-after-insertion-in-oracle/28472515#28472515 — Lalit Kumar B, May 14 '15 at 12:35
Use:`insert into my_table values(my_table_seq.nextval, 'etc', 'etc') returning id into :id;` — ibre5041, May 14 '15 at 12:39
@LalitKumarB is `RETURNING` clause available before Oracle 12? — Lahiru Chandima, May 14 '15 at 12:50
@LahiruChandima I always use trigers to track old data , anyway the returning clause that Lalit mention worh looking , it depends on what you need .. also Gorden Linoff has a nice answer too — Moudiz, May 14 '15 at 12:51
@LahiruChandima, Yes, the RETURNING clause has had been since older releases. — Lalit Kumar B, May 14 '15 at 13:30

score 5 · Accepted Answer · edited May 23 '17 at 12:06

5

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.

edited May 23 '17 at 12:06

Community

1
1

answered May 14 '15 at 12:27

Gordon Linoff

1,242,037
58
646
786

I read your solution using a sequence, but it is not clear how I can fetch the value used for the `id` column when a record is inserted. Any idea? – Lahiru Chandima May 14 '15 at 12:36
@LahiruChandima why dont you use a triger ? – Moudiz May 14 '15 at 12:44
@Moudiz I am not familiar with triggers. I will look into triggers if they can be used for this. – Lahiru Chandima May 14 '15 at 12:48
@LahiruChandima You could see my answer to know how to fetch the values using RETURNING clause. – Lalit Kumar B May 14 '15 at 14:16

Rahul Tripathi · Answer 2 · 2015-05-14T12:38:20.183

2

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;

edited May 14 '15 at 12:38

answered May 14 '15 at 12:31

Rahul Tripathi

168,305
31
280
331

@Lahiru Chandima:- Whats the issue with `returning` syntax? – Rahul Tripathi May 14 '15 at 12:49

score 0 · Answer 3 · answered May 14 '15 at 12:34

0

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 .

answered May 14 '15 at 12:34

Shepherdess

651
6
7

The gaps are must, and usually there is nothing wrong about them. Without having gaps, one transaction would have to wait till other commits (rolls back), so the whole system would not scale well. – ibre5041 May 14 '15 at 12:37
True, I should have said 'will' rather than 'may' – Shepherdess May 14 '15 at 13:27

score 0 · Answer 4 · answered May 14 '15 at 14:16

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.

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

4 Answers4