SQL*Plus Error Logging in Oracle 11g

SQL*Plus  is the commonly used tools by the DBAs . Sql*Plus Error Logging is one of the new useful feature in Oracle 11g .It provides additional methods of trapping errors . When error logging is enabled, it records sql , pl/sql  and sql*plus  errors and associated parameters in an error log table(SPERRORLOG by default) and we can then query the log table to review errors resulting from a query.

Note : It is a 11g SQL*Plus feature not with database engine.

Why  Error Logging ?

We normally spool the syntax to capture the errors from the scripts  and track the spool logs for the error output . This is work fine for single or few script but cumbersome when multiple scripts are involved.Secondly we need the OS path to store the scripts,permission and all . To overcome from this  scenario's Error Logging is useful feature  to capture and locate the errors in the database table rather than the OS  files .

Steps to Activate the Error Logging : 

1.) Check the status of Error Logging :  To check the status of error logging , fire the below command  

SQL> show errorlogging
errorlogging is OFF
Note: Error logging is set OFF by default.

2.) Enable  the Error Logging :  Whenever we enable the error loging the default table SPERRORLOG is created . Enable by using the below command 

SQL> set errorlogging on
SQL> show errorlogging
errorlogging is ON TABLE  SCOTT.SPERRORLOG
As, we see that the default table  "SPERRORLOG" is created in scott schemas, since the current user is scott . Hence, sperrorlog table is created current user .


Creating a User Defined Error Log Table :

We can create one or more error log tables to use other than the default . Before specifying a user defined error log table , let's  have look on default errorlog 

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

For each error, the error logging feature logs the following bits of information. To use a user defined log table, we must have permission to access the table, and we must issue the SET ERRORLOGGING command with the TABLE schema.tablename option to identify the error log table and the schema if applicable. Here is syntax to create  user-defined table..

SQL> set errorlogging on table [schema].[table]
for example :  
SQL> set errorlogging on table  hr.Error_log_table 


Demo to create user-defined table : 

Step 1 : Create the table : If we want create the error logging user-defined table and if table doesnot exist then get the below error as 

SQL> set errorlogging on table  hr.Error_log_table
SP2-1507: Errorlogging table, role or privilege is missing or not accessible


create  the table as  
SQL> create table  Hr.Error_log_table ( username   varchar(256),  timestamp  TIMESTAMP,  script    varchar(1024),  identifier varchar(256),  message  CLOB,  statement  CLOB) ;  
Table created.


Step 2 :   Create user-defined error logging table  
SQL> show errorlogging
errorlogging is OFF
SQL> set errorlogging on table  hr.Error_log_table
SQL> show errorlogging
errorlogging  is  ON TABLE   hr.Error_log_table


Step 3 :  Generate some errors 
SQL> selet  *   from  employees ;
SP2-0734: unknown command beginning "selet * fr..." - rest of line ignored.
SQL> select  *   from  employe ;
select * from employe
              *
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> set linesze 2000
SP2-0158: unknown SET option "linesze"


Step  4 : Check the error logging from the user-defined  errorlog 
SQL> select  *  from  hr.Error_log_table ;
SQL> commit ;

Without commit, other sessions won’t see this information. Here i have commit it and taken the output from other session for the sake of proper formatting purpose. 

We can truncate to clear all existing rows in the error log table and begins recording errors from the current session. as 

SQL> set errorlogging on truncate
SQL> select * from  Error_log_table ;
No rows selected

We can also set an unique identifier to make it easier to identify the logging record.We can use it to identify errors from a particular session or from a particular version of a query.

SQL> set errorlogging on identifier  'MARK' 
SQL > select  *  from  employ ; 
select * from employ
              *
ERROR at line 1:
ORA-00942: table or view does not exist


Now check the identifier : 
SQL> select  *  from  hr.Error_log_table ; 

We can  delete records as the regular table.
SQL> delete hr.Error_log_table  where IDENTIFIER='MARK' ; 
SQL> commit;


Disable Error Logging : 
SQL> set errorlogging OFF
SQL> show errorlogging
errorlogging is OFF




Enjoy    :-)