11G – SQL*PLUS Error logging

The SQL*Plus interface has several interesting 11g release innovations, including the new error logging feature and the incorporation of default SQL*Plus settings in the SQL*Plus executable itself instead of in the traditional glogin.sql file.

SQL*Plus Error Logging

When you’re troubleshooting code errors, it’s common to use the show errors command in SQL*Plus to identify the errors in a PL/SQL statement. Other than this, there was no way to check code errors, and the errors were not stored for later examination. In Oracle Database 11g,there’s a new SQL*Plus command called set errorlogging, which stores all errors resulting from the execution of any SQL, PL/SQL, and even SQL*Plus commands in a special error logging table.

By default, the set errorlogging command causes any query errors to be written to the default table SPERRORLOG. You can specify your own table name for the error logging table, instead of using this default table name. For each error, the error logging feature logs the following bits of information:

• The username

• The time when the error occurred

• The name of the script that contains the query, if a script was used

• A user-defined identifier

• The ORA, PLS, or SP2 error message

• The query statement that caused the error

By default, error logging is turned off, as you can see from the following query:

SQL> show errorlogging
errorlogging is OFF

You can turn error logging on with the set errorlogging command, as shown here:

SQL> set errorlogging on;

If you issue the show errorlogging command again to ensure that error logging has been successfully turned on, you’ll see something interesting:

SQL> connect hr/hr
SQL> show errorlogging
errorlogging is ON TABLE HR.SPERRORLOG

Not only does the database turn error logging on, but it also creates a new table called hr.sperrorlog to hold the error messages. The prefix to the error table is the same as the name of the schema owner who sets error logging on. In this case, we logged in as the user hr, so the error log is created in the hr schema.

The following example shows how to query the error logging table, sperrorlog, to retrieve the error messages and the SQL statements or PL/SQL code that generated those error messages:

SQL> select username,statement,message from sperrorlog;
USERNAME STATEMENT                                                       MESSAGE
——– ————————–                                                  ————————–
HR       create table employees as select * from employees ORA-00955: name is already used by an existing object

The first message indicates that the table creation statement failed because there is already a table with an identical name. The second message shows that the error was because of the presence of an invalid column name in a select statement.