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).

SQL> CREATE TABLE errors
  2  ( package_owner  VARCHAR2(30)
  3  , package_name   VARCHAR2(30)
  4  , procedure_name VARCHAR2(30)
  5  , action         VARCHAR2(100)
  6  , business_date  DATE
  7  , business_key   VARCHAR2(1000)
  8  , error_ts       TIMESTAMP
  9  , error_msg      VARCHAR2(4000)
 10  );

Table created.

Now we can create the simple error package. At this stage it doesn't handle bulk exceptions.

SQL> CREATE PACKAGE error AS
  2     PROCEDURE log( p_owner         IN errors.package_owner%TYPE,
  3                    p_package       IN errors.package_name%TYPE,
  4                    p_procedure     IN errors.procedure_name%TYPE,
  5                    p_action        IN errors.action%TYPE,
  6                    p_business_date IN errors.business_date%TYPE,
  7                    p_business_key  IN errors.business_key%TYPE DEFAULT NULL,
  8                    p_error         IN VARCHAR2 DEFAULT NULL );
  9  END;
 10  /

Package created.

So far we have a single procedure to log a single exception. The implementation of this logging procedure will typically be constructed as follows.

SQL> CREATE PACKAGE BODY error AS
  2
  3     PROCEDURE log( p_owner         IN errors.package_owner%TYPE,
  4                    p_package       IN errors.package_name%TYPE,
  5                    p_procedure     IN errors.procedure_name%TYPE,
  6                    p_action        IN errors.action%TYPE,
  7                    p_business_date IN errors.business_date%TYPE,
  8                    p_business_key  IN errors.business_key%TYPE DEFAULT NULL,
  9                    p_error         IN VARCHAR2 DEFAULT NULL ) IS
 10
 11        v_error errors.error_msg%TYPE := NVL(p_error,SQLERRM);
 12
 13        PRAGMA AUTONOMOUS_TRANSACTION;
 14
 15     BEGIN
 16
 17        INSERT INTO errors
 18           ( package_owner, package_name, procedure_name, action,
 19             business_date, business_key, error_ts, error_msg )
 20        VALUES
 21           ( p_owner, p_package, p_procedure, p_action,
 22             p_business_date, p_business_key, SYSTIMESTAMP, v_error );
 23
 24        COMMIT;
 25
 26     END log;
 27
 28  END error;
 29  /

Package body created.

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.

SQL> DECLARE
  2
  3     v_package       errors.package_name%TYPE   := 'ANON. BLOCK';
  4     v_procedure     errors.procedure_name%TYPE := 'ANON. BLOCK';
  5     v_action        errors.action%TYPE;
  6     v_business_date errors.business_date%TYPE  := TRUNC(SYSDATE)-1;
  7     v_business_key  errors.business_key%TYPE;
  8
  9  BEGIN
 10
 11     v_action := 'Process source data';
 12     FOR r IN ( SELECT object_id   AS key_attr1
 13                ,      object_name AS key_attr2
 14                ,      object_type AS data_attr1
 15                FROM   user_objects )
 16     LOOP
 17
 18        v_action := 'Assign business key';
 19        v_business_key := r.key_attr1 || ',' || r.key_attr2;
 20
 21        v_action := 'Transformation and business rules';
 22        r.data_attr1 := RPAD('Oops',4000);  --<-- oh dear
 23
 24     END LOOP;
 25
 26  EXCEPTION
 27     WHEN OTHERS THEN
 28        error.log( p_owner         => USER,
 29                   p_package       => v_package,
 30                   p_procedure     => v_procedure,
 31                   p_action        => v_action,
 32                   p_business_date => v_business_date,
 33                   p_business_key  => v_business_key );
 34
 35        ROLLBACK; --<-- error package should encapsulate this...
 36        RAISE;    --<-- error package should also encapsulate this...
 37  END;
 38  /

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.

SQL> exec print_table('SELECT * FROM errors');

