Speed Isn’t Everything – LOG ERRORS, SAVE EXCEPTIONS and Fun in the Snow

Before the fourth one-day international, Mitchell Johnson decided to shave off his moustache.
During the fourth one-day international, Mitchell returned the less than impressive figures of 0-72 off 10 overs.
At the end of the fourth one-day international, England had finally notched a win against Australia.
The logical conclusion to draw from all of this is that Mitchell Johnson is not a regular reader of this blog.

On the plus side, I did get a couple of interesting comments from my last post about the performance differences between Log Errors and Save Exceptions.
As well as Jim Dickson’s input, Steve Feuerstein made some observations on the Toadworld site (which you can see here if you’re interested).

These comments both had a similar theme to the effect that, whilst Log Errors and Save Exceptions are similar, there are some differences beyond their relative performance.

So, the aim of this post is to take a fresh look at these two mechanisms and how they compare.
For the code examples, I’m going to step away from the horror show that has been England’s cricket tour of Australia, and focus instead on the wacky world of Reality TV.

We’ve had celebrity high-diving, celebrity ballroom dancing, even celebrity dog-training.
With the Winter Olympics almost upon us, some particularly sadistic TV executive hit on the idea of assembling a collection of celebrities, strapping a plank of wood to each foot/handing them a tea-tray…and then pushing them off the side of a mountain.
All a bit of harmless fun. After all, what could possibly go wrong ?
Having said that, the producers of The Jump did hire a couple of extra cast members to account for the remote possibility that a broken rib/collar-bone/finger-nail might render one or more of the original contestants incapacitated.

The Data

Here we are then, the data that we want to load into our final target table :

CREATE TABLE contestants
(
    first_name varchar2(50),
    last_name varchar2(50),
    orig_cast_flag varchar2(1)
)
/

INSERT INTO contestants( first_name, last_name, orig_cast_flag)
VALUES( 'DONAL', 'MACINTYRE', NULL)
/

INSERT INTO contestants( first_name, last_name, orig_cast_flag)
VALUES('JOE', 'MCELDERRY', NULL)
/

INSERT INTO contestants( first_name, last_name, orig_cast_flag)
VALUES('STEVE', 'REDGRAVE', 'Y')
/

INSERT INTO contestants( first_name, last_name, orig_cast_flag)
VALUES('SINITTA', NULL, 'Y')
/

INSERT INTO contestants( first_name, last_name, orig_cast_flag)
VALUES('RITCHIE', 'NEVILLE', 'Y')
/

INSERT INTO contestants( first_name, last_name, orig_cast_flag)
VALUES('NICKY','CLARKE', 'Y')
/

INSERT INTO contestants( first_name, last_name, orig_cast_flag)
VALUES('MELINDA', 'MESSANGER', 'Y')
/

INSERT INTO contestants( first_name, last_name, orig_cast_flag)
VALUES('MARCUS', 'BRIGSTOCKE', 'Y')
/

INSERT INTO contestants( first_name, last_name, orig_cast_flag)
VALUES('LAURA', 'HAMILTON', 'Y')
/

INSERT INTO contestants( first_name, last_name, orig_cast_flag)
VALUES('KIMBERLY', 'WYATT', 'Y')
/

INSERT INTO contestants( first_name, last_name, orig_cast_flag)
VALUES('HENRY', 'CONWAY', 'Y')
/

INSERT INTO contestants( first_name, last_name, orig_cast_flag)
VALUES('DARREN', 'GOUGH', 'Y')
/

INSERT INTO contestants( first_name, last_name, orig_cast_flag)
VALUES('ANTHEA', 'TURNER', 'Y')
/

INSERT INTO contestants( first_name, last_name, orig_cast_flag)
VALUES('AMY', 'CHILDS', 'Y')
/

COMMIT;

Incidentally, if you’ve never heard of half of these people, I’m afraid I can’t help you there. I’m not really up on this sort of thing and only discovered last week that TOWIE was actually an acronym for a reality show and not the name of someone who features prominently in the gossip columns of some of the more lurid tabloids.

Anyway, the target table itself…

create table original_cast_members
(
    first_name VARCHAR2(50) NOT NULL,
    last_name VARCHAR2(50) NOT NULL
)
/

Looking at the table definition, it seems quite likely that we’re going to get some errrors when trying to load the records from the CONTESTANTS staging table into the target ORIGINAL_CAST_MEMBERS table.
So, we’re back to the LOG ERRORS or SAVE EXCEPTIONS mechanisms in order to ensure that one or more errors during the load do not cause the entire load to fail.

Log Errors

The LOG ERRORS mechanism is related to the ever-so-convenient DBMS_ERRLOG.CREATE_ERROR_LOG procedure which we can use to create a table to hold details of any dml errors on a given table.
In our case, we want details of errors for inserts into the ORIGINAL_CAST_MEMBERS table :

BEGIN
    DBMS_ERRLOG.CREATE_ERROR_LOG( dml_table_name => 'ORIGINAL_CAST_MEMBERS');
END;
/

This creates a table called ERR$_ORIGINAL_CAST_MEMBERS which looks like this :

 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 ORA_ERR_NUMBER$				    NUMBER
 ORA_ERR_MESG$					    VARCHAR2(2000)
 ORA_ERR_ROWID$ 				    ROWID
 ORA_ERR_OPTYP$ 				    VARCHAR2(2)
 ORA_ERR_TAG$					    VARCHAR2(2000)
 FIRST_NAME					    VARCHAR2(4000)
 LAST_NAME					    VARCHAR2(4000)

