OraFAQ Forum: Client Tools » How to capture Error Code in sqlplus

Home » Other » Client Tools » How to capture Error Code in sqlplus (SQLPLUS)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to capture Error Code in sqlplus [message #595382]

Tue, 10 September 2013 14:03

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

Report message to a moderator

Re: How to capture Error Code in sqlplus [message #595383 is a reply to message #595382]

Tue, 10 September 2013 14:07

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

Report message to a moderator

Re: How to capture Error Code in sqlplus [message #595385 is a reply to message #595383]

Tue, 10 September 2013 14:24

Michel Cadot
Messages: 68510
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

/forum/fa/2115/0/

I learn something new today...

Regards
Michel

Report message to a moderator

Re: How to capture Error Code in sqlplus [message #595388 is a reply to message #595385]

Tue, 10 September 2013 14:29

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

Report message to a moderator

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 #595390 is a reply to message #595389]

Tue, 10 September 2013 15:59

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

Report message to a moderator

Re: How to capture Error Code in sqlplus [message #595391 is a reply to message #595390]

Tue, 10 September 2013 16:02

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

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>

Report message to a moderator

Re: How to capture Error Code in sqlplus [message #595507 is a reply to message #595393]

Wed, 11 September 2013 07:49

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

Report message to a moderator

Re: How to capture Error Code in sqlplus [message #595511 is a reply to message #595507]

Wed, 11 September 2013 08:01

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://www.oracle.com/technetwork/articles/sql/11g-misc-091388.html

Report message to a moderator

Re: How to capture Error Code in sqlplus [message #595524 is a reply to message #595511]

Wed, 11 September 2013 08:58

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 ?

Report message to a moderator

Re: How to capture Error Code in sqlplus [message #595527 is a reply to message #595524]

Wed, 11 September 2013 09:05

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?

Report message to a moderator

Re: How to capture Error Code in sqlplus [message #595532 is a reply to message #595524]

Wed, 11 September 2013 09:25

Michel Cadot
Messages: 68510
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

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: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

Report message to a moderator

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

Re: How to capture Error Code in sqlplus [message #606208 is a reply to message #595594]

Tue, 21 January 2014 04:13

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

Report message to a moderator

Re: How to capture Error Code in sqlplus [message #606214 is a reply to message #606208]

Tue, 21 January 2014 04:26

Michel Cadot
Messages: 68510
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Thank to spam us.

Report message to a moderator

Re: How to capture Error Code in sqlplus [message #606216 is a reply to message #606214]

Tue, 21 January 2014 04:32

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.

Report message to a moderator

Re: How to capture Error Code in sqlplus [message #606218 is a reply to message #606216]

Tue, 21 January 2014 04:35

Michel Cadot
Messages: 68510
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.

Report message to a moderator

Re: How to capture Error Code in sqlplus [message #606219 is a reply to message #606218]

Tue, 21 January 2014 04:38

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.

Report message to a moderator

Re: How to capture Error Code in sqlplus [message #606224 is a reply to message #606219]

Tue, 21 January 2014 05:02

Michel Cadot
Messages: 68510
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.

Report message to a moderator

Re: How to capture Error Code in sqlplus [message #606228 is a reply to message #606224]

Tue, 21 January 2014 05:31

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.

Report message to a moderator

Previous Topic:	Select * from
Next Topic:	TOAD with Oracle and MS SQL Server

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Oct 02 00:37:30 CDT 2023