Identify the record causing an SQL statement to raise an Exception using LOG ERRORS feature

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.

SQL statements work in all or nothing principle. Even if a single record causes an ‘Invalid Number’ exception, the entire SQL fails. It could be a bit tricky to identify the record(s) which triggered the exception when the SQL is complex and the dataset is large.

As an example, if we run the below SQL against Oracle provided EMP table,

SELECT emp.*

      ,sal / comm sal_to_comm_ratio

FROM   scott.emp emp;

The result will be ORA-01476 since there is atleast one record where COMM is 0.

But the error message will not give a clue which record has caused this issue. Identifying the record is key to fixing this issue either in the SQL or in the database. Below steps can be performed to make use of the LOG ERRORS feature.

Step 1 : Create a table using the SQL in error. We just need the structure of the table. So please add a condition in the where clause (1=2).

create table emp_test as

SELECT emp.*

      ,sal / comm sal_to_comm_ratio

FROM   scott.emp emp

where 1=2;

Step 2: Create the error logging table for the new table created.

begin

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

end;

This creates a table with name ERR$_EMP_TEST in the database.

Step 3: Execute the SQL in an INSERT statement with LOG ERRORS clause appended at the end.

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;

This inserts all records without error to EMP_TEST while the records in error will be kept in ERR$_EMP_TEST.

Step 4: Query up ERR$_EMP_TEST to see the error message and records in error.

select * from ERR$_EMP_TEST;

PLSQL Script

Steps 1 to 3 can be automated using the below PLSQL script. This script accepts the problem SQL as input and performs the steps.

DECLARE

  l_sql_statement    VARCHAR2(30000) := '&select';

  l_create_table_sql VARCHAR2(32767);

  l_insert_table_sql VARCHAR2(32767);

  l_table_name       VARCHAR2(30) := 'XXERR' || TO_CHAR(SYSDATE

                                                       ,'DDMMYYYYHH24MISS');

  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';

  
  dbms_output.put_line('Table name is ' || l_table_name);

  dbms_output.put_line(l_create_table_sql);

   
  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;

On executing the script with SQL as input, the DBMS output displays the name of the error table which can be queried to find the record(s) in error.

 Limitations

• The single quotes in the SQL needs to be escaped when running from SQL*Plus or another IDE. This can be done by replacing all single quotes with 2 quotes.

• All derived values in the select clause list need to be given explicit aliases. Otherwise, the table creation will fail.

• This method can uncover only those issues arising from SELECT clause. The exceptions raised from FROM and WHERE clause will still fail the entire SELECT statement.