EXECUTE IMMEDIATE and
FORALL (for bulk DML operations) offer a
USING clause to bind variable values into the SQL statement. Let's follow the progression
of explicit row-from-row processing to bulk binding to bulk binding in native dynamic
DML to see how the
USING clause is deployed.
We start with this kind of explicit
FOR loop in our Oracle7 and Oracle8 code base:
FOR indx IN employee_ids.FIRST .. employee_ids.LAST LOOP UPDATE employees SET salary = salary * 1.1 WHERE employee_id = employee_ids (indx); END LOOP;
Then, with Oracle8i, we get rid of most of the context switches by moving to
FORALL indx IN employee_ids.FIRST .. employee_ids.LAST UPDATE employees SET salary = salary * 1.1 WHERE employee_id = employee_ids (indx);
And that handles all of our needs-unless, once again, we need or would like to
perform this same operation on different tables, based on location (or for any other kind of
dynamic SQL situation). In this case, we can combine
IMMEDIATE, with these wonderful results:
CREATE OR REPLACE PROCEDURE upd_employees ( loc_in IN VARCHAR2, employees_in IN employees_t ) IS BEGIN FORALL indx in employees_in.first.. employees_in.last EXECUTE IMMEDIATE 'UPDATE ' || loc_in || ' employees SET salary = salary*1.1' || ' WHERE employee_id = :the_id' USING employee_in (indx); END;
Notice that in the
USING clause, we must include both the name of the collection and
the subscript for a single row using the same
FORALL loop index variable.
Let's again follow the progression from individual row updates to bulk bind relying on
BULK COLLECT INTO to retrieve information, and finally the dynamic approach
possible in Oracle 9i.
Oracle8 enhanced DML capabilities by providing support for the
clause. Shown in the following
FOR loop, it allows us to obtain information (in this case,
the updated salary) from the DML statement itself (thereby avoiding a separate and
BEGIN FOR indx IN employee_ids.FIRST .. employee_ids.LAST LOOP UPDATE employees SET salary = salary * 1.1 WHERE employee_id = employee_ids (indx) RETURNING salary INTO salaries (indx); END LOOP; END;
Starting with Oracle8i, we can take advantage of
FORALL to improve performance
BEGIN FORALL indx IN employee_ids.FIRST .. employee_ids.LAST UPDATE employees SET salary = salary * 1.1 WHERE employee_id = employee_ids (indx) RETURNING salary BULK COLLECT INTO salaries; END;
There's one seemingly odd aspect of this code you should remember: Inside the DML
statement, any reference to the collection that drives the
FORALL statement must be
subscripted as in:
WHERE employee_id = employee_ids (indx)
RETURNING clause, however, you
BULK COLLECT INTO the collection
and not a single subscripted row of the collection.
That's all well and good, but what if (not to sound like a broken record) we want to
execute this same update for any of the employee tables for different locations? Time to
go to NDS and, with Oracle 9i only, also employ a
RETURNING BULK COLLECT
CREATE OR REPLACE PROCEDURE upd_employees ( loc_in IN VARCHAR2, employees_in IN employees_t ) IS my_salaries salaries_t; BEGIN FORALL indx in employees_in.first.. employees_in.last EXECUTE IMMEDIATE 'UPDATE ' || loc_in || ' employees SET salary = salary*1.1' || ' WHERE employee_id = :the_id RETURNING salary INTO :salaries' USING employee_in (indx) RETURNING BULK COLLECT INTO my_salaries; END;
Handling and Reporting Exceptions
FORALL was wonderful for rapid processing of bulk DML statements. One
problem with it, however, is that you lose some of the granularity in exception handling
that you help with row-by-row processing. Suppose, for example, that we want to load a
whole bunch of words into a vocabulary table. We can do it very efficiently as follows:
BEGIN FORALL indx IN words.FIRST .. words.LAST INSERT INTO vocabulary (text) VALUES (words (indx)); END;
If, however, an error occurred in any single
INSERT, the entire
FORALL statement would fail. This is the sort of scenario that was easily handled with row-level processing,
such as with a loop like this:
FOR indx IN words.FIRST .. words.LAST LOOP BEGIN INSERT INTO t text) VALUES (words (indx)); EXCEPTION WHEN OTHERS THEN error_codes (indx) := SQLERRM; END; END LOOP;
With this kind of code, we'd insert all rows that didn't cause an error to occur. With
Oracle 9i, you can now do this with both static and dynamic
FORALL SQL statements, by
taking advantage of the
SAVE EXCEPTIONS clause.
FORALL indx IN words.first..words.last SAVE EXCEPTIONS INSERT INTO vocabulary ( text ) VALUES ( words(indx) );
SAVE EXCEPTIONS allows the
FORALL to continue through all the rows
indicated by the collection; it "saves up" the exceptions as it encounters them. This saving
step begs the obvious question: How can you, the developer, get information about the
errors that were "saved"? By taking advantage of the new
pseudo-collection, as demonstrated in the code shown in Example 6.
Example 6. Using the
DECLARE bulk_errors EXCEPTION; PRAGMA EXCEPTION_INIT (bulk_errors, -24381); BEGIN FORALL indx IN words.FIRST .. words.LAST SAVE EXCEPTIONS INSERT INTO t (text) VALUES (words (indx)); EXCEPTION WHEN bulk_errors THEN FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP log_error ( SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX, SQLERRM(-1 * SQL%BULK_EXCEPTIONS(indx).ERROR_CODE) ); END LOOP; END;
Each row of this pseudo-collection is a record consisting of two fields:
ERROR_CODE. The former field shows which index in the
original bulk-load collection causes the failure.
ERROR_CODE is the error number
You must both use the
SAVE EXCEPTIONS construct and handle the
BULK_ERRORS exception to get the intended benefit (that is, that all non-erroring rows
The March of Progress
The new Oracle 9i features covered in this article should provide a comfortable feeling about Oracle's commitment to the PL/SQL language. Native compilation offers a path to formidable and transparent improvements in our application performance (even, and perhaps especially, existing "legacy" code). The
CASE statement and expression, as well as support for dynamic SQL in bulk binding, help round out the language semantics.
This article was originally published in the April 2002 issue of Oracle Professional. The material in Feuerstein's articles--and those he cowrote with Bryn Llewellyn--are based on Oracle Corporation white papers originally prepared by Llewellyn for Oracle OpenWorld 2001 in San Francisco and OracleWorld Copenhagen in June 2002, and Feuerstein book, Oracle PL/SQL Programming, 3rd Edition.
In the next article, we'll embark on an in-depth examination of table functions and cursor expressions.
Steven Feuerstein is considered one of the world's leading experts on the Oracle PL/SQL language, having written ten books on the subject. Steven is a Senior Technology Advisor with Quest Software and has been developing software since 1980.
Bryn Llewellyn is PL/SQL Product Manager, Database and Application Server Technologies Development Group, at Oracle Corporation Headquarters.
O'Reilly & Associates recently released (September 2002) Oracle PL/SQL Programming, 3rd Edition.
Sample Chapter 10, Dates and Timestamps, is available free online.
For more information, or to order the book, click here.
Return to the O'Reilly Network.