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

by somen
(india)

Using SQL returning clause to return value of column populated from a sequnce in Oracle

Using SQL returning clause to return value of column populated from a sequnce in Oracle

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 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 OR REPLACE TRIGGER my_tab_trig
BEFORE INSERT ON my_tab FOR EACH ROW
BEGIN
  SELECT my_tab_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.a%TYPE;
BEGIN

   INSERT INTO my_tab(b) VALUES ('testing')
   RETURNING a INTO x;

   COMMIT;

   SELECT max(a) INTO max_a FROM my_tab;

   DBMS_OUTPUT.PUT_LINE('a = '||x);
   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.

Note that from Oracle 11g onwards we can use a shortcut to get the next sequence value in our trigger by directly assigning to the variable rather than selecting from dual. The trigger code for Oracle 11g onwards would then be as follows:

CREATE OR REPLACE TRIGGER my_tab_trig
BEFORE INSERT ON my_tab
FOR EACH ROW
BEGIN
  :new.a := my_tab_seq.NEXTVAL ;
END;


For more help with PL/SQL see our Oracle PL/SQL tutorial. The Oracle PL/SQL Language Reference is also worth reading.

If you're interested in formal PL/SQL training (either on-line or in a classroom) then see our Oracle training page for more information.

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

Average Rating starstarstarstarstar

Click here to add your own comments

Apr 25, 2011
Rating
starstarstarstarstar
max(date) increment by one
by: Anonymous

I have a table, I want to select max date(DATE) and increase one by one(dd.mm.yyyy all field should increase). Can you help me to get it using insert query?

Date arithmetic is very easy in Oracle you can add and subtract days (and fractions of days) to any date.

For example:

SELECT sysdate+1/24 FROM dual;

This can be done with any date column, so if you have table (called emp say) with a date column (called hire_date, say) and you want to increase the hire dates by 1 day you could run the following command:

INSERT INTO new_emp(employee_id, new_hire_date)
SELECT employee_id, hire_date+1 FROM emp;


If you want the hire_date to be one more than the maximum current hire_date value you would need to use a subquery, like so:-

INSERT INTO new_emp(employee_id, new_hire_date)
SELECT e.employee_id, n.new_hire_date FROM
  ( SELECT employee_id FROM emp) e
 ,( SELECT max(hire_date)+1 new_hire_date FROM emp) n;


This is not necessarily the most efficient way of doing this as this creates a Cartesian join, but it does the job. In this query, the first subquery select all the employee ids and the second selects the maximum hire date from the employee table and adds 1 to it. The outer query combines the two subqueries and feeds the result into the insert statement.

Click here to add your own comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.