oracle-developer.net

encapsulating bulk pl/sql exceptions

One of the features of bulk PL/SQL processing is the SAVE EXCEPTIONS extension to FORALL. This clause has been available since Oracle 9i and instructs Oracle to skip any exceptions it might encounter during a FORALL DML operation. It enables us to continue processing a batch of data to completion rather than fail the entire statement. For example, we might be inserting 500 records using FORALL and if 1 record raises an exception, the remaining 499 rows will be successfully loaded. The bad row will be "set aside" for post-processing.

Most systems have an error-logging mechanism to write details of processing failures to files, tables or even queues. Most commonly this mechanism comprises a centralised error package and error-logging table, which will typically contain information such as timestamp, business date, failing package/procedure/function, some details on the nature of the exception (such as SQLERRM) and the keys to the source data that caused the exception (where applicable). Exception handling in these cases is quite simple: each procedure makes a single call to the error package, which in turns writes the exception details to the table/file/queue and optionally raises it.

One of the "features" of the SAVE EXCEPTIONS clause, however, is that the exception handling is quite code-intensive (i.e. we need to write quite a few lines of code to process the exceptions data). An example of this can be found in this oracle-developer.net article on 9i bulk PL/SQL features. It therefore makes sense for us to try to encapsulate this processing in an error-logging package and this article will suggest two methods for this, using the following Oracle features:

ANYDATA; and

type substitution ("polymorphism").

It is assumed that readers are familiar with these features of Oracle. For some background on these techniques, begin by reading the oracle-developer.net articles on ANYDATA and type enhancements in 9i (in particular the section on type polymorphism).

The examples in this article have been tested on 9i Release 2 (9.2) and should also work on any version of 10g.

setup: a simple error logger

As stated, we will be encapsulating FORALL .. SAVE EXCEPTIONS handling in an error package. We will start by building the error logging application that we wish to extend to include the new bulk handler. Like most systems, this package will record processing exceptions in an errors table. To keep this as simple as possible, we will exclude the rollback/raise management that such an error package should ideally encapsulate. We will begin by creating a simple error logging table (note that keys, constraints, indexes etc are deliberately ignored as they add nothing to the examples).

Before we move onto the main subject of this article, we can see how the error logger might typically be used in a "traditional" PL/SQL data processing routine. Note that the following anonymous block is an approximation of a daily processing procedure. Elements such as business date will usually be passed as parameters.

DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 36

