Oracle 11g SQLPLUS error logging

Written by Kokei's Blog
Posted Apr 22, 2011 at 4:54 PM

In Oracle 11g, error messages in the SQLPLUS can be recorded to a table automatically when ERRORLOGGING option is turned on.

By default, this feature is off, you can enable this feature just by typing "SET ERRORLOGGING ON" in SQLPLUS and a table called sperrorlog will be created in the current schema. Then all subsequent errors generating from SQL or PL/SQL statements will be written to sperrorlog.

Here is an example

SQL> set errorloggin on

SQL> desc sperrorlog

Name Null? Type

----------------------------------------- -------- ----------------------------

USERNAME VARCHAR2(256)

TIMESTAMP TIMESTAMP(6)

SCRIPT VARCHAR2(1024)

IDENTIFIER VARCHAR2(256)

MESSAGE CLOB

STATEMENT CLOB

SQL> select * from aaa;