Oracle auto generated columns and insert ... returning into

Everyone knows Oracle doesn’t have auto generated columns, so what am I talking about? I really mean columns whose value is generated by a trigger. The most common example being to simulate the auto-generated id columns functionality found in many other databases. In Oracle instead of defining a column as having an auto generated numeric value you have to define a sequence, define an insertion trigger that selects the next sequence value and uses it for the inserted id columns value, something like the following:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

 create table generated_ids (
  id number constraint pk primary key, 
  name varchar2(255) not null
) 
/ 
create sequence ids_seq start with 1;
/ 
create or replace trigger bi_generated_ids before 
  insert on generated_ids for each row 
begin 
 :new.id := nvl(:new.id, ids_seq.nextval);
end;
/ 
show errors
/

This works fine, you can insert into the table in the manner shown below, and the trigger will generate a value for the id column:

1

 insert into generated_ids (name) values ('whatever'); 

Very often however, you want to know the value of the id column that was chosen by the trigger. You could do something like this:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

declare l_id number;
begin insert into generated_ids (name) 
values 
  ('whatever');
select 
  id into l_id 
from 
  generated_ids 
where 
  name = 'whatever';
dbms_output.put_line(l_id);
end;

The above will work IFF there is a unique constraint on the name column, it won’t work reliably if not, there could be more than one row with the value ‘whatever’. Anyways having to perform two statements (the insert and then the select) seems very heavyweight for what is a very common need, there must be a better way. The better way to do this is to use the returning into clause with the insert statement, like so:

1
2
3
4
5
6
7

declare 
 l_id number;
begin 
 insert into generated_ids (name) values 
  ('whatever') returning id into l_id;
 dbms_output.put_line(l_id);
end;

That’s a bit less verbose and more robust at the same time. It’s worth emphasising that this technique is not just limited to auto-generated ids, it can be applied to any column value that may be generated or modified by a trigger.