Home » SQL & PL/SQL » 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: 2544
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: 59991
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
./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: 2544
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: 2544
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: 23146
Registered: January 2009
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: 23146
Registered: January 2009
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: 23146
Registered: January 2009
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: 59991
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: 2544
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: 2544
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: 59991
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: 2544
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: 59991
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: 2544
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: 59991
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 message
Lalit Kumar B
Messages: 2544
Registered: May 2013
Location: World Wide on the Web
Senior Member
Great idea. Thanks for the suggestion, will do it.
Previous Topic: Select * from
Next Topic: TOAD with Oracle and MS SQL Server
Goto Forum:
  


Current Time: Thu Dec 18 20:04:43 CST 2014

Total time taken to generate the page: 0.14495 seconds