Oracle 11g SQL Error Logging

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:

, , , , , ,

5 Responses to Oracle 11g SQL Error Logging

  1. Srinivas June 7, 2011 at 9:38 pm #

    Hi,

    I am trying to execute the multiple sql files , whose filenames are stored in single sql file.

    > Sql @/ddl/ExecuteScripts.sql

    which in turn have three procedure file, if one of the procedure fails with compilation error , i can see that message on standard output, however i cant see those details in “sperrorlog” table. any idea. Thanks

    • ittichai June 9, 2011 at 8:02 am #

      @Srinivas from my quick test, it captures PLS- and ORA- error messages. Any specific error you’ve received?

  2. Srinivas June 9, 2011 at 6:22 pm #

    Hi,

    Thanks for the update, I have received the following error when i execute the procedure from PL/SQL.
    ——
    Compilation errors for PROCEDURE TEST2
    Error: PLS-00201: identifier ‘V_TEST’ must be declared
    Line: 4
    Text: Select SysDate Into v_Test From Dual;
    Error: PL/SQL: ORA-00904: : invalid identifier
    Line: 4
    Text: Select SysDate Into v_Test From Dual;
    Error: PL/SQL: SQL Statement ignored
    Line: 4
    Text: Select SysDate Into v_Test From Dual;
    ——–

    I have following line in one of my procedure, where i have not declared v_Test.

    Select SysDate Into v_Test From Dual;

    I have following three START commands in one script file , which i am executing through sqlplus.

    START C:/ddl/TEST1.PRC
    START C:/ddl/TEST2.PRC
    START C:/ddl/TEST3.PRC

    following message are written to console

    Procedure created.

    Warning : Procedure created with compilation errors.

    Procedure created.

    • ittichai June 10, 2011 at 5:54 pm #

      Srinivas,
      You said the error message were on console. What is about in the sperrorlog?

      I tried to generate similar error messages as yours without any issues.

      This is in the MESSAGE column.

      MESSAGE
      —————————————-
      SP2-0310: unable to open file “proc1.sql”

      ORA-06550: line 1, column 7:

      PLS-00905: object ITTICHAI.PROC1 is invalid

      ORA-00936: missing expression

      ORA-06550: line 1, column 7:

      PL/SQL: Statement ignored

  3. Srinivas June 11, 2011 at 3:58 pm #

    Hi,
    Thanks for the update again.
    I am not getting the complete error message to console, it says “Warning : Procedure created with compilation errors.”,if execute Show Errors , it displays the errors, however i cannot see any rows in sperrorlog table.

    please look the below added message, I have copied all the messages from my console.
    SQL> set errorlogging on
    SQL> @C:\ddl\Ritvik.sql
    Procedure created.
    Warning: Procedure created with compilation errors.

    SQL> Select message from sperrorlog;

    no rows selected

    SQL> show errors
    Errors for PROCEDURE TEST2:

    LINE/COL ERROR
    ——– —————————————————————–
    4/1 PL/SQL: SQL Statement ignored
    4/21 PLS-00201: identifier ‘V_TEST’ must be declared
    4/28 PL/SQL: ORA-00904: : invalid identifier

    Please look the below settings for my SQLPlus, am i missing any

    SQL> show all
    appinfo is OFF and set to “SQL*Plus”
    arraysize 15
    autocommit OFF
    autoprint OFF
    autorecovery OFF
    autotrace OFF
    blockterminator “.” (hex 2e)
    btitle OFF and is the first few characters of the next SELECT statement
    cmdsep OFF
    colsep ” ”
    compatibility version NATIVE
    concat “.” (hex 2e)
    copycommit 0
    COPYTYPECHECK is ON
    define “&” (hex 26)
    describe DEPTH 1 LINENUM OFF INDENT ON
    echo OFF
    editfile “afiedt.buf”
    embedded OFF
    escape OFF
    escchar OFF
    FEEDBACK ON for 6 or more rows
    flagger OFF
    flush ON
    heading ON
    headsep “|” (hex 7c)
    instance “local”
    linesize 80
    lno 6
    loboffset 1
    logsource “”
    long 80
    longchunksize 80
    markup HTML OFF HEAD ” body {font:10pt Arial,Helvetica,sa
    ns-serif; color:black; background:White;} p {font:10pt Arial,Helvetica,sans-seri
    f; color:black; background:White;} table,tr,td {font:10pt Arial,Helvetica,sans-s
    erif; color:Black; background:#f7f7e7; padding:0px 0px 0px 0px; margin:0px 0px 0
    px 0px;} th {font:bold 10pt Arial,Helvetica,sans-serif; color:#336699; backgroun
    d:#cccc99; padding:0px 0px 0px 0px;} h1 {font:16pt Arial,Helvetica,Geneva,sans-s
    erif; color:#336699; background-color:White; border-bottom:1px solid #cccc99; ma
    rgin-top:0pt; margin-bottom:0pt; padding:0px 0px 0px 0px;} h2 {font:bold 10pt Ar
    ial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; margin-t
    op:4pt; margin-bottom:0pt;} a {font:9pt Arial,Helvetica,sans-serif; color:#66330
    0; background:#ffffff; margin-top:0pt; margin-bottom:0pt; vertical-align:top;}SQL*Plus Report” BODY “” TABLE “border=’1′ width=’90%’ alig
    n=’center’ summary=’Script output'” SPOOL OFF ENTMAP ON PREFORMAT OFF
    newpage 1
    null “”
    numformat “”
    numwidth 10
    pagesize 14
    PAUSE is OFF
    pno 1
    recsep WRAP
    recsepchar ” ” (hex 20)
    release 1102000100
    repfooter OFF and is NULL
    repheader OFF and is NULL
    serveroutput OFF
    shiftinout INVISIBLE
    showmode OFF
    spool OFF
    sqlblanklines OFF
    sqlcase MIXED
    sqlcode 0
    sqlcontinue “> ”
    sqlnumber ON
    sqlpluscompatibility 11.1.0
    sqlprefix “#” (hex 23)
    sqlprompt “SQL> ”
    sqlterminator “;” (hex 3b)
    suffix “sql”
    tab ON
    termout ON
    timing OFF
    trimout ON
    trimspool OFF
    ttitle OFF and is the first few characters of the next SELECT statement
    underline “-” (hex 2d)
    USER is “MANI”
    verify ON
    wrap : lines will be wrapped
    errorlogging is ON TABLE MANI.SPERRORLOG

Leave a Reply

Powered by WordPress. Designed by WooThemes