We can see that the exception handling is simplified by the error package, especially if it encapsulates ROLLBACK and RAISE logic as well (which typically it should though for simplicity we've ignored it for this article). The aim is to hand-off all exception handling mechanics to the error package.

Finally, using Tom Kyte's print_table procedure to make the format easier to read, we can see our single logged exception as follows.

Now that we have some context (i.e. we have an error logging package in place), we can move on to how we might log errors in bulk.

encapsulating bulk exceptions

Using the error logging framework that we have created above, we could quite simply decide to manage exception logging in every processing routine we write. We would need to code a loop through SQL%BULK_EXCEPTIONS, determine the bad business data and make associated calls to the ERROR.LOG procedure to record the exceptional data. However, this would be a lot of repetition across multiple procedures and, as stated in the introduction, this can be code-intensive. It is far better, therefore, to encapsulate this extension of exception handling, as we shall now see.

There are two primary elements we need to consider for this encapsulation. First, we need to process the SQL%BULK_EXCEPTIONS pseudo-array that Oracle populates following an exception with a FORALL statement (with or without SAVE EXCEPTIONS). Second, we use the metadata in this pseudo-array to determine the locations of the exceptional business data and the exceptions themselves. This means that an encapsulated error logger will need to accept an array of business data in any format. It is this requirement that leads us to the two Oracle features described in the introduction; namely ANYDATA and polymorphism. We will begin with ANYDATA.

encapsulating with anydata

ANYDATA is a built-in type that has been available since Oracle 9i. We can use ANYDATA as a container to store any form of structured data for which we have a named SQL type (either built-in or user-defined). We can exploit this feature to encapsulate generic collection handling (such as that required for bulk exceptions).

We will add the following components to our existing error logging application:

a generic collection type of VARCHAR2(4000) to be used in the implementation;

a generic collection type of NUMBER to be used in the implementation; and

an overloaded ERROR.LOG procedure to accept an ANYDATA parameter (of the business data we were processing at the time of failure).

We will begin by creating the generic collection types that will "assist" with the encapsulation as follows.

It will become clear how these are used when we add our encapsulation to the ERROR package. First we will add an overloaded LOG procedure to the package specification. For brevity, the original LOG procedure is removed from the output (though it still exists of course).

This overloaded LOG procedure takes similar parameters to the original, single-row version, with the key difference being that we now have the ability to pass in a collection of business data via the ANYDATA type. This business data will be held in the collection that we are processing when FORALL .. SAVE EXCEPTIONS is invoked (i.e. when we hit an exception). We have also encapsulated the exception that Oracle raises when this happens (ORA-24381).

We can now implement the overloaded LOG procedure by re-creating the package body as follows. Again, the original LOG procedure is removed from the output for brevity.

Line 46: we determine the name of the collection type that defines the data "inside" the ANYDATA instance. We will need to use this in a dynamic PL/SQL block to access the ANYDATA;

Lines 51-54: we set-aside the offsets of the elements in the business data collection that raised exceptions. This is because the Native Dynamic PL/SQL block that follows cannot reference SQL%BULK_EXCEPTIONS in this context and will need some way of identifying these index values;

Lines 62-75: we build a Native Dynamic PL/SQL block to retrieve the exceptional business keys from the ANYDATA instance. We start by retrieving the ANYDATA instance into a variable of the correct underlying collection type. Using the error index collection we built earlier, the dynamic block loops through the business collection and derives the keys of the "bad" business data. These keys are added to a collection to be passed as an OUT bind variable;

Lines 72: we ensure that all collection types used in FORALL .. SAVE EXCEPTIONS constructs have a PRINT_KEY() member function to simlify the retrieval of business key values;

Lines 80-82: we execute the dynamic PL/SQL block and receive a collection of business keys for "bad" records as an OUT parameter; and

Lines 88-98: we process the BULK_EXCEPTIONS pseudo-array and invoke the original ERROR.LOG procedure for each exception we encounter, including all of the critical information we have extracted (including business keys).

We now have an extension to our error logger/handler that enables us to work with bulk PL/SQL and capture the exceptions without having to repeatedly code a complicated exception block. To test this, we will build a dummy customer table and load it with bulk PL/SQL, ensuring we have some "bad" data. We will begin by creating a CUSTOMERS table as follows.

One of the pre-requisites of being able to pass around collections of records with ANYDATA is that we use SQL object and collection types (i.e. types creates using the CREATE TYPE... syntax). Most developers will be familiar with using PL/SQL types (records and associative arrays that are declared in a package or procedure) for array processing. It is not much of a diversion to use objects and collections and, in some cases, using the SQL types provides greater flexibility than the PL/SQL-only type structures. Given this, we will now create a customer object to define a "record" of CUSTOMERS source data as follows.

Note that we have included a member function named PRINT_KEY. This is for convenience. Remember that the dynamic PL/SQL block in the error logger will invoke this to extract the business keys of the exceptional data. To work with multiple records of customer data, we must create a collection type of the customer "record", which we do as follows.

SQL> CREATE TYPE customer_ntt AS TABLE OF customer_ot;
2 /

Type created.

We now have the elements we require to test the encapsulated FORALL .. SAVE EXCEPTIONS handler. The following anonymous block represents a batch load of CUSTOMERS. We will fetch the source data first and then manufacture two duplicate records to ensure our subsequent FORALL .. SAVE EXCEPTIONS construct hits some exceptions. Note that we would usually expect this load to include some complex transformations between the fetch and load stages (else we would be using bulk SQL and not PL/SQL), but these are assumed and omitted for brevity.

We can see from the error messages that we found some exceptions during our processing. Before we examine these and determine whether our encapsulation works, it might be helpful to comment on some of the syntax in the load example. Using SQL objects/collections, rather than PL/SQL records/arrays, requires that we modify our syntax slightly as follows:

Lines 12-17: to bulk collect a set of columns into a collection of an object, we "wrap" the columns with the object's constructor. This casts the separate columns into attributes of a single object structure;

Lines 32-37: to use FORALL .. INSERT with collections of objects, we have two options. One is to cast every attribute using a technique described in this article. The other is to insert into an object view of the target table, which is what we have used in our load above. This enables Oracle to map each instance of the object type in the collection to the columns in the target table. From 9i, we can declare this object view in-line as seen above; and

Line 46: the collection of customer data is converted to an instance of ANYDATA using the type's convertCollection static function.

Remember that we rigged two duplicate rows in our example CUSTOMERS load. We can now query our ERRORS table to see if both exceptions were logged. Note that this table was truncated before running the example load above.

To summarise, therefore, the key elements of the encapsulation of FORALL .. SAVE EXCEPTIONS with ANYDATA are as follows:

each business load package requires an additional object type and a collection type of this object. The object type represents a single record of data that is to be loaded to the target table. The collection is simply an array of this record structure. The object type contains a PRINT_KEY member function to simplify access to the business data that we wish to log (in our example we have just printed the record's primary key);

any exceptions we encounter during FORALL bulk processing are passed off to the ERROR.LOG API as an instance of ANYDATA; and

the ERROR.LOG procedure is overloaded to accept a SYS.ANYDATA parameter. This parameter stores a collection of the business data that was being loaded at the time of hitting the exception(s). This business data can be of any structure, as defined by relevant underlying object and collection types.

encapsulating with type substitution

In the previous section, we saw that ANYDATA enables us to encapsulate the processing of the SQL%BULK_EXCEPTIONS pseudo-array and the related business data. One of the drawbacks of the ANYDATA method is that it requires dynamic PL/SQL that will be generated and executed every time the error logger is called. An alternative to this method is to use type substitution (known as polymorphism) and we will now build an example of how we might implement this.

Briefly, polymorphism enables us to create a hierarchy of types and use any of the subtypes wherever their respective supertypes are expected. We can take advantage of this to build a data-loading framework that utilises a single supertype as a consistent parameter type and yet uses underlying types of different structures for specific load targets. In relation to our encapsulated FORALL .. SAVE EXCEPTIONS handler, we will create a single "generic" load type as follows.

Note how we define this type as being NOT FINAL and NOT INSTANTIABLE. The former means we have not yet completed the implementation of the type hierarchy (of which BULK_LOAD_OT is a supertype) and the latter means that we will not be able to directly use this type for variables in our PL/SQL programs. We create this type with a single ID attribute that will be inherited by all subtypes and also a PRINT_KEY member function, as before, that will print the current value of the business key contained within the data structure.

As we have a member function, we must also have a type body. Often with polymorphism, we might exclude a type body in the supertype and instead include an overriding function in every subtype. For simplicity, we will create one function in the BULK_LOAD_OT supertype as follows and allow all subtypes to inherit and use this function.

The specification differs from the ANYDATA version only by the type of the p_business_data parameter. This time, we use the BULK_LOAD_NTT type for the business data, which is a collection type based on BULK_LOAD_OT. As we will see later, this means that the collection can contain data of any subtype in a type hierarchy created under BULK_LOAD_OT. Before we can see this, we must create the package body for our new LOG overload as follows.

We can see immediately that this implementation is far simpler than the ANYDATA example from earlier. We are working with a known collection type (BULK_LOAD_NTT) and we also know that each "record" in this collection will have a PRINT_KEY member function. These two factors make the processing of the SQL%BULK_EXCEPTIONS pseudo-array and the probing of the business data collection much easier. The resulting code is therefore short, simple and self-explanatory.

We are now ready to test our implementation. We can see that the ERROR.LOG encapsulation expects an instance of BULK_LOAD_NTT. By using type substitution, we can pass in a collection of any subtype that is defined under BULK_LOAD_OT. Using our CUSTOMERS example from earlier, we will now create a CUSTOMER_OT subtype under BULK_LOAD_OT as follows.

This subtype inherits the GENERIC_ID attribute and PRINT_KEY member function from the BULK_LOAD_OT supertype. It represents a "record" of customer-specific data, yet can be used wherever a BULK_LOAD_OT record is expected. We will test this using the same example that we used to demonstrate the ANYDATA method. In other words, we will bulk fetch some "source" data, manufacture two exceptions and load the CUSTOMERS table using FORALL .. SAVE EXCEPTIONS.

As before, our two exceptions generated the Oracle error we expected. There are a few small differences between this example and the ANYDATA example, most notably the following:

Line 7: we are fetching into and loading from a collection of the BULK_LOAD_NTT type, rather than a specific customer data collection;

Line 12: we use the CUSTOMER_OT constructor to convert the source data columns into the correct format for bulk fetching into the business data collection. Because CUSTOMER_OT is a subtype of BULK_LOAD_OT, it can be used in the BULK_LOAD_NTT collection instance (this is the benefit of type substitution);

Line 37: because we have substituted our CUSTOMER_OT data into a BULK_LOAD_NTT collection, Oracle now considers each element in our collection to be of the BULK_LOAD_OT structure. We must tell Oracle that we have in fact loaded the collection with multiple instances of CUSTOMER_OT instead (using type substitution). We do this when we access the data by using the TREAT function to "downcast" the data to its correct subtype. Quite simply, we have converted the type both on the "way in" and on the "way out" of the collection.

Finally, we can confirm that our encapsulation works by checking the ERRORS table (this was truncated before the previous example was executed).

In summary, the key elements of the type substitution method for encapsulating bulk exceptions are listed below.

we have a single "top-level" generic object type and collection type of this object. This single supertype is used to define generic parameters where the incoming data might be of different structures. The object type has a single ID attribute to be used by subtypes as appropriate and also a PRINT_KEY member function for simplifying access to the business data;

each business load package requires its own object type to be created as a subtype of the single supertype. Each subtype represents a single record of data that is to be loaded to the target table and replaces the use of a PL/SQL record. The subtypes can optionally override the PRINT_KEY member function if they need to access data other than that contained in the single ID attribute;

each business load package uses its own object type to structure each record but stores these in a variable of the generic collection type. This is made possible by type substitution and enables a single data type to be passed to generic APIs;

any exceptional business data encountered during the FORALL processing is passed off to the ERROR.LOG API in a generic collection; and

the ERROR.LOG procedure is overloaded to accept a BULK_LOAD_NTT parameter. This parameter stores a collection of the business data that was being loaded at the time of hitting the exception(s). This business data can be of any subtype structure in the overall type hierarchy that exists under the BULK_LOAD_OT supertype. In our example, we loaded customer data via this mechanism, but this could equally be of any other data format (ACCOUNTS, SALES and so on) as required.

summary

We have seen two methods for encapsulating SQL%BULK_EXCEPTIONS and logging the underlying business information during exceptions handling. We have managed to avoid lengthy and repetitive exception-handling in each load process we write. Instead, we hand off our business data to a generic utility that does this for us.

The ANYDATA method enables us to pass any format of data (defined as collections of SQL object types) to the API, whereas type substitution limits us to whatever we define in the object type hierarchy. The latter method, however, is much simpler to work with and the resulting implementation is much cleaner. Both methods require that we divert away from PL/SQL records and arrays and instead use SQL object types and collections in their place. As stated, however, the SQL types can provide much greater flexibility and scope than their PL/SQL-only counterparts.