Systems Engineering and RDBMS

Oracle 11g: Error Logging in SQL *PLUS

Posted by decipherinfosys on March 14, 2009

Along with all the other feature enhancements in Oracle 11g,  SQL*Plus also received a very good enhancement for error logging.  In previous releases, in order to keep track of all the DDL/DML errors we have to spool the output and then review the output from the spool file at the end of the execution. In 11g, we can simply turn on the error logging and all the errors will be stored in special table called SPERRORLOG. By default, errorlogging will be off. Let us start with an example. Connect to SQL*Plus session using appropriate credentials. Check the errorlogging capabilities.

SQL> show errorlogging
errorlogging is OFF

Let us turn on the logging.

SQL> set errorlogging on

Once we turn on the logging, SPERRORLOG table will be created if it does not exist in the schema you are connected to. If we issue show command again, it will display following details.

SQL> show errorlogging
errorlogging is ON TABLE SCOTT.SPERRORLOG

And here is the table definition.

SQL> desc sperrorlog
Name                                      Null?    Type
—————————————– ——– —————-

USERNAME                                           VARCHAR2(256)
TIMESTAMP                                          TIMESTAMP(6)
SCRIPT                                             VARCHAR2(1024)
IDENTIFIER                                         VARCHAR2(256)
MESSAGE                                            CLOB
STATEMENT                                          CLOB

Now let us start issuing erroneous commands.  ‘TEST’ table is already there in our test schema. We are issuing multiple commands with correct command inbetween.

SQL> ALTER TABLE TEST ADD NEW_COL VARCHR2(2);
*
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option

SQL> SELECT 1/0 FROM DUAL;
*
ERROR at line 1:
ORA-01476: divisor is equal to zero

SQL> UPDATE TEST
2  SET TEST_DESC = ‘HELLO’
3  WHERE TEST_ID = 1
4  /

1 row updated.

SQL> COMMIT;

Commit complete.

SQL> UPDATE TEST
2  SET TEST_DESC = LPAD(‘ ‘,32,’A’)
3  WHERE TEST_ID = 1;

ERROR at line 2:
ORA-12899: value too large for column “SCOTT”.”TEST”.”TEST_DESC” (actual: 32,
maximum: 30)

Following are the details from sperrorlog table. There are statement and error columns, which we are interested in. This helps us in reviewing statement and corresponding error.

SQL> column username format A5
SQL> column message format A30 wrap
SQL> column statement format A30 wrap
SQL> SELECT username,statement,message FROM sperrorlog;

USERN STATEMENT                      MESSAGE
—– —————————— ——————————
SCOTT ALTER TABLE TEST ADD NEW_COL V ORA-01735: invalid ALTER TABLE
ARCHR2(2)                       option

SCOTT SELECT 1/0 FROM DUAL           ORA-01476: divisor is equal to
Zero

SCOTT UPDATE TEST SET TEST_DESC = LP ORA-12899: value too large for
AD(” “,32,”A”) WHERE TEST_ID =  column “SCOTT”.”TEST”.”TEST_D
1                             ESC” (actual: 32, maximum: 30)

These records will be available from all sessions. So if we are interested in session specific records then we need to assign identifier when turning the errorlogging on. This way we can restrict the records for specific identifier using where clause. In order to see errors across session, one has to commit the changes in each session to make error visible in other session.

SQL> set errorlogging on identifier ‘TEST1’

Records from SPERRORLOG table do not get deleted automatically. Either you have to purge records or you can turn the errorlogging on with truncate.  Again if commit command is not issued, records will be still available in other session.

SQL> set errorlogging on truncate

We performed testing for this feature on 11g on Windows XP. No matter when and how we get the errors, all errors will be reported. Best thing about it is as long as you have 11g client, it can work against 10g databases as well. This is very useful when we have to make lots of schema changes via scripts during development phase. We can start errorlogging and review it once scripts are done with the execution.

Resources:

  • Otn article: – here.
  • Book – Oracle 11g New features for Developers and DBAs by Sam Alapati, Charles Kim

5 Responses to “Oracle 11g: Error Logging in SQL *PLUS”

  1. Nice feature!

    Thanks for sharing.

  2. Raghu said

    Thanks, This was something new and really helpful..

  3. Anonymous said

    What if the statement length is greater than 4000 chars? I get the SP2-1519 error. How do you get around that?

    • Mor L said

      This happens on Oracle Client 11.2.0.3 (maybe more) and is solved when using Oracle Client 12.1 – so you just need to upgrade your oracle client…

    • Mor L said

      This is a bug in sqlplus’s errorlogging feature in Oracle Client 11.2.0.3 (maybe more) and is solved when using Oracle Client 12.1 – so just upgrade your oracle client to 12c.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: