|
|
|
|
|
|
|
|
| Re: How to capture Error Code in sqlplus [message #595389 is a reply to message #595388] |
Tue, 10 September 2013 14:38   |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
I have always followed the documentation,and it says, normally errors will be logged in SPERRORLOG table by default, however, you could also create your own user defined table for error logging. When you want to spool the errors into a file, you could fetch it from either of the tables. You just need SELECT privilege.
Go through the example and parameters required to be altered here
I made a small demonstration in SCOTT schema using the default error log table SPERRORLOG, see if this helps to understand easily -
1. SP2 error
SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 11 01:27:00 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> desc sperrorlog;
Name Null? Type
----------------------------------------- -------- ----------------------------
USERNAME VARCHAR2(256)
TIMESTAMP TIMESTAMP(6)
SCRIPT VARCHAR2(1024)
IDENTIFIER VARCHAR2(256)
MESSAGE CLOB
STATEMENT CLOB
SQL> truncate table sperrorlog;
Table truncated.
SQL> set errorlogging on;
SQL> selct * from dual;
SP2-0734: unknown command beginning "selct * fr..." - rest of line ignored.
SQL> select timestamp, username, script, statement, message from sperrorlog;
TIMESTAMP
---------------------------------------------------------------------------
USERNAME
--------------------------------------------------------------------------------
SCRIPT
--------------------------------------------------------------------------------
STATEMENT
--------------------------------------------------------------------------------
MESSAGE
--------------------------------------------------------------------------------
11-SEP-13 01.27.29.000000 AM
SCOTT
TIMESTAMP
---------------------------------------------------------------------------
USERNAME
--------------------------------------------------------------------------------
SCRIPT
--------------------------------------------------------------------------------
STATEMENT
--------------------------------------------------------------------------------
MESSAGE
--------------------------------------------------------------------------------
selct * from dual;
SP2-0734: unknown command beginning "selct * fr..." - rest of line ignored.
2. ORA error
SQL> truncate table sperrorlog;
Table truncated.
SQL> select * from dula;
select * from dula
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select timestamp, username, script, statement, message from sperrorlog;
TIMESTAMP
---------------------------------------------------------------------------
USERNAME
--------------------------------------------------------------------------------
SCRIPT
--------------------------------------------------------------------------------
STATEMENT
--------------------------------------------------------------------------------
MESSAGE
--------------------------------------------------------------------------------
11-SEP-13 01.36.08.000000 AM
SCOTT
TIMESTAMP
---------------------------------------------------------------------------
USERNAME
--------------------------------------------------------------------------------
SCRIPT
--------------------------------------------------------------------------------
STATEMENT
--------------------------------------------------------------------------------
MESSAGE
--------------------------------------------------------------------------------
select * from dula
ORA-00942: table or view does not exist
3. Similarly, you can have PLS errors too.
In your case you execute it through scripts, you can do it like this, and later spool the errors into a file. I kept these three lines in the sperrorlog_test.sql file -
truncate table sperrorlog;
selct * from dual;
select * from dula;
SQL> @D:\sperrorlog_test.sql;
Table truncated.
SP2-0734: unknown command beginning "selct * fr..." - rest of line ignored.
select * from dula
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select TIMESTAMP, SCRIPT, STATEMENT, MESSAGE from sperrorlog;
TIMESTAMP
---------------------------------------------------------------------------
SCRIPT
--------------------------------------------------------------------------------
STATEMENT
--------------------------------------------------------------------------------
MESSAGE
--------------------------------------------------------------------------------
11-SEP-13 01.50.17.000000 AM
D:\sperrorlog_test.sql;
SP2-0734: unknown command beginning "D:\sperror..." - rest of line ignored.
TIMESTAMP
---------------------------------------------------------------------------
SCRIPT
--------------------------------------------------------------------------------
STATEMENT
--------------------------------------------------------------------------------
MESSAGE
--------------------------------------------------------------------------------
11-SEP-13 01.50.27.000000 AM
D:\sperrorlog_test.sql
selct * from dual;
SP2-0734: unknown command beginning "selct * fr..." - rest of line ignored.
TIMESTAMP
---------------------------------------------------------------------------
SCRIPT
--------------------------------------------------------------------------------
STATEMENT
--------------------------------------------------------------------------------
MESSAGE
--------------------------------------------------------------------------------
11-SEP-13 01.50.27.000000 AM
D:\sperrorlog_test.sql
select * from dula
ORA-00942: table or view does not exist
SQL>
Regards,
Lalit
[Updated on: Tue, 10 September 2013 15:30] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: How to capture Error Code in sqlplus [message #595554 is a reply to message #595524] |
Wed, 11 September 2013 13:48 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
LOOKUP_BI wrote on Wed, 11 September 2013 19:28Is there a way instead of reading the error log table to directly spool out an ERROR file instead with error in it ?
And I said,
Lalit Kumar B wrote on Wed, 11 September 2013 01:08you could also create your own user defined table for error logging. When you want to spool the errors into a file, you could fetch it from either of the tables.
In addition to the above reply, if you want to be particularly specific about each session's error to be spooled into a file you could do this -
SQL> set errorlogging on identifier my_session_identifier
Above mentioned IDENTIFIER keyword becomes a column in SPERRORLOG table. It would get populated with the string value "my_session_identifier". Now you just need to do this -
SQL> select timestamp, username, script, statement, message
2 from sperrorlog
3 where identifier = 'my_session_identifier';
Now last but not the least, what you have been demanding for - to spool the session specific errors into a file, just do this(Michel already showed you how to spool the erroneous data into a file) :-
SQL> spool error.log
SQL> select timestamp, username, script, statement, message
2 from sperrorlog
3 where identifier = 'my_session_identifier';
SQL> spool off
Apart from all the above mentioned steps, I don't think/know/demonstrate anything else that exists in Oracle's latest release.
Regards,
Lalit
|
|
|
|
| Re: How to capture Error Code in sqlplus [message #595594 is a reply to message #595554] |
Thu, 12 September 2013 04:36 |
 |
pradip.tk
Messages: 3
Registered: July 2013
Location: India
|
Junior Member |
|
|
Yes, Lalit is correct. You just need to spool on and spool off before and after calling each .sql file.
For eg;
spool file1
@file1.sql
spool off;
You can grep for any errors or ora errors through a shell script.
BTW, in Oracle 12c, there is a feature to capture all the DDL commnds. Should be useful.
Thx
Pradeep
[Updated on: Thu, 12 September 2013 04:38] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Script to catch ALL Error in sqlplus (SQL, OS and SP2) [message #689500 is a reply to message #595382] |
Sun, 21 January 2024 08:22 |
 |
Lionel94
Messages: 1
Registered: January 2024
|
Junior Member |
|
|
Here is an SQL script that I call "execute_sql.sql" and which is used to execute another 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 inside the test.sql script (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
|
|
|
|
| Re: Script to catch ALL Error in sqlplus (SQL, OS and SP2) [message #689501 is a reply to message #689500] |
Sun, 21 January 2024 09:20 |
 |
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Welcome to the forum.
Thanks for formatting your post.
Some remarks about it:
- SQL*Plus error logging mechanism traps PLS, SQL, ORA and SP2 errors not OS ones (but those already internally trapped by SQL*Plus and converted to an SP2 ones
- With "whenever sqlerror exit sql.sqlcode" you stop SQL*Plus at first error, so you won't trap all errors
- In addition, this statement prevents from your PL/SQL block to be executed
- The "show errors" statement will show the errors for the last procedural object created, a script may have several ones (but of course this statement is never reached due to the "whenever" and the "raise_application_error" in the PL/SQL block unless there are no errors)
- SPERRORLOG table should be cleared from the rows with the same identifier otherwise the PL/SQL block will report the previous executions errors
- I think the PL/SQL block should be replaced by a simple SELECT which will avoid PL/SQL and the "set serveroutput on" which add an overhead and may be a problem
- I don't like the "clear screen" at the begnning and the "exit" at the end but this is my opinion
- Also I don't like you modify many SQL*Plus settings which the called script may rely on, this is also my opinion and can easily be modified as each one want.
In the end, this script is a good idea and may be adapted for anyone case (like change "\" to "/" for *ix systems).
[Updated on: Sun, 21 January 2024 12:06] Report message to a moderator |
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