Now, not every DML statement that fails will be recorded in this table…

SQL> insert into original_cast_members values(NULL, 'SILLY');
insert into original_cast_members values(NULL, 'SILLY')
                                         *
ERROR at line 1:
ORA-01400: cannot insert NULL into
("MIKE"."ORIGINAL_CAST_MEMBERS"."FIRST_NAME")


SQL> select count(*) from err$_original_cast_members;

  COUNT(*)
----------
	 0

SQL> 

However, any errors on this table as the result of a DML statement including the LOG ERRORS table will be written here by default :

insert into original_cast_members values(NULL, 'SILLY')
  2* log errors reject limit unlimited
SQL> /

0 rows created.

SQL> select count(*) from err$_original_cast_members;

  COUNT(*)
----------
	 1

SQL> 

A couple of things are striking about this last example.
First, there’s no error message returned.
Secondly, although we didn’t tell it to, the statement has automatically written the error to the $ERR table we just created.

Taking a closer look at the error table…

SELECT ora_err_mesg$
FROM err$_original_cast_members;

…we find the error message…

ORA_ERR_MESG$
--------------------------------------------------------------------------------
ORA-01400: cannot insert NULL into ("MIKE"."ORIGINAL_CAST_MEMBERS"."FIRST_NAME")

LOG ERRORS has one more surprise up it’s sleeves.

LOG ERRORS and The Hidden Autonomous Transaction

To demonstrate, I’m going to truncate the error table (yes, I know I haven’t commited anything but bear with me), and try another statement. Incidentally, for what follows, I also make sure that autocommit is turned off :

set autocommit off
truncate table err$_original_cast_members;

…and check that there are no rows in either the target table or the error table…

SQL> select count(*) from original_cast_members;

  COUNT(*)
----------
	 0

SQL> select count(*) from err$_original_cast_members;

  COUNT(*)
----------
	 0

SQL> 

Now to run a log errors insert that looks like this (saved as insert_le.sql):

INSERT INTO original_cast_members( first_name, last_name)
    SELECT first_name, last_name
    FROM contestants
    WHERE NVL(orig_cast_flag, 'N') = 'Y'
    LOG ERRORS REJECT LIMIT UNLIMITED
/
SQL> @insert_le.sql

11 rows created.

SQL> select count(*) from original_cast_members;

  COUNT(*)
----------
	11

SQL> select count(*) from err$_original_cast_members;

  COUNT(*)
----------
	 1

SQL> 

Looks like I’ve hit an error somewhere, so maybe I’ll just rollback the entire transaction…

SQL> rollback;

Rollback complete.

SQL> select count(*) from original_cast_members;

  COUNT(*)
----------
	 0

SQL> select count(*) from err$_original_cast_members;

  COUNT(*)
----------
	 1

SQL> 

Interesting. Whilst the insert to the target table has been successfully rolled back, you’ll notice that the error table is still stubbornly clinging on to it’s record.

As Jim observed, it looks very much like LOG ERRORS inserts records into the error table in an autonomous transaction.
This behaviour becomes particularly useful when we want the transaction to fail if more than a given number of records error.
Let’s try a slightly modified example

ALTER TABLE original_cast_members
    ADD orig_cast_flag VARCHAR2(1) 
    CONSTRAINT chk_orig_only CHECK( orig_cast_flag = 'Y')
/

At this point, it’s worth noting that the error log table is not updated to account for the new column.
You can either drop and re-create the table using DBMS_ERRLOG as previously, or amend the structure of the table manually :

ALTER TABLE err$_original_cast_members ADD orig_cast_flag varchar2(4000);

Hang on, a VARCHAR2(4000) for a VARCHAR2(1) on the target table. Isn’t that a bit excessive ?
Well, one of the errors you may well catch is ORA-12899: value too large for column. If you do hit this, you’d probably like to know what the value that you were trying to set was.
Now, we want to try to run an insert statement which bails out when we get more than two errors (we should get three in this load) …

INSERT INTO original_cast_members( first_name, last_name, orig_cast_flag)
    SELECT first_name, last_name, NVL(orig_cast_flag, 'N')
    FROM contestants
    LOG ERRORS REJECT LIMIT 2
/

…now run it and…

SQL> @insert_le_max_err.sql
    SELECT first_name, last_name, NVL(orig_cast_flag, 'N')
                       *
ERROR at line 2:
ORA-01400: cannot insert NULL into ("MIKE"."ORIGINAL_CAST_MEMBERS"."LAST_NAME")


SQL> 

Now we’re getting an error back. We can also see that we haven’t successfully inserted any records into the target table. However, there are three records in the error table :

SQL> select count(*) from original_cast_members;

  COUNT(*)
----------
	 0

SQL> select count(*) from err$_original_cast_members;

  COUNT(*)
----------
	 3

SQL> 

So, although the statement has failed, we have the error records ready to investigate in the errors table.
All errors, up to and including the one that exceeded the limit we specified, will be recorded.
We can review these simply by :

SELECT ora_err_mesg$, first_name, last_name, orig_cast_flag
FROM err$_original_cast_members
/

The results :

ORA_ERR_MESG$									 FIRST_NAME	 LAST_NAME	 ORIG_CAST_
-------------------------------------------------------------------------------- --------------- --------------- ----------
ORA-02290: check constraint (MIKE.CHK_ORIG_ONLY) violated			 DONAL		 MACINTYRE	 N
ORA-02290: check constraint (MIKE.CHK_ORIG_ONLY) violated			 JOE		 MCELDERRY	 N
ORA-01400: cannot insert NULL into ("MIKE"."ORIGINAL_CAST_MEMBERS"."LAST_NAME")  SINITTA			 Y

