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:
