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:
FORALL index IN [ lower_bound .. upper_bound | INDICES OF indexing_collection | VALUES OF indexing_collection ] [ SAVE EXCEPTIONS] sql_statement;
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.
ORA-22160: element at index [N] does not exist
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.
FORALL indx IN INDICES OF sparse_collection INSERT INTO the_table VALUES sparse_collection (indx);
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.
FORALL indx IN VALUES OF pointer_collection INSERT INTO the_table VALUES binding_collection (indx);
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.
FORALL indx IN depts.FIRST .. depts.LAST DELETE FROM emp WHERE deptno IN depts (indx) RETURNING empno BULK COLLECT INTO empnos;
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:
bulk_errors exception; pragma exception_init (bulk_errors, -24381);
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:
DECLARE TYPE emp_rt is RECORD (empno NUMBER(4) ,ename VARCHAR2(25) ,job VARCHAR2(25) ); TYPE emp_tt IS TABLE OF emp_rt INDEX BY PLS_INTEGER; l_emps emp_tt; BEGIN ... << Code to fill up the collection >> FORALL indx IN 1 .. l_emps.COUNT INSERT INTO emp (empno) VALUES (l_emps(indx).empno); END;
Would result in an error:
PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records
In Oracle 11g this restriction has been lifted.
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:
for rec in (select empno, ename from emp) loop ... end loop;
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.