We can see from this that two of the records failed the new check constraint, and one because the entry has no value specified for LAST_NAME.

Other Error Table Columns

So far, we’ve concentrated on the ORA_ERR_MESG$ column of the error table.
It’s probably worth having a look at the other error specific columns in the table and how LOG ERRORS populates them.
Two of these columns are fairly straightforward :

  • ORA_ERR_NUMBER$ is the Oracle Error Number (which is included in the ORA_ERR_MESG$ as we’ve seen)
  • ORA_ERR_OPTYP$ is the DML operation that caused the error i.e. I(nsert), U(pdate) or D(elete)

The ORA_ERR_TAG$ allows you to enter an identifier for the particular instance of the DML statement you’re running.
Up until now, I’ve had to truncate the error table between runs to avoid getting confused.
However, if I tweak the statement to specify a tag as follows :

INSERT INTO original_cast_members( first_name, last_name, orig_cast_flag)
    SELECT first_name, last_name, NVL(orig_cast_flag, 'N')
    FROM contestants
    LOG ERRORS('OFF TO CASUALTY') REJECT LIMIT 2
/

After I’ve run into the problems, I can query the error table just for the records that failed in this particular transaction :

SELECT COUNT(*)
FROM err$_original_cast_members
WHERE ora_err_tag$ = 'OFF TO CASUALTY'
/

The final error column to look at is ORA_ERR_ROWID$. It’s always set to NULL for an insert statement, which is understandable when you think about it.
It’s purpose is to hold the rowid of the row that you’ve just failed to update or delete. So…

Update Errors

First, let’s try inserting a valid record into ORIGINAL_CAST_MEMBERS so that we can play around a bit :

INSERT INTO original_cast_members(first_name, last_name, orig_cast_flag)
VALUES('RITCHIE', 'NEVILLE', 'Y')
/
COMMIT;

Let’s try a wacky update statement and see what LOG_ERRORS makes of it…

UPDATE original_cast_members
SET orig_cast_flag = 'Original Cast or Plaster Cast ?'
WHERE first_name = 'RITCHIE'
AND last_name = 'NEVILLE'
LOG ERRORS ('BAD TASTE UPDATE') REJECT LIMIT UNLIMITED
/

Predictably, the update does not affect any rows. Not surprising considering I was trying to set a VARCHAR2(1) column to a value considerably longer than 1. However, when we look in the error table, we can see that we now have a rowid :

SQL> SELECT ora_err_rowid$
  2  FROM err$_original_cast_members
  3  WHERE ora_err_tag$ = 'BAD TASTE UPDATE'
  4  /

ORA_ERR_ROWID$
--------------------------------------------------------------------------------
AAAGONAAEAAABQbAAA

SQL> 

So, not only do we have the details of the values used in the failed update statement, we can also use the rowid to find the target row of the update :

SQL> SELECT first_name, last_name, orig_cast_flag
  2  FROM original_cast_members
  3  WHERE ROWID = 'AAAGONAAEAAABQbAAA';

FIRST_NAME	LAST_NAME	ORIG_CAST_FLAG
--------------- --------------- ---------------
RITCHIE 	NEVILLE 	Y

SQL> 

So, functionally at least, LOG ERRORS has a fair bit going for it.
But how does the PL/SQL equivalant of SAVE EXCEPTIONS compare ?

SAVE EXCEPTIONS

Having cleared down the target and error tables once more, we can now have a look at the SAVE EXCEPTIONS equivalent of LOG ERRORS. Just how closely can PL/SQL match the functionality on offer with the LOG ERRORS mechanism ?

The Error Message

Firstly, with no limit on the number of records that error on insert…

DECLARE
    TYPE typ_orig_cast IS TABLE OF original_cast_members%rowtype;
    tbl_orig_cast typ_orig_cast;
    
    e_dml_err EXCEPTION;
    
    PRAGMA EXCEPTION_INIT(e_dml_err, -24381);
    
    CURSOR c_contestants IS
        SELECT first_name, last_name, orig_cast_flag
        FROM contestants
        WHERE NVL(orig_cast_flag, 'N') = 'Y';

    --
    -- Array for error handling
    --
    TYPE typ_err IS TABLE OF err$_original_cast_members%ROWTYPE
        INDEX BY PLS_INTEGER;
    tbl_err typ_err;
    tbl_err_empty typ_err;
    l_idx NUMBER;
BEGIN
    OPEN c_contestants;
    LOOP
        FETCH c_contestants BULK COLLECT INTO tbl_orig_cast LIMIT 100;
        EXIT WHEN tbl_orig_cast.COUNT = 0;
        BEGIN
            FORALL i IN 1..tbl_orig_cast.COUNT SAVE EXCEPTIONS
                INSERT INTO original_cast_members
                VALUES tbl_orig_cast(i);
        EXCEPTION
            WHEN e_dml_err THEN
                FOR j IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
                    --
                    -- Loop through and populate the error array
                    --
                    l_idx := SQL%BULK_EXCEPTIONS(j).error_index;
                    tbl_err(j).ora_err_number$ := SQL%BULK_EXCEPTIONS(j).error_code;
                    tbl_err(j).ora_err_mesg$ := SQLERRM( SQL%BULK_EXCEPTIONS(j).error_code * -1);
                    tbl_err(j).ora_err_rowid$ := NULL; -- need to specify the NULL value in this example
                    tbl_err(j).ora_err_tag$ := 'HOW DO YOU STOP AGAIN ?';
                    tbl_err(j).ora_err_optyp$ := 'I';
                    tbl_err(j).first_name := tbl_orig_cast(l_idx).first_name;
                    tbl_err(j).last_name := tbl_orig_cast(l_idx).last_name;
                    tbl_err(j).orig_cast_flag := tbl_orig_cast(l_idx).orig_cast_flag;
                END LOOP;
                --
                -- ...and populate the error table in one fell swoop ( per iteration)
                --
                FORALL k IN 1..tbl_err.COUNT 
                    INSERT INTO err$_original_cast_members
                    VALUES tbl_err(k);
                --
                -- Cleardown the error array
                --
                tbl_err := tbl_err_empty;
        END;
    END LOOP;
    CLOSE c_contestants;
