Not all errors in Oracle are
fatal, and that statement applies to data manipulation language
statements as well. Even though an error in and of itself may be
fatal, when handled properly it becomes benign. The question is
this: how do you make an otherwise fatal error benign or harmless?
There are at least three ways, two of which you are probably quite
familiar with, and a lesser known, but very versatile third
method.
The Tried and True
Ways
Two methods of capturing DML errors
and being able to deal with them in a suitable manner are SQL*Loader
and PL/SQL exception handling. If your situation involves uploading
data or writing blocks of exception handling code (and only checking
for a few errors), then by and large, these manners are suitable.
SQL*Loader can provide a wealth of information about a bad record
via the log, bad, and discard files, but its usefulness across all
DML operations is limited.
PL/SQL exception handling can also
capture errors and provide information for feedback, but coding all
of the potential places where an integrity error can occur, in
addition to keeping track of an exception block's scope, can grow to
be quite tedious. Besides, are you sure you accounted for all of the
possible errors? The PL/SQL approach also means having to wrap
blocks in an inappropriate manner and repeating the error handling
from block to block. The PL/SQL approach also incurs the cost of
context switching because of having to switch between SQL and
PL/SQL. There has to be a better way, and in this case, there does
exist a better way, namely, that of what is referred to as DML error
logging.
DML Error Logging Basics
Managing Tables, Chapter 15 of the
Administrator's Guide, explains what takes place during DML error
logging.
To use DML error logging, you add
a statement clause that specifies the name of an error logging table
into which the database records errors encountered during DML
operations. When you add this error logging clause to the INSERT
statement, certain types of errors no longer terminate and roll back
the statement. Instead, each error is logged and the statement
continues. You then take corrective action on the erroneous rows at
a later time.
The scenario just shown mentions
INSERT, but DML error logging applies to UPDATE and DELETE as well.
The basic steps (summarized from the documentation) are to:
1. Optionally create an error
logging table. You can create the table manually or use the
DBMS_ERRLOG package to automatically create it for you.
2. Execute a DML statement and
include an error logging clause. This clause:
-
Optionally references the error
logging table that you created. If you do not provide an error
logging table name, the database logs to an error logging table
with a default name. The default error logging table name is
ERR$_ followed by the first 25 characters of the name of the
table that is being inserted into.
-
Optionally includes a tag (a
numeric or string literal in parentheses) that is added to the
error log to help identify the statement that caused the errors.
If the tag is omitted, a NULL value is used.
-
Optionally includes a REJECT
LIMIT subclause. This subclause indicates the maximum number of
errors that can be encountered before the DML statement
terminates and rolls back. You can also specify UNLIMITED. The
default reject limit is zero, which means that upon encountering
the first error, the error is logged and the statement rolls
back. For parallel DML operations, the reject limit is applied
to each parallel server.
3. Note: If the statement exceeds
the reject limit and rolls back, the error logging table retains
the log entries recorded so far.
4. Query the error logging table
and take corrective action for the rows that generated errors.
If you do create an error logging
table, you must include all of the mandatory error logging columns
(although they can be created in any order). You may optionally
create additional columns, which reference the DML table. An
important point to keep in mind is that datatypes of the additional
columns must be able to capture or handle the datatype of the DML
column. For example, don't make a number column in the error table
when the DML table is trying to use VARCHAR2. Do, however, make the
error table column datatype VARCHAR2 when dealing with a number in
the DML table. Oracle will handle the casting for you (if it can).
VARCHAR2(4000) is pretty much the default datatype you should use
for typical DML column datatypes.
The mandatory columns in an (again,
optional) error table are:
Column
Name |
Datatype |
Description |
ORA_ERR_NUMBER$ |
NUMBER |
Oracle error number |
ORA_ERR_MESG$ |
VARCHAR2(2000) |
Oracle error message text |
ORA_ERR_ROWID$ |
ROWID |
Rowid of the row in error
(for update and delete) |
ORA_ERR_OPTYP$ |
VARCHAR2(2) |
Type of operation: I/U/D
(merge will include U and
I) |
ORA_ERR_TAG$ |
VARCHAR2(2000) |
Value of the tag supplied
by the user in the error logging clause |
You have two options with respect to
creating an error table. The first is to let Oracle do the work for
you, and that requires using the DBMS_ERRLOG package. This built-in
will not only create the mandatory columns just mentioned, but will
also map the target DML table's columns. The second and decidedly
more difficult way is to manually create the logging table via a
data definition language (DDL) CREATE TABLE statement. Under the
manual method, you are responsible for ensuring the mandatory parts
are in place and for mapping any additional columns.
The only real advantage to manually
creating an error logging table is that you can name it whatever you
want and add (or not) only some of the DML table's columns.
Otherwise, the table is named ERR$_.
This package, described in Chapter
38 of Oracle? Database PL/SQL Packages and Types Reference, 10g
Release 2 (10.2) employs a security model. For the most part, you
can create an error logging table for any table (or view) you own.
Some of the package's input parameters can be null, and the only
mandatory input parameter is the name of the DML (or target) table.
There is only one procedure in this package, and that is the
CREATE_ERROR_LOG procedure. To help prevent a datatype mismatch
between the DML and error logging table, you may want to consider
using the skip_unsupported input parameter (BOOLEAN, default is
false, meaning an unsupported column type will cause the procedure
to terminate).
Let's look at an example/use case
for DML error logging. To keep things simple, we will use the EMP
table in Scott's schema. The steps below show how easy it is to
create the error logging table. Note how all of the columns in EMP
have been mapped to VARCHAR2(4000)'s in ERR$_EMP.