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
Here is an SQL script that I call “execute_sql.sql” and which is responsible for executing the script given to it as a parameter.
Example: execute_sql.sql test.sql
The “execute_sql.sql” script will be able to catch all errors (SQL, OS and SP2)
set feedback off heading off newpage none verify off termout off linesize 500
column SUBST_OS_USER new_val OS_USER
column SUBST_HOST new_val HOST
column SUBST_EXECUTION_DATE new_val EXECUTION_DATE
column SUBST_FULL_PATH_FILE new_val FULL_PATH_FILE
column SUBST_SCRIPT_NAME new_val SCRIPT_NAME
select
sys_context(‘USERENV’, ‘OS_USER’) SUBST_OS_USER
, sys_context(‘USERENV’, ‘HOST’) SUBST_HOST
, to_char(CURRENT_TIMESTAMP, ‘DD/MM/YYYY” a “HH24″h”MI”m”SSXFF”s”‘) SUBST_EXECUTION_DATE
, replace(‘&1’, ‘\\’, ‘\’) SUBST_FULL_PATH_FILE
, substr(‘&1’, instr(‘&1′,’\’,-1) + 1) SUBST_SCRIPT_NAME
from dual;
define IDENTIFIER=’&SCRIPT_NAME : &OS_USER@&HOST le &EXECUTION_DATE’
set termout on
clear screen
prompt #########################################################################################################################
prompt # Script SQL : &FULL_PATH_FILE
prompt # Information : &OS_USER le &EXECUTION_DATE
prompt #########################################################################################################################
set errorlogging on IDENTIFIER ‘&IDENTIFIER’
set feedback on heading on serveroutput on sqlblanklines on newpage 1
whenever sqlerror exit sql.sqlcode
@”&FULL_PATH_FILE”
set define on
set feedback off verify off
declare
iCOUNT NUMBER;
begin
select count(1) into iCOUNT from SPERRORLOG where identifier = ‘&IDENTIFIER’;
if iCOUNT > 0
then raise_application_error (-20006, iCOUNT || case when iCOUNT=1 then ‘ erreur SQLPLUS dĂ©tectĂ©’ else ‘ erreurs SQLPLUS dĂ©tectĂ©s’ end);
end if;
end;
/
–Affichage des erreurs pour les crĂ©ation d’objet (procĂ©dure, fonction, vue, etc…)
show errors
prompt
exit