END;
/   

When we run this, all seems as expected…

SQL> @insert_se1.sql

PL/SQL procedure successfully completed.

SQL> select count(*) from original_cast_members;

  COUNT(*)
----------
	11

SQL> select count(*) from err$_original_cast_members;

  COUNT(*)
----------
	 1

SQL> 

However, if we take a closer look, we can begin to see some differences. First of all, let’s take a look at the error message that’s been recorded :

SQL> select ora_err_mesg$
  2  from err$_original_cast_members
  3* where ora_err_tag$ = 'HOW DO YOU STOP AGAIN ?'
SQL> /

ORA_ERR_MESG$
--------------------------------------------------------------------------------
ORA-01400: cannot insert NULL into ()
SQL> 

Hmmm, that’s a bit less specific than for LOG ERRORS.
Whilst we do have data available to populate most of the columns in the error table, we don’t have the specific error for each record.
the SQL%BULK_EXCEPTIONS collection only has two elements :

  • error index – holds the index number of the record in the collection where the failure occurred
  • error code – the Oracle error code of the error that was raised

In order to get an error message into the table, we’re having to use the SQLERRM function to lookup the text for the error number recorded.
In this example, not having the precise details of the column name where we hit this error is a minor inconvenience.
It would be less so if you were looking at such a failure for a table with a large number of not null columns.

Autonomous Error Record Logging

At this point you may well be thinking that I’ve forgotten to complete the transaction. Well, let’s do that now and see if the magic hidden autonomous transaction is still in play….

SQL> rollback;

Rollback complete.

SQL> select count(*) from original_cast_members;

  COUNT(*)
----------
	 0

SQL> select count(*) from err$_original_cast_members;

  COUNT(*)
----------
	 0

SQL> 

…er, no.

Incidentally, whether you regard this as a plus or a minus will rather depend on how you feel about having SAVE EXCEPTIONS do this in the first place.
Where you have error records identified by a unique tag so that they’re easy to lookup, it’s probably useful to have the error record, irrespective of what happens to the parent transaction. Otherwise, you could end up with quite a lot of error records which are of limited value.

Replicating this behaviour in PL/SQL requires a bit of a tweak to our code. By moving the error handling into a procedure, we can specify the autonomous transaction :

DECLARE
    TYPE typ_orig_cast IS TABLE OF original_cast_members%rowtype;
    tbl_orig_cast typ_orig_cast;
    
    e_dml_err EXCEPTION;
    
    PRAGMA EXCEPTION_INIT(e_dml_err, -24381);
     
    CURSOR c_contestants IS
        SELECT first_name, last_name, orig_cast_flag
        FROM contestants
        WHERE NVL(orig_cast_flag, 'N') = 'Y';
    --
    -- Encapsulate the error logging into an automomous transaction
    -- so that errors get saved even if the parent transaction is rolled back.
    --
    PROCEDURE process_errors_pr IS
        TYPE typ_err IS TABLE OF err$_original_cast_members%ROWTYPE
            INDEX BY PLS_INTEGER;
        tbl_err typ_err;
        tbl_err_empty typ_err;
        l_idx NUMBER;
        PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
        FOR j IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
            l_idx := SQL%BULK_EXCEPTIONS(j).error_index;
            tbl_err(j).ora_err_number$ := SQL%BULK_EXCEPTIONS(j).error_code;
            tbl_err(j).ora_err_mesg$ := SQLERRM( SQL%BULK_EXCEPTIONS(j).error_code * -1);
            tbl_err(j).ora_err_rowid$ := NULL; 
            tbl_err(j).ora_err_tag$ := 'HOW DO YOU STOP AGAIN ?';
            tbl_err(j).ora_err_optyp$ := 'I';
            tbl_err(j).first_name := tbl_orig_cast(l_idx).first_name;
            tbl_err(j).last_name := tbl_orig_cast(l_idx).last_name;
            tbl_err(j).orig_cast_flag := tbl_orig_cast(l_idx).orig_cast_flag;
        END LOOP;
        FORALL k IN 1..tbl_err.COUNT 
            INSERT INTO err$_original_cast_members
            VALUES tbl_err(k);
        --
        -- Save the exceptions
        --
        COMMIT;
        tbl_err := tbl_err_empty;
    END process_errors_pr;

BEGIN
    OPEN c_contestants;
    LOOP
        FETCH c_contestants BULK COLLECT INTO tbl_orig_cast LIMIT 100;
        EXIT WHEN tbl_orig_cast.COUNT = 0;
        BEGIN
            FORALL i IN 1..tbl_orig_cast.COUNT SAVE EXCEPTIONS
                INSERT INTO original_cast_members
                VALUES tbl_orig_cast(i);
        EXCEPTION
            WHEN e_dml_err THEN
            process_errors_pr;
        END;
    END LOOP;
    CLOSE c_contestants;
