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:
Tags: 11g, Error, Logging, oracle, Oracle 11g, sql, SQL Plus
