exception enhancements in 10g

Oracle has introduced a few enhancements to exception handling and error messages in 10g. This article summarises a few that I've come across so far.

backtrace for full error stack reporting

It has long been a bug-bear of Oracle developers that it was impossible to get to the true origin of an exception in a nested stack of PL/SQL calls. In 10g, Oracle provides part of the solution with a new function in the DBMS_UTILITY package called FORMAT_ERROR_BACKTRACE. Many developers will be familiar with the other FORMAT_%_STACK functions in this package, but this new one reports the exception points through the entire error stack. I've written about this in more detail here, but have included a small example below.

This example will simply create three procedures (they should of course be in a package but for the purposes of this high-level demo, it will be much easier to read the stack if we use standalone procedures). The procedure calls will be cascading and the "lowest" procedure will raise an exception. Using a call to DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, we'll see the points in each procedure that the exception is propagated.

First, let's create the three procedures.

SQL> CREATE PROCEDURE p3 AS
  2  BEGIN
  3     --filler
  4     --filler
  5     RAISE PROGRAM_ERROR;
  6  END p3;
  7  /

Procedure created.

SQL> CREATE PROCEDURE p2 AS
  2  BEGIN
  3     p3;
  4  END p2;
  5  /

Procedure created.

SQL> CREATE PROCEDURE p1 AS
  2  BEGIN
  3     --filler
  4     --filler
  5     --filler
  6     --filler
  7     p2;
  8  END p1;
  9  /

Procedure created.

Now we can call the top-level procedure P1 and report the propagation points of any exceptions.

SQL> BEGIN
  2     p1;
  3  EXCEPTION
  4     WHEN OTHERS THEN
  5        DBMS_OUTPUT.PUT_LINE(
  6           DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
  7           );
  8  END;
  9  /

ORA-06512: at "SCOTT.P3", line 5
ORA-06512: at "SCOTT.P2", line 3
ORA-06512: at "SCOTT.P1", line 7
ORA-06512: at line 2

PL/SQL procedure successfully completed.

An important point to note is that if your development standards require you to include a default exception block along the lines of "WHEN OTHERS THEN log_the_error(); RAISE;" then the FORMAT_ERROR_BACKTRACE function will report the propagation stack of all the RAISE statements.

ORA-12899: value too large for column

This enhancement is proving incredibly popular! When we try to insert too many bytes into a constrained column, Oracle now reports the column name, its constrained width and the length of the data we tried to insert. This is a very useful feature. In the "real world", tables are wide and INSERT(..SELECT) statements that fail due to this issue can often take time to debug. Not any more. We can see this in a simple example below.

SQL> CREATE TABLE t ( c VARCHAR2(10) );

Table created.

SQL> INSERT INTO t VALUES ( 'Nineteen characters' );

INSERT INTO t VALUES ( 'Nineteen characters' )
                       *
ERROR at line 1:
ORA-12899: value too large for column "SCOTT"."T"."C" (actual: 19, maximum: 10)

Did I already mention that this feature was useful?

ORA-38104: a meaningful merge message

When the MERGE statement was provided in 9i, it was an unfamiliar (and to some, quite cumbersome) syntax. Being a new feature, it also meant that we would frequently make mistakes when learning how to use it. One of these mistakes (quite common at the time) was trying to include a join column (i.e. a column used in the ON clause between source and target data) in the UPDATE section of the MERGE. In 9i, there was no specific message for this, so Oracle would raise ORA-00904: invalid identifier which was confusing to say the least and took a while to debug the first time I saw it. In 10g, Oracle provides a dedicated ORA-38104 error for this restriction as the following example demonstrates.

We'll start by creating a small target table for our MERGE.

SQL> CREATE TABLE tgt ( x INT, y INT, z VARCHAR2(1) );

Table created.

Now we'll try to MERGE a single row of derived data into our TGT table. Note that we've joined source to target using columns X and Y yet we've tried to UPDATE column Y when we get a match (thereby hitting the restriction).

SQL> MERGE
  2     INTO tgt
  3     USING (
  4            SELECT 1   AS x
  5            ,      2   AS y
  6            ,      'A' AS z
  7            FROM   dual
  8           ) src
  9     ON   ( tgt.x = src.x AND tgt.y = src.y )
 10  WHEN MATCHED THEN
 11     UPDATE
 12     SET tgt.y = src.y
 13     ,   tgt.z = src.z;

   ON   ( tgt.x = src.x AND tgt.y = src.y )
                            *
ERROR at line 9:
ORA-38104: Columns referenced in the ON Clause cannot be updated: "TGT"."Y"

This new error should make it slightly more obvious to new developers getting to grips with MERGE for the first time!

source code

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

Adrian Billington, June 2004

oracle-developer.net