oracle-developer.net

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.

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.

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