Capturing column value through returning clause while inserting data into a table in Oracle

by somen
(india)

Question:
I have a table in my Oracle database defined as follows:

create table my_tab(
a number primary key
, b varchar2(10)
, c date default sysdate);

The columns b and c are populated by insert statements on the table and column a is populated using a sequence by a trigger defined on the table.

Now my question is that is it possible to get the value of column a while inserting a row into the table?

More precisely, in the following code:

declare
   x my_tab.a%type;
begin

   insert into my_tab(b) values ('testing')
   returning a into x;

   dbms_output.put_line('A = '||x);
   commit;
end;
/

will the returning clause return the correct value?

Answer:
Whilst you can use a trigger to populate a column with a value from a sequence in Oracle, this may not be the best approach as it hides some of the application logic for populating the table which may make your application harder to maintain. It might be better to access the sequence directly from an insert statement like so:

insert into my_tab(a,b)
values (my_tab_seq.nextval, 'test');

However, you probably have a good reason for using a trigger so we'll continue with that approach and see if it will work.

The first thing we need to do is to create the table so we'll use this table definition:


create or replace table my_tab(
a number primary key
, b varchar2(10)
, c date default sysdate);

Next we need to create an Oracle sequence and a PL/SQL trigger to populate the column with the value from the sequence like so:

CREATE SEQUENCE my_tab_seq START WITH 1 INCREMENT BY 1;

CREATE TRIGGER my_tab_trig
BEFORE INSERT ON my_tab
FOR EACH ROW
BEGIN
  select ttt_seq.nextval into :new.a from dual;
END;

The final step is to run the PL/SQL block to insert data and see of it returns the value of the primary key (column a) for the row we've just inserted and to prove that we're getting the correct value we'll also fetch the maximum value of a at the same time.

declare
   x my_tab.a%type;
   max_a my_tab.%TYPE;
begin

   insert into my_tab(b) values ('testing')
   returning a into x;

   dbms_output.put_line('A = '||x);
   commit;
   select max(a) into max_a from my_tab;
dbms_output.put_line('highest value of a = '||max_a);
end;
/

This gives us the following results:
a = 4
highest value of a = 4

In short then, the answer is yes, we can insert a value from a sequence using a trigger and get that value using the returning clause.