SQLPlus ERRORLOGGING does not keep error log on rollback

Recently I'm heavily involved in SQL*Plus scripting automation tasks. I must admit that despite all the settings you may set and retrieve with SQL*Plus, it is a real pain to do some good automation with this tool. I'm very glad that Oralce continues to extend Oracle SQL*Plus features, but i must say that it seems like the design details for those new features are not well thought, which gives me a headache when working with this tool and forces me to look for tricks and workarounds for badly designed functionality. In this post I'll write about the downsides of the ERRORLOGGING feature introduced with SQL*Plus 11.1 and above.

The feature itself is described in the Oracle documentation, so i will not reedit it here. For purposes of the post i'm using the Oracle sample schema HR. The SET ERRORLOGGING ON command however will not give you the expected results in some cases. Follow the examples to see the ERRORLOGGING fail to log errors into errors table. Lets first create the error logging table.

CREATE TABLE sperrorlog(
  username VARCHAR(256),
  timestamp TIMESTAMP,
  script VARCHAR(1024),
  identifier VARCHAR(256),
  message CLOB,
  statement CLOB
);

Once done, we can see it's working with the following script.

SET ERRORLOGGING ON
INSERT INTO bad_table(a) VALUES(1);
SET ERRORLOGGING OFF
SELECT timestamp, script, substr(message,1,100) message
  FROM sperrorlog;[/sql]
The output below proves the errorlogging to work.
CB1
[code gutter="0"]SQL*Plus: Release 11.2.0.2.0 Production on Wed Jan 1 23:22:20 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Use "connect username/password@XE" to connect to the database.
SQL> conn hr/hr 
Connected.
SQL> truncate table sperrorlog;

Table truncated.

SQL> SET ERRORLOGGING ON
SQL> INSERT INTO bad_table(a) VALUES(1);
INSERT INTO bad_table(a) VALUES(1)
            *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> SET ERRORLOGGING OFF
SQL> ROLLBACK;

Rollback complete.

SQL> SELECT timestamp, script, substr(message,1,100) message FROM sperrorlog;

no rows selected

SQL>

The error logging is not using autonomous transaction to log the error that occurs. You will not be able to log errors using ERRORLOGGING command if your script is performing ROLLBACK operations. Oracle guys did a really sloppy work by not implementing the ERRORLOGGING feature in autonomous transaction. If your scripts are using ERRORLOGGING along with ROLLBACK they will not work as expected. Now here is a real-life example. Consider that you have a script that is supposed to Update and Insert some data in EMPLOYEES table. The script is supposed to log all the errors that occur. The script is also supposed to exit with failure exit code and rollback all the changes done if an error occurred.

WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
SET ERRORLOGGING ON;

REM Start processing changes

UPDATE EMPLOYEES
   SET SALARY = SALARY * 1.1;

INSERT INTO
EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,
  HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID)
 VALUES (100,'Steven','King','SKING','515.123.4567',
  to_date('2003-06-17 00:00:00','YYYY-MM-DD HH24:MI:SS'),
  'AD_PRES',24000,null,null,90);

SET ERRORLOGGING OFF;

REM Finished processing changes

EXIT SUCCESS COMMIT;

The script is written to fail on the insert statement. Unfortunately, there is no trace of the failure in the ERRORLOG table, as we requested to ROLLBACK all changes on SQLERROR.

See the solution I've found for this issue.