What I like about SQL*LOADER is its ability to continue processing through load errors. If the record being loaded is rejected because of a unique constraint violation or discarded because it does not meet some user-defined criteria, SQL*LOADER places the record into a discard or reject file and keeps on running until it has reached a user-specified maximum number of rejects or discards.
The loader's log file will show how many records were loaded, rejected or discarded. I can look at the messages and review the discard or reject files, fix the problem and attempt to reload them again.
In 10G R2, this same type of processing has been applied to bulk DML operations. Users are able to specify whether they want to log errors or abort the entire DML statement, set the amount of detail information logged and the maximum error threshold. So instead of the entire DML statement blowing up and rolling back, Oracle will log the errors to the error table and continue processing until it exceeds the maximum number of errors. Just like SQL*Loader!
Oracle 10G R2's DBMS_ERRLOG package is used to create the error log output table and link it to the table being updated. The package's specification is provided below:
DBMS_ERRLOG.CREATE_ERROR_LOG (
dml_table_name IN VARCHAR2,
err_log_table_name IN VARCHAR2 := NULL,
err_log_table_owner IN VARCHAR2 := NULL,
err_log_table_space IN VARCHAR2 := NULL,
skip_unsupported IN BOOLEAN := FALSE);
Most of the columns are pretty self explanatory: table name being updated, error log table name, owner of error log table and the error log table's tablespace. If the SKIP_UNSIPPORTED is set to TRUE, column types that are not supported by error logging will be skipped over and not added to the error logging table. If it is set to FALSE, an unsupported column type will cause the procedure to fail.
Here's a quick example:
Let's create or table that will be updated:
SQL> r
1 CREATE TABLE foot.emp_table
2 (empno number(4), ename varchar2(10), job varchar2(8))
3* TABLESPACE users;Table created.
Add a primary key:
SQL> ALTER TABLE foot. emp_table ADD PRIMARY KEY(empno)
2 USING INDEX TABLESPACE users;Table altered.;
Load some rows:
SQL> INSERT INTO foot.emp_table VALUES
2 (7499, 'ALLEN', 'SALESMAN');1 row created.
SQL> INSERT INTO foot.emp_table VALUES
2 (7521, 'WARD', 'SALESMAN');1 row created.
SQL> INSERT INTO foot.emp_table VALUES
2 (7566, 'JONES', 'MANAGER');
Let's cause a unique constraint violation:
SQL> insert into foot.emp_table select * from foot.emp_table;
insert into foot.emp_table select * from foot.emp_table
*
ERROR at line 1:
ORA-00001: unique constraint (FOOT.SYS_C009069) violated
Create the 10G R2 error logging table:
exec dbms_errlog.CREATE_ERROR_LOG ('emp_table','dml_errors_emp_table')
Let's create a table that we can update and change one row to see if we can get one row to load and 2 to fail and be placed into the DML_ERRORS_EMP_TABLE:
SQL> create table foot.emp_load_table tablespace users as select * from foot.emp_table
Table created.
Update one row to change the EMPNO column's value to avoid the primary key violation:
SQL> update foot.emp_load_table set empno=123 where empno=7499;
1 row updated.
Rerun the statement specifying the new 10G R2 error logging syntax. Use our new load input table so that our one changed row will be loaded and two will be rejected and placed into the DML_ERRORS_EMP_TABLE:
SQL> insert into foot.emp_table select * from foot.emp_table
LOG ERRORS INTO dml_errors_emp_table('test_load_20050718')
REJECT LIMIT UNLIMITED;1 row created.
OK, we loaded one. What happened to our other two? Let's see what our DML_ERRORS_EMP_TABLE contains.
First, let's describe the DML_ERRORS_EMP_TABLE:
SQL> DESC foot.dml_errors_emp_table
ORA_ERR_NUMBER$ NUMBER
ORA_ERR_MESG$ VARCHAR2(2000)
ORA_ERR_ROWID$ ROWID
ORA_ERR_OPTYP$ VARCHAR2(2)
ORA_ERR_TAG$ VARCHAR2(2000)
EMPNO VARCHAR2(4000)
ENAME VARCHAR2(4000)
JOB VARCHAR2(4000)
Our error logging table contains an incrementing error counter, the error message, ROWID, error type, tag (contains our users specified name from above -'test_load_20050718') and the three columns of our table.
Let's select from the table. I have
truncated the ORA_ERR_MESG$ table
for readability sake:
SQL> SELECT ora_err_number$,
ora_error_mesg$, emp_no FROM
foot.dml_errors_emp_table;
ORA_ERR_NUMBER$ ORA_ERR_MESG$ EMP_NO
--------------- -------------------------------------------------- ------
1 ORA-00001: unique constraint (FOOT.SYS_C009069) violated…..7521
2 ORA-00001: unique constraint (FOOT.SYS_C009069) violated….7566
We loaded one row and rejected two rows due to primary key constraint violations.
