Handle bulk binding exceptions in PL/SQL

by Bob Watkins | Aug 21, 2006 9:19:00 PM

Tags: Programming languages, Databases, Bob Watkins, Listing C, Oracle Corp., FORALL, SAVE EXCEPTIONS, SQL%BULK_EXCEPTIONS, PL/SQL, Oracle Tips Newsletter

Takeaway: The downside to bulk binding in PL/SQL is that if any of the data values result in an invalid UPDATE, the entire set of iterations is rolled back. Bob Watkins explains how you can get around this issue.

Bulk binding in PL/SQL is an important technique for efficient processing of dynamic DML. Instead of looping within your program, and submitting one UPDATE statement with each iteration, you use the FORALL statement to submit a single UPDATE statement with bind variables in it. Collections, such as nested tables or VARRAYs, are used to pass multiple data values to the bind variables.

The result is a set of iterations executed within a single trip to the SQL Server engine, instead of alternating between SQL and PL/SQL. This reduces the overhead of context switching between the two engines. The downside is that if any of the data values result in an invalid UPDATE, the entire set of iterations is rolled back.

To get around this, you can allow some of the UPDATE iterations to complete, but return a list of the ones that failed. The two syntax elements needed to do this were introduced in Oracle 9i: the SAVE EXCEPTIONS clause of FORALL, and a cursor attribute named SQL%BULK_EXCEPTIONS.

SQL%BULK_EXCEPTIONS is a complex data type (record) with two fields: ERROR_INDEX is the iteration that caused the error, and ERROR_CODE is the Oracle error message number describing the error. (Use the SQLERRM function to get the full message text.)

In Listing A, I create a SALES_QUOTA table in the OE schema. Listing B attempts to load the table from existing data in the ORDERS table, but without the SAVE EXCEPTIONS clause. Some of the rows will fail because the calculated value exceeds the column precision. From the run results, you can see that none of the rows was updated—not even the ones that contained correct data.

Listing C adds the words SAVE EXCEPTIONS to the FORALL, and an EXCEPTION section to the block. The SQL%BULK_EXCEPTIONS cursor attribute is processed to obtain the iterations that failed. For each iteration, the SQL error message and the data used in the iteration are returned. Listing D shows the run output.

Miss a tip?

Check out the Oracle archive, and catch up on our most recent Oracle tips.

Bob Watkins (OCP, MCDBA, MCSE, MCT) is a computer professional with 25 years of experience as a technical trainer, consultant, and database administrator. Visit Bob's site.