END;
/   

Now when we run this bit of code…

SQL> @insert_se_at.sql

PL/SQL procedure successfully completed.

SQL> rollback;

Rollback complete.

SQL> select count(*) from original_cast_members;

  COUNT(*)
----------
	 0

SQL> select count(*) from err$_original_cast_members;

  COUNT(*)
----------
	 1

SQL> 

Specified maximum number of errors

OK, so what about the maximum number of errors ? Well, once again, PL/SQL can do something similar should you desire :

DECLARE
    TYPE typ_orig_cast IS TABLE OF original_cast_members%rowtype;
    tbl_orig_cast typ_orig_cast;
    
    e_dml_err EXCEPTION;
    
    PRAGMA EXCEPTION_INIT(e_dml_err, -24381);
    --
    -- this cursor should give us 3 error records...
    -- 
    CURSOR c_contestants IS
        SELECT first_name, last_name, NVL(orig_cast_flag, 'N')
        FROM contestants;
    --
    -- Use a variable to set the maximum number of errors before
    -- we raise an error and fail the entire parent transaction
    --
    lc_max_errors CONSTANT PLS_INTEGER := 2;
    l_err_count PLS_INTEGER := 0;
    e_max_errors EXCEPTION;
    
    PROCEDURE process_errors_pr IS
        TYPE typ_err IS TABLE OF err$_original_cast_members%ROWTYPE
            INDEX BY PLS_INTEGER;
        tbl_err typ_err;
        tbl_err_empty typ_err;
        l_idx NUMBER;
        PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
        --
        -- Keep a running total of the number of exceptions...
        --
        l_err_count := l_err_count + SQL%BULK_EXCEPTIONS.COUNT;
        FOR j IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
            l_idx := SQL%BULK_EXCEPTIONS(j).error_index;
            tbl_err(j).ora_err_number$ := SQL%BULK_EXCEPTIONS(j).error_code;
            tbl_err(j).ora_err_mesg$ := SQLERRM( SQL%BULK_EXCEPTIONS(j).error_code * -1);
            tbl_err(j).ora_err_rowid$ := NULL; 
            tbl_err(j).ora_err_tag$ := 'HOW DO YOU STOP AGAIN ?';
            tbl_err(j).ora_err_optyp$ := 'I';
            tbl_err(j).first_name := tbl_orig_cast(l_idx).first_name;
            tbl_err(j).last_name := tbl_orig_cast(l_idx).last_name;
            tbl_err(j).orig_cast_flag := tbl_orig_cast(l_idx).orig_cast_flag;
        END LOOP;
        FORALL k IN 1..tbl_err.COUNT 
            INSERT INTO err$_original_cast_members
            VALUES tbl_err(k);
        COMMIT;
        tbl_err := tbl_err_empty;
    END process_errors_pr;

BEGIN
    OPEN c_contestants;
    LOOP
        --
        -- For the purposes of testing the maxiumum error functionality, set the
        -- limit to an unusually small value...
        --
        FETCH c_contestants BULK COLLECT INTO tbl_orig_cast LIMIT 5;
        EXIT WHEN tbl_orig_cast.COUNT = 0;
        BEGIN
            --
            -- Check the maximum number of errors outside of the bulk collect
            -- so we won't get caught up with the handling of the save exceptions
            -- error.
            --
            IF l_err_count > lc_max_errors THEN
                RAISE e_max_errors;
            END IF;
            FORALL i IN 1..tbl_orig_cast.COUNT SAVE EXCEPTIONS
                INSERT INTO original_cast_members
                VALUES tbl_orig_cast(i);
        EXCEPTION
            WHEN e_dml_err THEN
                process_errors_pr;
            WHEN e_max_errors THEN
                ROLLBACK;
                --
                -- re-raise to the outer block so it dies horribly
                --
                RAISE_APPLICATION_ERROR(-20000, 'Maximum number of errors exceeded');
        END;
    END LOOP;
    CLOSE c_contestants;
END;
/   

Run this and we get…

SQL> @insert_se_max_err.sql
DECLARE
*
ERROR at line 1:
ORA-20000: Maximum number of errors exceeded
ORA-06512: at line 85


SQL> select count(*) from original_cast_members;

  COUNT(*)
----------
	 0

SQL> select count(*) from err$_original_cast_members;

  COUNT(*)
----------
	 3

SQL> 

Of course, there is a chance that you may record additional error records. Remember, the PL/SQL is processing these records in batches wheras LOG ERRORS does it row-by-row. Therefore, you may get more than the maximum number of errors + 1.

There is one more tweak we need to apply to the PL/SQL to bring it more into line with LOG ERRORS, as far as INSERTS are concerned at least.

Handling values that are too large

As you’ve no doubt noticed, the collection we’re using is based on the target table – i.e. ORIGINAL_CAST_MEMBERS. This means that, when you get issues trying to insert a value that’s too large for it’s target column, things will fall over in a heap. To demonstrate…

CREATE TABLE too_big
(
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    orig_cast_flag VARCHAR2(50)
)
/

INSERT INTO too_big
    SELECT first_name, last_name, 'Way too big to fit !'
    FROM contestants
/

COMMIT;

Now, using this to populate the table …

