code_icon_text

The RETURNING INTO clause is a very convenient way to retrieve values from rows that are being inserted/deleted/updated. When used with UPDATE and DELETE statements, it’s possible to retrieve values of multiple rows by using the RETURNING BULK COLLECT INTO. This is something that does not work with the INSERT statements: in that case you will be able to only return values from a single row.
Let’s see how this works and create a simple test table that will be the target of our DML operations

First of all let’s use a simple case just to have a look at how this works. Let’s update one row and retrieve rowid and run_date of the row just updated

As you can see from the output, we retrieved rowid and run_date through the RETURNING INTO clause. The same identical thing can be done with DELETE

and INSERT

Now imagine this scenario: after executing an update that involves multiple rows, you need to execute another operation on the same recordset. It would be handy to have the rowids of those records without the need to query again the database. Here is where the RETURNING INTO clause becomes really useful. It’s possible, in fact, to retrieve data from a multi-row operation by specifying BULK COLLECT

The RETURNING INTO BULK COLLECT operation is possible with both UPDATE and DELETE statements but not with multi-row inserts (insert….select). If you try to execute it you simply get an exception

How can we overcome this limitation in case we want to use the RETURNING INTO clause in a multi-row insert? There is a very interesting solution that goes through the use of a FORALL statement.
If we first insert our data into a collection like a Pl/Sql table and then call the single insert that will be iterated from inside a FORALL we are able to return all the values in our collection

So how does this work? The trick lies inside the nature of FORALL. Keep in mind that FORALL is not a loop but a declarative statement. It tells Oracle to execute all iterations of the DML with a single context switch. Basically the engine generates all the inserts and executes them all in one single “shot”, without switching context between Pl/Sql and Sql for every single insert like a normal loop would do.
This perfectly serves our purpose of retrieving the rowids of the newly inserted rows.
Fantastic!

Share this post on Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedInEmail this to someone

Leave a Reply

Your email address will not be published. Required fields are marked *