The process of learning requires not only hearing and applying but also forgetting and then remembering again. – John Gray

Sending data back

Using the bulk capabilities you can not only fetch data in one roundtrip to the database, but you can also use it to send data back to the database in a single roundtrip. Using the FORALL statement. You can provide the FORALL statement with one or more collections and it will send the data back to the database in one pass. The syntax for the FORALL statement is:

If you are using lower_bound .. upper_bound you collections needs to be dense, because the SQL statement will be applied to every index of the collection. If the is no element defined at a certain index, an exception will be raised.

If you are working with a sparse collection, you can still use FORALL, but you should use either INDICES OF or VALUES OF. INDICES OF uses all the index values defined in the collection.

VALUES OF uses the values defined in the collection as index values. This way you can use one (indexing) collection as a driver, to insert values from another (binding) collection.

Instead of using the indices of the first collection, we are using the values of this collection as pointers to the second collection. If you want to use the returning clause in a SQL statement be sure you BULK COLLECT into a collection because you get more than one result back. One result for every row in your collection. The following statement takes a collection of departments to delete all the employees in those departments returning the employee numbers which have been deleted.

Save exceptions

You might encounter an error when performing DML using the FORALL statement causing all the DML to be rolled back. When you are performing DML on a single record, then this is not much of a problem, but what if you are performing DML on 1000 records and number 500 raises an error, then the previous 499 will NOT be rolled back. But the following 500 will not be processed. For this problem there is the SAVE EXCEPTIONS option in the FORALL statement. This allows you to continue with the rest of the collection even if one item raises an error. To check what errors have been raised you can define an exception (or check for the
error number) like this:

Then, in your exception handler you can check the SQL%BULK_EXCEPTIONS collection for the exceptions raised. This collection consists of records containing information about the errors encountered. The record layout has the following fields:
• error_index Which statement raised the error
• error_code What was the error code of the error raised

Note, Oracle errors are negative, but the code in this collection is positive. So if you want to use this error code in the sqlerrm function to retrieve the error message don’t forget to multiply it by -1.

Prior to Oracle 11g you cannot reference individual fields from a record in a collection. A statement like:

Would result in an error:

In Oracle 11g this restriction has been lifted.

Conclusion

You should be using the bulk features whenever you are requesting data from the server. When you see a recurring SQL statement in a loop, consider converting it to a FORALL statement. Bulk collects deliver a densely filled collection starting at row 1. If there is no data to fetch (anymore) then the NO_DATA_FOUND exception will not be raised, but an empty collection will be returned (or less items than the limit provided). If there are Cursor For Loops in your code like:

and you on Oracle 10g or higher then you shouldn’t worry about converting to a bulk collect version, because the optimizing compiler will do this automatically for you.

ref:
Oracle PL/SQL Programming 5th Edition – Steven Feuerstein
Oracle PL/SQL for DBA’s – Arup Nanda and Steven Feuerstein