DECLARE
    TYPE typ_orig_cast IS TABLE OF original_cast_members%rowtype;
    tbl_orig_cast typ_orig_cast;
    
    e_dml_err EXCEPTION;
    
    PRAGMA EXCEPTION_INIT(e_dml_err, -24381);
    
    --
    -- this cursor should give us 3 error records...
    -- 
    CURSOR c_contestants IS
        SELECT first_name, last_name, orig_cast_flag
        FROM too_big;
    lc_max_errors CONSTANT PLS_INTEGER := 2;
    l_err_count PLS_INTEGER := 0;
    e_max_errors EXCEPTION;
    
    PROCEDURE process_errors_pr IS
        TYPE typ_err IS TABLE OF err$_original_cast_members%ROWTYPE
            INDEX BY PLS_INTEGER;
        tbl_err typ_err;
        tbl_err_empty typ_err;
        l_idx NUMBER;
        PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
        l_err_count := l_err_count + SQL%BULK_EXCEPTIONS.COUNT;
        FOR j IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
            l_idx := SQL%BULK_EXCEPTIONS(j).error_index;
            tbl_err(j).ora_err_number$ := SQL%BULK_EXCEPTIONS(j).error_code;
            tbl_err(j).ora_err_mesg$ := SQLERRM( SQL%BULK_EXCEPTIONS(j).error_code * -1);
            tbl_err(j).ora_err_rowid$ := NULL; 
            tbl_err(j).ora_err_tag$ := 'HOW DO YOU STOP AGAIN ?';
            tbl_err(j).ora_err_optyp$ := 'I';
            tbl_err(j).first_name := tbl_orig_cast(l_idx).first_name;
            tbl_err(j).last_name := tbl_orig_cast(l_idx).last_name;
            tbl_err(j).orig_cast_flag := tbl_orig_cast(l_idx).orig_cast_flag;
        END LOOP;
        FORALL k IN 1..tbl_err.COUNT 
            INSERT INTO err$_original_cast_members
            VALUES tbl_err(k);
        COMMIT;
        tbl_err := tbl_err_empty;
    END process_errors_pr;

BEGIN
    OPEN c_contestants;
    LOOP
        FETCH c_contestants BULK COLLECT INTO tbl_orig_cast LIMIT 5;
        EXIT WHEN tbl_orig_cast.COUNT = 0;
        BEGIN
            IF l_err_count > lc_max_errors THEN
                RAISE e_max_errors;
            END IF;
            FORALL i IN 1..tbl_orig_cast.COUNT SAVE EXCEPTIONS
                INSERT INTO original_cast_members
                VALUES tbl_orig_cast(i);
        EXCEPTION
            WHEN e_dml_err THEN
                process_errors_pr;
            WHEN e_max_errors THEN
                ROLLBACK;
                RAISE_APPLICATION_ERROR(-20000, 'Maximum number of errors exceeded');
        END;
    END LOOP;
    CLOSE c_contestants;
END;
/   

…we won’t get very far…

SQL> @too_big_se.sql
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind
ORA-06512: at line 63


SQL> 

To fix this, we really need to make sure that, like the error table, the collection can handle the maximum size of the appropriate data type for each column ( as we’re on 11g this will be 4000 for varchars).

DECLARE
    --
    -- Explicitly create the record so that we can handle errors where the
    -- value to be inserted is too large...
    --
    TYPE rec_orig_cast IS RECORD
    (
        first_name VARCHAR2(4000),
        last_name VARCHAR2(4000),
        orig_cast_flag VARCHAR2(4000)
    );
    TYPE typ_orig_cast IS TABLE OF rec_orig_cast 
        INDEX BY PLS_INTEGER;
    tbl_orig_cast typ_orig_cast;
    
    e_dml_err EXCEPTION;
    
    PRAGMA EXCEPTION_INIT(e_dml_err, -24381);
    
    CURSOR c_contestants IS
        SELECT first_name, last_name, orig_cast_flag
        FROM too_big;

    lc_max_errors CONSTANT PLS_INTEGER := 2;
    l_err_count PLS_INTEGER := 0;
    e_max_errors EXCEPTION;
    
    PROCEDURE process_errors_pr IS
        TYPE typ_err IS TABLE OF err$_original_cast_members%ROWTYPE
            INDEX BY PLS_INTEGER;
        tbl_err typ_err;
        tbl_err_empty typ_err;
        l_idx NUMBER;
        PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
        l_err_count := l_err_count + SQL%BULK_EXCEPTIONS.COUNT;
        FOR j IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
            l_idx := SQL%BULK_EXCEPTIONS(j).error_index;
            tbl_err(j).ora_err_number$ := SQL%BULK_EXCEPTIONS(j).error_code;
            tbl_err(j).ora_err_mesg$ := SQLERRM( SQL%BULK_EXCEPTIONS(j).error_code * -1);
            tbl_err(j).ora_err_rowid$ := NULL; 
            tbl_err(j).ora_err_tag$ := 'HOW DO YOU STOP AGAIN ?';
            tbl_err(j).ora_err_optyp$ := 'I';
            tbl_err(j).first_name := tbl_orig_cast(l_idx).first_name;
            tbl_err(j).last_name := tbl_orig_cast(l_idx).last_name;
            tbl_err(j).orig_cast_flag := tbl_orig_cast(l_idx).orig_cast_flag;
        END LOOP;
        FORALL k IN 1..tbl_err.COUNT 
            INSERT INTO err$_original_cast_members
            VALUES tbl_err(k);
        COMMIT;
        tbl_err := tbl_err_empty;
    END process_errors_pr;

