Native Compilation, CASE, and Dynamic Bulk Binding
Pages: 1, 2, 3, 4
In-Binding
Both the 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:
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 FORALL with EXECUTE
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.
Out-Binding
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 RETURNING
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
expensive query).
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
dramatically:
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) In the 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
clause:
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
In Oracle8i, 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) ); Use of 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 SQL%BULK_COLLECTIONS
pseudo-collection, as demonstrated in the code shown in Example 6.
Example 6. Using the SQL%BULK_COLLECTIONS pseudo-collection.
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_INDEX and ERROR_CODE. The former field shows which index in the
original bulk-load collection causes the failure. ERROR_CODE is the error number
encountered.
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
are inserted).
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.
You can also look at the Table of Contents, the Index, and the Full Description of the book.
For more information, or to order the book, click here.
Return to the O'Reilly Network.
You must be logged in to the O'Reilly Network to post a talkback.
Showing messages 1 through 2 of 2.
-
Problem with the examples?
2003-01-09 05:43:50 chrisrimmer [Reply | View]
Perhaps I have misunderstood, but the 2 examples of the grade_translator function on p3 of the article do not seem to have a selector. They appear to be examples of the "searched" case statement described lower down p3. I think they should read:
.....
CASE grade_in
WHEN 'A'
THEN retval := 'Excellent';
WHEN 'B'
THEN retval := 'Very Good';
.....
and
.....
CASE grade_in
WHEN 'A' THEN 'Excellent';
WHEN 'B' THEN 'Very Good';
.....
CREATE OR REPLACE FUNCTION
grade_translator (grade_in IN VARCHAR2)
RETURN VARCHAR2
IS
retval VARCHAR2(100);
BEGIN
CASE
WHEN grade_in = 'A'
THEN retval := 'Excellent';
WHEN grade_in = 'B'
THEN retval := 'Very Good';
WHEN grade_in = 'C'
THEN retval := 'Good';
WHEN grade_in = 'D'
THEN retval := 'Fair';
WHEN grade_in = 'F'
THEN retval := 'Poor';
ELSE retval := 'No such grade';
END CASE;
RETURN retval;
END;
...and...
CREATE OR REPLACE FUNCTION
grade_translator (grade_in IN VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
RETURN
CASE
WHEN grade_in = 'A'
THEN 'Excellent'
WHEN grade_in = 'B'
THEN 'Very Good'
WHEN grade_in = 'C'
THEN 'Good'
WHEN grade_in = 'D'
THEN 'Fair'
WHEN grade_in = 'F'
THEN 'Poor'
ELSE 'No such grade'
END;
END;
While they both do compile and run OK, chrisrimmer is right - they're not the best examples of good style.
In the case that the WHEN at every leg is an equality test on the same expression, then you should pull it out to the top of the CASE as you show.
So we should have used an example like this...
FUNCTION
grade_translator (grade_in IN VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
RETURN
CASE
WHEN grade_in = 'A'
THEN 'Excellent'
WHEN grade_in = 'B'
THEN 'Very Good'
WHEN grade_in = 'C'
THEN 'Good'
WHEN grade_in IN ('D', 'E', 'F' )
THEN 'Could do better'
ELSE 'No such grade'
END;
END;
Thanks! Bryn.