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.
SQL*Plus: Release 11.2.0.2.0 Production on Wed Jan 1 22:48:33 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> 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> SELECT timestamp, script, substr(message,1,100) message FROM sperrorlog;

TIMESTAMP
---------------------------------------------------------------------------
SCRIPT
--------------------------------------------------------------------------------
MESSAGE
--------------------------------------------------------------------------------
01-JAN-14 10.49.02.000000 PM

ORA-00942: table or view does not exist

SQL>

So far so good. Now it’s time to show where it fails my expectations.

Lets try the above script with a small modification.

SET ERRORLOGGING ON
INSERT INTO bad_table(a) VALUES(1);
SET ERRORLOGGING OFF
ROLLBACK;
SELECT timestamp, script, substr(message,1,100) message
  FROM sperrorlog;
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.

2 comments on “SQLPlus ERRORLOGGING does not keep error log on rollback

  • I have a script myscript.sql :
    WHENEVER SQLERROR EXIT SQL.SQLCODE
    DECLARE
    l_cnt NUMBER;
    BEGIN
    SELECT
    INTO l_cnt
    FROM user_tab_columns t
    WHERE t.table_name = ‘IVN_TB_COVERSHEET_IMAGE’
    AND t.COLUMN_NAME =’EAM_ORGUNIT’;
    raise_application_error( -20001, ‘Column to this particular table is already added.’);

    END;
    /

    I run this script from a shell script : shell script has code :
    ;sqlplus -S /nolog <<!
    set errorlogging on truncate
    @myscript.sql
    set errorlogging off

    exit 0
    !

    code=$?
    echo $code

    When I run the shell script , it returns exit code 0 , if I remove the 'set errorlogging' part from shell script , it returns code 33.
    Can you please explain this behavior .

    • Hi.
      The script you’ve provided contains errors, however, after fixes I’ve tested it and didn’t get the issue you’re getting.
      I keep getting return code = 33 even if I use errorologging.

      I’ve tested it in a bit different way:

      Shell script:

      sqlplus -S /nolog <<!
      conn $db_user/$db_pass
      set errorlogging on truncate
      @myscript.sql
      set errorlogging off
      
      exit 0
      !
      
      code=$?
      echo $code
      

      SQL script:

      WHENEVER SQLERROR EXIT SQL.SQLCODE
      
      DECLARE
        l_cnt NUMBER;
      BEGIN
        SELECT COUNT(1) INTO l_cnt FROM user_tab_columns t
         WHERE t.table_name='IVN_TB_COVERSHEET_IMAGE'
           AND t.COLUMN_NAME ='EAM_ORGUNIT';
        raise_application_error( -20001, 'Column to this particular table is already added.');
      END;
      /
      

      What version of SQL Plus are you using?
      What version of database are you using?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.