BEGIN
    OPEN c_contestants;
    LOOP
        FETCH c_contestants BULK COLLECT INTO tbl_orig_cast LIMIT 5;
        EXIT WHEN tbl_orig_cast.COUNT = 0;
        BEGIN
            IF l_err_count > lc_max_errors THEN
                RAISE e_max_errors;
            END IF;
            FORALL i IN 1..tbl_orig_cast.COUNT SAVE EXCEPTIONS
                INSERT INTO original_cast_members
                VALUES tbl_orig_cast(i);
        EXCEPTION
            WHEN e_dml_err THEN
                process_errors_pr;
            WHEN e_max_errors THEN
                ROLLBACK;
                RAISE_APPLICATION_ERROR(-20000, 'Maximum number of errors exceeded');
        END;
    END LOOP;
    CLOSE c_contestants;
END;
/   

Run this and we get the more desirable…

SQL> @too_big2_se.sql
DECLARE
*
ERROR at line 1:
ORA-20000: Maximum number of errors exceeded
ORA-06512: at line 96


SQL> select count(*) from original_cast_members;

  COUNT(*)
----------
	 0

SQL> select count(*) from err$_original_cast_members;

  COUNT(*)
----------
	 5

SQL> select ora_err_mesg$ from err$_original_cast_members;

ORA_ERR_MESG$
--------------------------------------------------------------------------------
ORA-12899: value too large for column  (actual: , maximum: )
ORA-12899: value too large for column  (actual: , maximum: )
ORA-12899: value too large for column  (actual: , maximum: )
ORA-01400: cannot insert NULL into ()
ORA-12899: value too large for column  (actual: , maximum: )

SQL> 

Notice the effect that I mentioned a minute ago ? Although the maximum error count is 2, we’ve actually recorded 5 errors.

Save Exceptions and ROWID

Before running through the following examples, we need to populate the ORIGINAL_CAST_MEMBERS table :

INSERT INTO original_cast_members 
    SELECT * 
    FROM contestants 
    WHERE orig_cast_flag IS NOT NULL 
    AND last_name IS NOT NULL;
/

This should be fairly straightforward. After all, you can use RETURNING INTO with a BULK COLLECT in a FORALL update like this, for example :

set serveroutput on size unlimited
DECLARE
    TYPE typ_orig_cast IS TABLE OF original_cast_members%ROWTYPE
        INDEX BY PLS_INTEGER;
    tbl_orig_cast typ_orig_cast;
    -- array to hold the rowids
    tbl_rowids DBMS_SQL.UROWID_TABLE;
BEGIN
    SELECT first_name, last_name, orig_cast_flag
    BULK COLLECT INTO tbl_orig_cast
    FROM original_cast_members;
    
    FORALL i IN 1..tbl_orig_cast.COUNT SAVE EXCEPTIONS
        UPDATE original_cast_members
        SET orig_cast_flag = 'X'
        WHERE first_name = tbl_orig_cast(i).first_name
        AND last_name = tbl_orig_cast(i).last_name
        -- populate the rowids array with the rowid of the target row
        RETURNING ROWID BULK COLLECT INTO tbl_rowids;
    DBMS_OUTPUT.PUT_LINE('Rows updated : '||tbl_rowids.COUNT);    
    FOR j IN 1..tbl_rowids.COUNT LOOP    
        DBMS_OUTPUT.PUT_LINE('Rowid : '||tbl_rowids(j)||' updated.');
    END LOOP;
END;
/

Run this and we get…

SQL> @rowid_test.sql
Rows updated : 11
Rowid : AAAGT5AAEAAAGQNAAA updated.
Rowid : AAAGT5AAEAAAGQNAAB updated.
Rowid : AAAGT5AAEAAAGQNAAC updated.
Rowid : AAAGT5AAEAAAGQNAAD updated.
Rowid : AAAGT5AAEAAAGQNAAE updated.
Rowid : AAAGT5AAEAAAGQNAAF updated.
Rowid : AAAGT5AAEAAAGQNAAG updated.
Rowid : AAAGT5AAEAAAGQNAAH updated.
Rowid : AAAGT5AAEAAAGQNAAI updated.
Rowid : AAAGT5AAEAAAGQNAAJ updated.
Rowid : AAAGT5AAEAAAGQNAAK updated.

PL/SQL procedure successfully completed.

SQL> 

Well, that seems perfectly OK. However, when we try to use this on a statement where errors occur, we get a bit of a nasty surprise :