PACKAGE_OWNER   : SCOTT
PACKAGE_NAME    : ANON. BLOCK
PROCEDURE_NAME  : ANON. BLOCK
ACTION          : Transformation and business rules
BUSINESS_DATE   : 25-jul-2007 00:00:00
BUSINESS_KEY    : 33944,ANOTHER_SUBTYPE_OT
ERROR_TS        : 26-JUL-07 18.24.37.640000
ERROR_MSG       : ORA-06502: PL/SQL: numeric or value error: character string buffer too small
-----------------

PL/SQL procedure successfully completed.

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.

SQL> CREATE OR REPLACE TYPE varchar2_ntt AS TABLE OF VARCHAR2(4000);
  2  /

Type created.

SQL> CREATE OR REPLACE TYPE number_ntt AS TABLE OF NUMBER;
  2  /

Type created.

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).

SQL> CREATE OR REPLACE PACKAGE error AS
  2
  3     PROCEDURE log( ...snip...
 10
 11     bulk_exceptions EXCEPTION;
 12     PRAGMA EXCEPTION_INIT(bulk_exceptions, -24381);
 13
 14     PROCEDURE log( p_owner         IN errors.package_owner%TYPE,
 15                    p_package       IN errors.package_name%TYPE,
 16                    p_procedure     IN errors.procedure_name%TYPE,
 17                    p_action        IN errors.action%TYPE,
 18                    p_business_date IN errors.business_date%TYPE,
 19                    p_business_data IN ANYDATA );
 20
 21  END;
 22  /

Package created.

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.

SQL> CREATE OR REPLACE PACKAGE BODY error AS
  2
  3     PROCEDURE log( ...snip...
 27
 28     PROCEDURE log( p_owner         IN errors.package_owner%TYPE,
 29                    p_package       IN errors.package_name%TYPE,
 30                    p_procedure     IN errors.procedure_name%TYPE,
 31                    p_action        IN errors.action%TYPE,
 32                    p_business_date IN errors.business_date%TYPE,
 33                    p_business_data IN ANYDATA ) IS
 34
 35        v_plsql          VARCHAR2(1024);                 --<-- dynamic PL/SQL block
 36        v_type_name      VARCHAR2(61);                   --<-- underlying type name
 37        nt_error_indices number_ntt   := number_ntt();   --<-- offsets to bad data
 38        nt_business_keys varchar2_ntt := varchar2_ntt(); --<-- keys of bad data
 39
 40     BEGIN
 41
 42        /*
 43        || Determine the details of the collection type contained within the
 44        || ANYDATA parameter...
 45        */
 46        v_type_name := p_business_data.gettypename;
 47
 48        /*
 49        || Determine where in our business data collection the bad records are...
 50        */
 51        FOR i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
 52           nt_error_indices.EXTEND;
 53           nt_error_indices(nt_error_indices.LAST) := SQL%BULK_EXCEPTIONS(i).error_index;
 54        END LOOP;
 55
 56        /*
 57        || Build a PL/SQL block to accept the ANYDATA and a collection of error indices.
 58        || This will extract the collection from the ANYDATA instance and probe it
 59        || with the error indices from SQL%BULK_EXCEPTIONS, building up a return
 60        || collection of the bad data keys...
 61        */
 62        v_plsql := 'DECLARE '
 63                || '   n PLS_INTEGER; '
 64                || '   c ' || v_type_name || '; '
 65                || '   a SYS.ANYDATA  := :b1; '
 66                || '   x number_ntt   := :b2; '
 67                || '   o varchar2_ntt := varchar2_ntt(); '
 68                || 'BEGIN'
 69                || '   n := a.GetCollection(c); '
 70                || '   FOR i IN x.FIRST .. x.LAST LOOP '
 71                || '      o.EXTEND; '
 72                || '      o(o.LAST) := c(x(i)).print_key; '
 73                || '   END LOOP; '
 74                || '   :b3 := o; '
 75                || 'END; ';
 76
 77        /*
 78        || Execute the PL/SQL string to return the bad data keys...
 79        */
 80        EXECUTE IMMEDIATE v_plsql USING IN  p_business_data,
 81                                        IN  nt_error_indices,
 82                                        OUT nt_business_keys;
 83
 84
 85        /*
 86        || Now we can log the errors...
 87        */
 88        FOR i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
 89
 90           error.log( p_owner         => p_owner,
 91                      p_package       => p_package,
 92                      p_procedure     => p_procedure,
 93                      p_action        => p_action,
 94                      p_business_date => p_business_date,
 95                      p_business_key  => nt_business_keys(i),
 96                      p_error         => SQLERRM(-1*SQL%BULK_EXCEPTIONS(i).error_code) );
 97
 98        END LOOP;
 99
100     END log;
101
102  END error;
103  /

Package body created.

Note the following elements of the bulk exceptions wrapper above:

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.

SQL> CREATE TABLE customers
  2  ( customer_id   NUMBER PRIMARY KEY
  3  , first_name    VARCHAR2(30)
  4  , last_name     VARCHAR2(50)
  5  , start_date    DATE
  6  );

Table created.

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.

SQL> CREATE TYPE customer_ot AS OBJECT
  2  ( customer_id   NUMBER
  3  , first_name    VARCHAR2(30)
  4  , last_name     VARCHAR2(50)
  5  , start_date    DATE
  6  , MEMBER FUNCTION print_key RETURN VARCHAR2
  7  );
  8  /

Type created.

SQL> CREATE TYPE BODY customer_ot AS
  2     MEMBER FUNCTION print_key RETURN VARCHAR2 IS
  3     BEGIN
  4        RETURN TO_CHAR(SELF.customer_id);
  5     END;
  6  END;
  7  /

Type body created.

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.

SQL> DECLARE
  2
  3     v_package        errors.package_name%TYPE   := 'ANYDATA_ENCAPSULATION';
  4     v_procedure      errors.procedure_name%TYPE := 'CUSTOMER_LOAD_EG';
  5     v_action         errors.action%TYPE;
  6     v_business_date  errors.business_date%TYPE := TRUNC(SYSDATE)-1;
  7     nt_customer_data customer_ntt := customer_ntt();
  8
  9  BEGIN
 10
 11     v_action := 'Fetch source data';
 12     SELECT customer_ot(
 13               object_id,   --<-- customer_id
 14               object_type, --<-- first_name
 15               object_name, --<-- last_name
 16               created      --<-- start_date
 17               )
 18     BULK COLLECT INTO nt_customer_data
 19     FROM   all_objects
 20     WHERE  ROWNUM < 9;
 21
 22     /*
 23     || Add two duplicates to ensure we hit SAVE EXCEPTIONS...
 24     */
 25     FOR i IN 1 .. 2 LOOP
 26        nt_customer_data.EXTEND;
 27        nt_customer_data(nt_customer_data.LAST) := nt_customer_data(i);
 28     END LOOP;
 29
 30     v_action := 'Customer load';
 31     FORALL i IN 1 .. nt_customer_data.COUNT SAVE EXCEPTIONS
 32        INSERT INTO (SELECT customer_ot( customer_id,
 33                                         first_name,
 34                                         last_name,
 35                                         start_date )
 36                     FROM   customers)
 37        VALUES (nt_customer_data(i));
 38
 39  EXCEPTION
 40     WHEN error.bulk_exceptions THEN
 41        error.log( p_owner         => USER,
 42                   p_package       => v_package,
 43                   p_procedure     => v_procedure,
 44                   p_action        => v_action,
 45                   p_business_date => v_business_date,
 46                   p_business_data => ANYDATA.convertCollection(nt_customer_data) );
 47
 48        ROLLBACK; --<-- error package should encapsulate this...
 49        RAISE;    --<-- error package should also encapsulate this...
 50  END;
 51  /

ERROR:
ORA-24381: error(s) in array DML
ORA-06512: at line 49



Warning: PL/SQL compilation errors.

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.

SQL> exec print_table('SELECT * FROM errors');

PACKAGE_OWNER      : SCOTT
PACKAGE_NAME       : ANYDATA_ENCAPSULATION
PROCEDURE_NAME     : CUSTOMER_LOAD_EG
ACTION             : Customer load
BUSINESS_DATE      : 31-jul-2007 00:00:00
BUSINESS_KEY       : 17286
ERROR_TS           : 01-AUG-07 18.25.11.859000
ERROR_MSG          : ORA-00001: unique constraint (.) violated
-----------------
PACKAGE_OWNER      : SCOTT
PACKAGE_NAME       : ANYDATA_ENCAPSULATION
PROCEDURE_NAME     : CUSTOMER_LOAD_EG
ACTION             : Customer load
BUSINESS_DATE      : 31-jul-2007 00:00:00
BUSINESS_KEY       : 7559
ERROR_TS           : 01-AUG-07 18.25.11.859000
ERROR_MSG          : ORA-00001: unique constraint (.) violated
-----------------

PL/SQL procedure successfully completed.

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.

SQL> CREATE TYPE bulk_load_ot AS OBJECT
  2  ( generic_id NUMBER
  3  , MEMBER FUNCTION print_key RETURN VARCHAR2
  4  ) NOT FINAL NOT INSTANTIABLE;
  5  /

Type created.

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.

SQL> CREATE TYPE BODY bulk_load_ot AS
  2     MEMBER FUNCTION print_key RETURN VARCHAR2 IS
  3     BEGIN
  4        RETURN TO_CHAR(SELF.generic_id);
  5     END;
  6  END;
  7  /

Type body created.

Finally, because we are dealing with collections of data and not single records, we will create a collection type based on our BULK_LOAD_OT object.

SQL> CREATE TYPE bulk_load_ntt AS TABLE OF bulk_load_ot;
  2  /

Type created.

We now have the elements we require to implement another version of the ERROR.LOG procedure. The ERROR package specification is as follows (the original LOG procedure definition is cut for brevity).

SQL> CREATE OR REPLACE PACKAGE error AS
  2
  3     PROCEDURE log( ...snip...
 10
 11     bulk_exceptions EXCEPTION;
 12     PRAGMA EXCEPTION_INIT(bulk_exceptions, -24381);
 13
 14     PROCEDURE log( p_owner         IN errors.package_owner%TYPE,
 15                    p_package       IN errors.package_name%TYPE,
 16                    p_procedure     IN errors.procedure_name%TYPE,
 17                    p_action        IN errors.action%TYPE,
 18                    p_business_date IN errors.business_date%TYPE,
 19                    p_business_data IN bulk_load_ntt );
 20
 21  END;
 22  /

Package created.

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.

SQL> CREATE OR REPLACE PACKAGE BODY error AS
  2
  3     PROCEDURE log( ...snip...
 27
 28     PROCEDURE log( p_owner         IN errors.package_owner%TYPE,
 29                    p_package       IN errors.package_name%TYPE,
 30                    p_procedure     IN errors.procedure_name%TYPE,
 31                    p_action        IN errors.action%TYPE,
 32                    p_business_date IN errors.business_date%TYPE,
 33                    p_business_data IN bulk_load_ntt ) IS
 34
 35        v_error_index PLS_INTEGER;
 36        v_error_code  PLS_INTEGER;
 37
 38     BEGIN
 39
 40        /*
 41        || Simply log the errors...
 42        */
 43        FOR i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
 44
 45           v_error_index := SQL%BULK_EXCEPTIONS(i).error_index;
 46           v_error_code  := SQL%BULK_EXCEPTIONS(i).error_code;
 47
 48           error.log( p_owner         => p_owner,
 49                      p_package       => p_package,
 50                      p_procedure     => p_procedure,
 51                      p_action        => p_action,
 52                      p_business_date => p_business_date,
 53                      p_business_key  => p_business_data(v_error_index).print_key(),
 54                      p_error         => SQLERRM(-1 * v_error_code) );
 55
 56        END LOOP;
 57
 58     END log;
 59
 60  END error;
 61  /

Package body created.

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.

SQL> CREATE TYPE customer_ot UNDER bulk_load_ot
  2  ( first_name    VARCHAR2(30)
  3  , last_name     VARCHAR2(50)
  4  , start_date    DATE
  5  );
  6  /

Type created.

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.

SQL> DECLARE
  2
  3     v_package        errors.package_name%TYPE   := 'SUBTYPE_ENCAPSULATION';
  4     v_procedure      errors.procedure_name%TYPE := 'CUSTOMER_LOAD_EG';
  5     v_action         errors.action%TYPE;
  6     v_business_date  errors.business_date%TYPE := TRUNC(SYSDATE)-1;
  7     nt_customer_data bulk_load_ntt := bulk_load_ntt();
  8
  9  BEGIN
 10
 11     v_action := 'Fetch source data';
 12     SELECT customer_ot(
 13               object_id,   --<-- generic_id in lieu of customer_id
 14               object_type, --<-- first_name
 15               object_name, --<-- last_name
 16               created      --<-- start_date
 17               )
 18     BULK COLLECT INTO nt_customer_data
 19     FROM   all_objects
 20     WHERE  ROWNUM < 10;
 21
 22     /*
 23     || Add two duplicates to ensure we hit SAVE EXCEPTIONS...
 24     */
 25     FOR i IN 1 .. 2 LOOP
 26        nt_customer_data.EXTEND;
 27        nt_customer_data(nt_customer_data.LAST) := nt_customer_data(i);
 28     END LOOP;
 29
 30     v_action := 'Customer load';
 31     FORALL i IN 1 .. nt_customer_data.COUNT SAVE EXCEPTIONS
 32        INSERT INTO (SELECT customer_ot( customer_id,
 33                                         first_name,
 34                                         last_name,
 35                                         start_date )
 36                     FROM   customers)
 37        VALUES (TREAT(nt_customer_data(i) AS customer_ot));
 38
 39  EXCEPTION
 40     WHEN error.bulk_exceptions THEN
 41        error.log( p_owner         => USER,
 42                   p_package       => v_package,
 43                   p_procedure     => v_procedure,
 44                   p_action        => v_action,
 45                   p_business_date => v_business_date,
 46                   p_business_data => nt_customer_data );
 47
 48        ROLLBACK; --<-- error package should encapsulate this...
 49        RAISE;    --<-- error package should also encapsulate this...
 50  END;
 51  /

ERROR:
ORA-24381: error(s) in array DML
ORA-06512: at line 49


Warning: PL/SQL compilation errors.

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).

SQL> exec print_table('SELECT * FROM errors');

PACKAGE_OWNER      : SCOTT
PACKAGE_NAME       : SUBTYPE_ENCAPSULATION
PROCEDURE_NAME     : CUSTOMER_LOAD_EG
ACTION             : Customer load
BUSINESS_DATE      : 31-jul-2007 00:00:00
BUSINESS_KEY       : 17286
ERROR_TS           : 01-AUG-07 18.27.49.625000
ERROR_MSG          : ORA-00001: unique constraint (.) violated
-----------------
PACKAGE_OWNER      : SCOTT
PACKAGE_NAME       : SUBTYPE_ENCAPSULATION
PROCEDURE_NAME     : CUSTOMER_LOAD_EG
ACTION             : Customer load
BUSINESS_DATE      : 31-jul-2007 00:00:00
BUSINESS_KEY       : 7559
ERROR_TS           : 01-AUG-07 18.27.49.625000
ERROR_MSG          : ORA-00001: unique constraint (.) violated
-----------------

PL/SQL procedure successfully completed.

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.

source code

The source code for the examples in this article can be downloaded from here.

Adrian Billington, July 2007

oracle-developer.net