Automate the errored records using LOG ERRORS feature

View All Scripts Login to Run Script

Script Name Automate the errored records using LOG ERRORS feature
Description Oracle provides a powerful mechanism to uncover DML errors from version 10g Release 2 onwards. The LOG ERRORS clause is similar to SAVE EXCEPTIONS in PLSQL bulk operations. This feature can be used to identify the records the problematic records which cause an SQL statements to raise an exception.
Area PL/SQL General
Contributor Binuraj Somanathan Nair (http://oracleappsdiary.blogspot.co.uk)
Created Tuesday July 12, 2016

Statement 1
The SQL results will be ORA-01476 since there is atleast one record where COMM is 0. But the error message does not detail which record is in error
Generate Exception
```
SELECT emp.*
      ,sal / comm sal_to_comm_ratio
FROM   scott.emp emp
```
```
ORA-01476: divisor is equal to zero
```
More Details: https://docs.oracle.com/error-help/db/ora-01476

Statement 2

Create a table with the column structure of the SQL

Create a table

create table emp_test as
SELECT emp.*
      ,sal / comm sal_to_comm_ratio
FROM   scott.emp emp
where 1=2

Table created.

Statement 3

Create Error Log Table using API provided by Oracle

Create Error Log table

begin
    dbms_errlog.create_error_log(dml_table_name => 'EMP_TEST');
end;

Statement processed.

Statement 4

Use Insert table statements with LOG ERRORS clause to insert the data in to the error table

Insert into the table with LOG ERRORS clause

 INSERT INTO emp_test
  SELECT emp.*
        ,sal / comm sal_to_comm_ratio
  FROM   scott.emp emp LOG errors
  INTO   err$_emp_test reject LIMIT unlimited

13 row(s) inserted.

Statement 5

Query the error table to see the error messages

Select * from ERR$_EMP_TEST


ORA_ERR_NUMBER$ ORA_ERR_MESG$ ORA_ERR_ROWID$ ORA_ERR_OPTYP$ ORA_ERR_TAG$ EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO SAL_TO_COMM_RATIO
1476 ORA-01476: divisor is equal to zero
 - I  - 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 1500
1476 ORA-01476: divisor is equal to zero
 - I  - 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 1500

2 rows selected.

ORA_ERR_NUMBER$	ORA_ERR_MESG$	ORA_ERR_ROWID$	ORA_ERR_OPTYP$	ORA_ERR_TAG$	EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO	SAL_TO_COMM_RATIO
1476	ORA-01476: divisor is equal to zero	-	I	-	7844	TURNER	SALESMAN	7698	08-SEP-81	1500	0	30	1500
1476	ORA-01476: divisor is equal to zero	-	I	-	7844	TURNER	SALESMAN	7698	08-SEP-81	1500	0	30	1500

Statement 6

Automate the above steps using PLSQL script. The script will take the SQL statement in to a variable and performs steps 2 to 5 above. The error table name will be displayed in output.

PLSQL script to Automate

   DECLARE

  l_sql_statement    VARCHAR2(30000) := ' SELECT emp.*
      ,sal / comm sal_to_comm_ratio
FROM   scott.emp emp ';
  l_create_table_sql VARCHAR2(32767);
  l_insert_table_sql VARCHAR2(32767);
  l_table_name       VARCHAR2(30) := 'XXERR_TEST';
  l_error_table_name VARCHAR2(30) := 'ERR$_' || l_table_name;

BEGIN
  l_create_table_sql := 'CREATE TABLE ' || l_table_name ||  
                        ' AS SELECT * FROM (' || l_sql_statement ||
                        ') WHERE 1=2';
  

   
  EXECUTE IMMEDIATE l_create_table_sql;
  
  dbms_errlog.create_error_log(dml_table_name => l_table_name);
  
 l_insert_table_sql := 'INSERT INTO ' || l_table_name || ' SELECT * FROM (' || 
                        l_sql_statement || ') LOG ERRORS INTO err$_' 
                      ||l_table_name || ' (''INSERT'') REJECT LIMIT UNLIMITED';
   
  EXECUTE IMMEDIATE l_insert_table_sql;
  
  dbms_output.put_line('Error Messages are stored in table ' ||                           l_error_table_name);

END;

Table name is XXERR12072016082820
CREATE TABLE XXERR12072016082820 AS SELECT * FROM ( SELECT emp.*
      ,sal / comm sal_to_comm_ratio
FROM   scott.emp emp ) WHERE 1=2
Error Messages are stored in table ERR$_XXERR12072016082820

Statement 7
Query the error table to see the error messages
```
select * from ERR$_XXERR_TEST
```

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database