Following article shows how to catch all ORA- statements in sqlplus using clause

SET ERRORLOGGING ON

SET ERRORLOGGING command enables error logging of SQL, PL/SQL and SQL*Plus errors. Errors can go to default (SPERRORLOG) or manually defined table.

Following options can be used

SET ERRORL[OGGING] {ON | OFF} [TABLE [schema.]tablename] 
[TRUNCATE] [IDENTIFIER identifier]

Turn on error logging – two options long and short – as default it will create SPERRORLOG in current schema

SET ERRORLOGGING ON

SET ERRORL ON

Layout of SPERRORLOG

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

Turn off error logging – two options long and short

SET ERRORLOGGING OFF

SET ERRORL OFF

As default above options catch errors to SPERRORLOG but you can specify your dedicated table – few example.

!!! NOTE – first you need to create your own table manually !!!

CREATE TABLE my_table
(
  USERNAME   VARCHAR2(256),
  TIMESTAMP  TIMESTAMP(6),
  SCRIPT     CLOB,
  IDENTIFIER VARCHAR2(256),
  MESSAGE    CLOB,
  STATEMENT  CLOB
);

SET ERRORLOGGING ON TABLE my_table

SET ERRORL TABLE my_table

You can always truncate own error logging table but only with ON option

SET ERRORL ON TABLE sperrorlog TRUNCATE

SET ERRORL ON TABLE my_table TRUNCATE

Set identifier in error logging table – it’s possible to specify unique entry in error table when ORA- is catched. It’s stored later in column IDENTIFIER VARCHAR2(256).

SET ERRORL ON TABLE my_table TRUNCATE IDENTIFIER my_id

SET ERRORL ON IDENTIFIER my_id

Example

Logon to database turn on error logging with TRUNCATE and IDENTIFIER option

sqlplus connect tomasz/tomasz
SET ERRORL ON TABLE sperrorlog TRUNCATE IDENTIFIER my_id

run fake query that generate error

SQL> select 1 from not_existing_table;
select 1 from not_existing_table
              *
ERROR at line 1:
ORA-00942: table or view does not exist

read error logging table and see all error for your error

SQL> set pagesize 0
SQL> select * from sperrorlog;
TOMASZ
18/08/03 11:16:08,000000

my_id
ORA-00942: table or view does not exist
select 1 from not_existing_table

Have a fun 🙂
Tomasz