Home » Other » Client Tools » How to capture Error Code in sqlplus (SQLPLUS)
icon5.gif  How to capture Error Code in sqlplus [message #595382] Tue, 10 September 2013 14:03 Go to next message
LOOKUP_BI
Messages: 5
Registered: September 2013
Junior Member
I have a .sql file that is used as a wrapper file that when executes within sqlplus (9.2.0.1.0), executes a bunch of .sql files within it. Example below:

WRAPPER.SQL
START D:\Scripts\A.sql "'04-01-2012 00:00:00'"
START D:\Scripts\B.sql "'04-01-2012 00:00:00'"
START D:\Scripts\C.sql "'04-01-2012 00:00:00'"
START D:\Scripts\D.sql "'04-01-2012 00:00:00'"
START D:\Scripts\E.sql "'04-01-2012 00:00:00'"
EXIT;

Each of the .sql file (A,B,C,D,E)
Spools individual output of sql statment within them. Each of the indv .sql file queries different tables with different filters(where) clause.

I would like to capture any error (OS,SQL,DB) into indv error file (A_ERROR.log). The reason being is because later in the process we need to validate if there were any errors before processing and loading the data into our SQL database
Re: How to capture Error Code in sqlplus [message #595383 is a reply to message #595382] Tue, 10 September 2013 14:07 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Yes, it could be done, however, you need to have SQL*Plus Release 11.1.

I don't want to repeat similar words, have a look at SQL*Plus Error Logging in SQL*Plus Release 11.1
Re: How to capture Error Code in sqlplus [message #595385 is a reply to message #595383] Tue, 10 September 2013 14:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68658
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
/forum/fa/2115/0/ I learn something new today...

Regards
Michel
Re: How to capture Error Code in sqlplus [message #595388 is a reply to message #595385] Tue, 10 September 2013 14:29 Go to previous messageGo to next message
LOOKUP_BI
Messages: 5
Registered: September 2013
Junior Member
Hi Lalit, Thanks for getting back to me..We only have read access to the Source Oracle database.I would prefer to output the error to indv file's, would I be still able to do this with read only access? Could you help me with some examples if any
Re: How to capture Error Code in sqlplus [message #595389 is a reply to message #595388] Tue, 10 September 2013 14:38 Go to previous messageGo to next message
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 #595390 is a reply to message #595389] Tue, 10 September 2013 15:59 Go to previous messageGo to next message
LOOKUP_BI
Messages: 5
Registered: September 2013
Junior Member
Hi Lalit, I tried to do DESC sperrorlog or SELECT * FROM sperrorlog; Both gave me error
ERROR ORA-04043: Object sperrorlog does not exist
ERROR ORA-00942: table or view does not exist
Re: How to capture Error Code in sqlplus [message #595391 is a reply to message #595390] Tue, 10 September 2013 16:02 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
1. Your SQL*Plus version? I see your original post it's 9.2. So, not possible in that.
2. If it is 11.1, even if you can't do DESC, then first time you need to once execute "set errorlogging on;"
3. Then try, DESC SPERRORLOG; or simply select * from SPERRORLOG;

[Updated on: Tue, 10 September 2013 16:17]

Report message to a moderator

Re: How to capture Error Code in sqlplus [message #595393 is a reply to message #595391] Tue, 10 September 2013 17:01 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Sep 10 15:00:24 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT * FROM sperrorlog;
SELECT * FROM sperrorlog
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> set errorlogging on; 
SQL> SELECT * FROM sperrorlog;

no rows selected

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

SQL> 


Re: How to capture Error Code in sqlplus [message #595507 is a reply to message #595393] Wed, 11 September 2013 07:49 Go to previous messageGo to next message
LOOKUP_BI
Messages: 5
Registered: September 2013
Junior Member
Tried executing SET ERRORLOGGING ON; came back with insufficient privileges. SPERRORLOG table does not exist in schema "X"... and we also have no privilege to create our own table. All we have is read access to select from existing table. I also have installed client 11g
Re: How to capture Error Code in sqlplus [message #595511 is a reply to message #595507] Wed, 11 September 2013 08:01 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.oracle.com/technetwork/articles/sql/11g-misc-091388.html
Re: How to capture Error Code in sqlplus [message #595524 is a reply to message #595511] Wed, 11 September 2013 08:58 Go to previous messageGo to next message
LOOKUP_BI
Messages: 5
Registered: September 2013
Junior Member
Is there a way instead of reading the error log table to directly spool out an ERROR file instead with error in it ?
Re: How to capture Error Code in sqlplus [message #595527 is a reply to message #595524] Wed, 11 September 2013 09:05 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
LOOKUP_BI wrote on Wed, 11 September 2013 06:58
Is there a way instead of reading the error log table to directly spool out an ERROR file instead with error in it ?


HUH?

SPOOL is a SQL*Plus command; which does not exist within PL/SQL.

What problem are you really trying to solve?

How will you, I, or anyone recognize a correct solution that gets posted here?
Re: How to capture Error Code in sqlplus [message #595532 is a reply to message #595524] Wed, 11 September 2013 09:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68658
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
LOOKUP_BI wrote on Wed, 11 September 2013 15:58
Is there a way instead of reading the error log table to directly spool out an ERROR file instead with error in it ?


Just add at then of your script (before EXIT):
spool error.log
SELECT * FROM sperrorlog;
spool off

Regards
Michel

Re: How to capture Error Code in sqlplus [message #595554 is a reply to message #595524] Wed, 11 September 2013 13:48 Go to previous messageGo to next message
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:28
Is 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:08
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.


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 Go to previous messageGo to next message
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

Re: How to capture Error Code in sqlplus [message #606208 is a reply to message #595594] Tue, 21 January 2014 04:13 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Finally added this to my blog post SQL*Plus error logging New feature release 11.1
Re: How to capture Error Code in sqlplus [message #606214 is a reply to message #606208] Tue, 21 January 2014 04:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68658
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Thank to spam us.

Re: How to capture Error Code in sqlplus [message #606216 is a reply to message #606214] Tue, 21 January 2014 04:32 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
I did not see any forum rule that says I cannot post my own blog's post here. If it is considered spam, please move it an appropriate forum.
Re: How to capture Error Code in sqlplus [message #606218 is a reply to message #606216] Tue, 21 January 2014 04:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68658
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

To promote your blog you have the Marketplace forum.
And yes it is spamming as we don't care you add it to your blog, the most important thing is that the answer is here.

icon7.gif  Re: How to capture Error Code in sqlplus [message #606219 is a reply to message #606218] Tue, 21 January 2014 04:38 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Ok sorry, my bad. Please move the post to marketplace.
Re: How to capture Error Code in sqlplus [message #606224 is a reply to message #606219] Tue, 21 January 2014 05:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68658
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

My advice is that you create a topic in Marketplace forum for your blog and add from time to time posts in it for the new pages you think it would be useful we know.

Re: How to capture Error Code in sqlplus [message #606228 is a reply to message #606224] Tue, 21 January 2014 05:31 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Great idea. Thanks for the suggestion, will do it.
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68658
Registered: March 2007
Location: Nanterre, France, http://...
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

Re: Script to catch ALL Error in sqlplus (SQL, OS and SP2) [message #689641 is a reply to message #689501] Thu, 07 March 2024 08:27 Go to previous messageGo to next message
skyman
Messages: 1
Registered: March 2024
Junior Member
Hi,

very useful tip, i tried it and it worked well with sqlplus and sqldeveloper.
does anybody knows if it works for sessions that connect to the database via jdbc ?

Re: Script to catch ALL Error in sqlplus (SQL, OS and SP2) [message #689643 is a reply to message #689641] Thu, 07 March 2024 08:59 Go to previous message
Michel Cadot
Messages: 68658
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Error logging is a SQL*Plus and SQL Developer (Oracle client programs) feature not one of the underlying connection way.
For instance, you can connect with SQL Developer using JDBC.

Previous Topic: How to Generate Schema Table DDLs
Next Topic: Generating lots of concurrent sessions
Goto Forum:
  


Current Time: Fri May 24 21:53:08 CDT 2024