set serveroutput on
DECLARE
    TYPE rec_orig_cast IS RECORD
    (
        first_name VARCHAR2(4000),
        last_name VARCHAR2(4000),
        orig_cast_flag VARCHAR2(4000)
    );
    TYPE typ_orig_cast IS TABLE OF rec_orig_cast 
        INDEX BY PLS_INTEGER;
    tbl_orig_cast typ_orig_cast;
    --
    -- Create an array to hold the rowids of the rows updated in the target table
    --
    tbl_rowid DBMS_SQL.UROWID_TABLE;
    tbl_rowid_empty DBMS_SQL.UROWID_TABLE;
    
    e_dml_err EXCEPTION;
        
    PRAGMA EXCEPTION_INIT(e_dml_err, -24381);
    
    CURSOR c_contestants IS
        SELECT first_name, last_name, orig_cast_flag
        FROM too_big;

    lc_max_errors CONSTANT PLS_INTEGER := 2;
    l_err_count PLS_INTEGER := 0;
    e_max_errors EXCEPTION;
    
    PROCEDURE process_errors_pr IS
        TYPE typ_err IS TABLE OF err$_original_cast_members%ROWTYPE
            INDEX BY PLS_INTEGER;
        tbl_err typ_err;
        tbl_err_empty typ_err;
        l_idx NUMBER;
        PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
        l_err_count := l_err_count + SQL%BULK_EXCEPTIONS.COUNT;
        FOR j IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
            l_idx := SQL%BULK_EXCEPTIONS(j).error_index;
            -- Just checking what's going on at this point...
            DBMS_OUTPUT.PUT_LINE('Error No is : '||l_idx);
            DBMS_OUTPUT.PUT_LINE('First Name is : '||tbl_orig_cast(l_idx).first_name);
            DBMS_OUTPUT.PUT_LINE('Last Name is : '||tbl_orig_cast(l_idx).last_name);
            DBMS_OUTPUT.PUT_LINE('Count of rowids = '||tbl_rowid.COUNT);
            DBMS_OUTPUT.PUT_LINE('Rowid = '||tbl_rowid(l_idx));
            tbl_err(j).ora_err_number$ := SQL%BULK_EXCEPTIONS(j).error_code;
            tbl_err(j).ora_err_mesg$ := SQLERRM( SQL%BULK_EXCEPTIONS(j).error_code * -1);
            -- get the rowid from our collection...
            tbl_err(j).ora_err_rowid$ := tbl_rowid(l_idx); 
            tbl_err(j).ora_err_tag$ := 'WHICH ROWID';
            tbl_err(j).ora_err_optyp$ := 'I';
            tbl_err(j).first_name := tbl_orig_cast(l_idx).first_name;
            tbl_err(j).last_name := tbl_orig_cast(l_idx).last_name;
            tbl_err(j).orig_cast_flag := tbl_orig_cast(l_idx).orig_cast_flag;
        END LOOP;
        FORALL k IN 1..tbl_err.COUNT 
            INSERT INTO err$_original_cast_members
            VALUES tbl_err(k);
        COMMIT;
        tbl_err := tbl_err_empty;
        tbl_rowid := tbl_rowid_empty;
    END process_errors_pr;

BEGIN
    OPEN c_contestants;
    LOOP
        FETCH c_contestants BULK COLLECT INTO tbl_orig_cast LIMIT 5;
        EXIT WHEN tbl_orig_cast.COUNT = 0;
        BEGIN
            IF l_err_count > lc_max_errors THEN
                RAISE e_max_errors;
            END IF;
            FORALL i IN 1..tbl_orig_cast.COUNT SAVE EXCEPTIONS
                UPDATE original_cast_members
                SET orig_cast_flag = tbl_orig_cast(i).orig_cast_flag
                WHERE first_name = tbl_orig_cast(i).first_name
                AND last_name = NVL(tbl_orig_cast(i).last_name, 'X')
                RETURNING ROWID BULK COLLECT INTO tbl_rowid; 
        EXCEPTION
            WHEN e_dml_err THEN
                process_errors_pr;
            WHEN e_max_errors THEN
                ROLLBACK;
                RAISE_APPLICATION_ERROR(-20000, 'Maximum number of errors exceeded');
        END;
    END LOOP;
    CLOSE c_contestants;
END;
/   

Run this and we get…

SQL> @se_rowid.sql
Error No is : 1
First Name is : STEVE
Last Name is : REDGRAVE
Count of rowids = 0
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 45
ORA-06512: at line 81
ORA-24381: error(s) in array DML


SQL> 

The RETURNING INTO clause only returns a value for a successful update.
Of course, if your cursor is referencing your target table, you can always simply retrieve the rowid as part of the query using the ROWID pseudo-column in the SELECT clause. However, in our case, where we’re the cursor we’re BULK COLLECTing is from another table, this isn’t going to do us much good.

The Summary

As we’ve seen, LOG ERRORS packs an awful lot of functionality into a single line of code.

  • Errors records are saved irrespective of the success of the parent transaction
  • It allows you to specify a tag for a specific transaction to make searching for errors easier
  • As it processes errors row-by-row, it will save the error message generated for a specific row operation
  • It allows you to specify a maximum number of erros to allow before failing the transaction
  • For UPDATE and DELETE statements, it will record the ROWID of the target row

We can replicate most of this functionality in PL/SQL using SAVE EXCEPTIONS. However, the error messages are more generic (and therefore less helpful when you’re trying to debug). Additionally, recording the ROWID of a target row for an errored Update or Delete is problematic using this mechanism.
One other point to note, the PL/SQL approach does require quite a bit more code to implement.
So, which one should you use ?
As usual, the correct answer is “it depends.”
If performance is the primary factor to consider for a given problem, then SAVE EXCEPTIONS has a pretty strong case.
As we’ve seen previously, it’s likely to be significantly faster than LOG ERRORS.
If, on the other hand, a LOG ERRORS solution is fast enough then the functional advantages it offers make it worthy of consideration.

If you’ve got this far, you’re probably of the opinion that you’ve read quite enough fence-sitting and really just want to know what happened in The Jump.
Well, of the four contestants who made it through to the Final…
Steve Redgrave withdrew from the final due to injuries sustained on the show.
Marcus Brigstocke fell in the first event of the final causing him to withdraw through injury.
Of the two left standing – both of whom were not in the original line-up, Joe Mcelderry beat Donal Macintyre in the ski-jumping to claim the coveted Cow Bell trophy ( and possibly earn a no-claims bonus on his holiday insurance).

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s