Oracle 11g SQL Error Logging

We’re working on the installation scripts for an internal PL/SQL applications. Multiple scripts are called from the main one. To capture errors from script execution, normally the spool syntax will be used in the scripts to pipe out all executions into log files, and then later the deployment team members will examine them using find/search ORA- for any errors. This would work fine if there are only a few scripts but it becomes cumbersome when multiple scripts are involved. In addition we’d like to be able to run the scripts on either Windows and Unix platforms, handling OS file paths for multiple platforms using spool adds unnecessary layer.

The SQL Error Logging is a new feature in 11g. This simplifies the way we capture and locate error messages as information now will be stored in database table instead of OS files.

To check whether or not the Error Logging is enabled.

SQL> show errorlogging

errorlogging is OFF

To enable the Error Logging. The default table SPERRORLOG is created.

SQL> set errorlogging on

By default, the SPERRORLOG will be created under current user. In this sample, the current schema is TEST1_USER.

SQL> show errorlogging

errorlogging is ON TABLE TEST1_USER.SPERRORLOG

Here is the structure of this table SPERRORLOG.

SQL> desc SPERRORLOG
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
USERNAME                                           VARCHAR2(256)
TIMESTAMP                                          TIMESTAMP(6)
SCRIPT                                             VARCHAR2(1024)
IDENTIFIER                                         VARCHAR2(256)
MESSAGE                                            CLOB
STATEMENT                                          CLOB

To enable the Error Logging to an user defined table instead of the default SPERRORLOG table.

SQL> set errorlogging on table [schema].[table]

If non-default, the table has to be created in advance, otherwise you will get an error.

SQL> set errorlogging on table my_sperrorlog;

SP2-1507: Errorlogging table, role or privilege is missing or not accessible

Here is the syntax to create an user-defined table. If this table is created on a different schema, an insert grant to is needed for the current user.

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

SQL> set errorlogging on table my_sperrorlog

SQL> show errorlogging
errorlogging is ON TABLE TEST1_USER.my_sperrorlog

Demo
Generate error #1.

SQL> SELECT 1/0 FROM DUAL;
SELECT 1/0 FROM DUAL
*
ERROR at line 1:
ORA-01476: divisor is equal to zero

Generate error #2.

SQL> alter table EMP add new_column VARCHR2(1);
alter table EMP add new_column VARCHR2(1)
*
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option

Check the SQL Error Logging.

SQL> column username format A10
SQL> column message format A30 wrap
SQL> column statement format A30 wrap

SQL> SELECT username, STATEMENT, message FROM sperrorlog;

USERNAME   STATEMENT                      MESSAGE
---------- ------------------------------ ------------------------------
TEST1_USER SELECT 1/0 FROM DUAL           ORA-01476: divisor is equal to
zero

TEST1_USER alter table EMP add new_column ORA-01735: invalid ALTER TABLE
VARCHR2(1)                     option

Without commit, other sessions won’t see this information.

SQL> commit;

Commit complete.

Set an unique identifier to make it easier to identify the logging record. In sample here, the identifier is set to REL1.

SQL> set errorlogging on identifier 'REL1'

Generate error #3.

SQL> alter table EMP modify ABC NOT NULL;
alter table EMP modify ABC NOT NULL
*
ERROR at line 1:
ORA-00904: "ABC": invalid identifier

Check the SQL Error Logging with the identifier=’REL1′.

SQL> SELECT username, statement, message, IDENTIFIER
FROM sperrorlog where IDENTIFIER='REL1';

USERNAME   STATEMENT                      MESSAGE                        IDENT
---------- ------------------------------ ------------------------------ -----
TEST1_USER alter table EMP modify ABC NOT ORA-00904: "ABC": invalid iden REL1
NULL                          tifier

Truncate the Error Logging to clear all existing rows in the error log table. This will clear out all records regardless of who creates them.

SQL> set errorlogging on truncate

SQL> SELECT * FROM sperrorlog;
No rows selected

There is no set errorlogging truncate only a specified identifier. Doing below is the same set errorlogging on truncate. So basically the identifier is ignored.

SQL> set errorlogging on truncate identifier 'REL1'

But you can just delete records as the regular table.

SQL> delete sperrorlog where IDENTIFIER='REL1';

SQL> commit;

Disable Error Logging. (Log off will automatically disable it.)

SQL> set errorlogging OFF

SQL> show errorlogging
errorlogging is OFF

Will it work with previous versions of Oracle database?

Since the SQL Error Logging is the feature on client, I have no reason to believe that it would not work with pre-11g databases.

The 11g SQL Plus Client to 10.2.0.1 Database

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Solaris: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> show errorlogging
errorlogging is OFF

SQL> set errorlogging ON

SQL> show errorlogging
errorlogging is ON TEST1_USER.SPERRORLOG

SQL> select username, TIMESTAMP from  sperrorlog;

no rows selected

SQL> SELECT 1/0 FROM DUAL;
SELECT 1/0 FROM DUAL
*
ERROR at line 1:
ORA-01476: divisor is equal to zero

SQL> SELECT username, STATEMENT, message FROM sperrorlog;

USERNAME   STATEMENT                      MESSAGE
---------- ------------------------------ ------------------------------
TEST1_USER SELECT 1/0 FROM DUAL           ORA-01476: divisor is equal to
zero

The 11g SQL Plus Client to 10.1.0.4 Database

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - 64bi
PL/SQL Release 10.1.0.4.0 - Production
CORE    10.1.0.4.0      Production
TNS for Solaris: Version 10.1.0.4.0 - Production
NLSRTL Version 10.1.0.4.0 - Production

SQL> show errorlogging
errorlogging is OFF

SQL> set errorlogging ON

SQL> show errorlogging
errorlogging is ON TABLE TEST1_USER.SPERRORLOG

SQL> select username, TIMESTAMP from  sperrorlog;

no rows selected

SQL> SELECT 1/0 FROM DUAL;
SELECT 1/0 FROM DUAL
*
ERROR at line 1:
ORA-01476: divisor is equal to zero

SQL> SELECT username, STATEMENT, message FROM sperrorlog;

USERNAME   STATEMENT                      MESSAGE
---------- ------------------------------ ------------------------------
TEST1_USER SELECT 1/0 FROM DUAL           ORA-01476: divisor is equal to
zero

The 11g SQL Plus Client to 9i Database
Unfortunately I don’t have any Oracle 9i databases.  🙂

The 11g SQL Plus Client to 8i Database

Surprisingly, I still have one 8i database left. But, well, the 11g client no longer supports the 8i database.

ERROR:
ORA-03134: Connections to this server version are no longer supported.

Related topics:

• The Access Control Lists to Network Services (e.g., UTL_HTTP, UTL_SMTP, UTL_TCP, etc.) in Oracle 11g
• 11gR2 New Feature – Alter Database Link to Change Password