Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1179
  • Last Modified:

SQL*Plus Error & Message Handling

I'm exporting data from an SQL statement to a flat file with sqlplus and spool ...
How do I:
a) Separate error message output from the data output
b) Continue outputting data after a conversion error is reported
For example, the SELECT might include conversions like:
TO_CHAR(TO_DATE(column, 'YYYYMMDD'), 'MM/DD/YYYY')
and occasionally, the database may have an invalid MM value.
Looking for generic ways of handling messages, rather than specific rewrites of this particular conversion to avoid errors.
Thanks!
0
blaal02a
Asked:
blaal02a
  • 3
1 Solution
 
slightwv (䄆 Netminder) Commented:
Using sqlplus, no.  It is a pretty simple tool.

You can create a pl/sql block that uses dbms_output ( or write to files directly using utl_file) that will allow for some better error handling.

If dates are your only issue and you don't care if the output is 'accurate', use substr calls to change the date format around.
0
 
slightwv (䄆 Netminder) Commented:
I shouldn't say 'no'.  I should say: I don't know of a way in sqlplus.  Every time I say 'no' another Expert proves me wrong.
0
 
OP_ZaharinCommented:
- er... you were right slightwv, on someone will proves you wrong ;)

blaal02a,
- there is a way of handling error in sql plus, but not much you can do with it. as per the syntax, if there is error, it will simply exit:
- for handling OS error
WHENEVER OSERROR  EXIT OSCODE
- for handling SQL error
WHENEVER SQLERROR EXIT SQL.SQLCODE

- further here:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch12052.htm
http://www.toadworld.com/Kx/Controls/KxPopup.aspx?KxRes=SQL482AB6



- here's an interesting new feature on 11g sql plus. error will be captured and the script will continue to run. it will not stop on error. you can install 11g client to get the 11g sql plus and use it on 9i and 10g database:

SQL> set ERRORLOGGING on
SQL> run your script here
SQL> set ERRORLOGGING off

-  errors will be stored in default error log table SPERRORLOG:
SQL> select timestamp,script,statement,message from sperrorlog;

- further here:
http://download.oracle.com/docs/cd/B28359_01/server.111/b31189/ch12040.htm#SQPUG160
0
 
slightwv (䄆 Netminder) Commented:
I'll give you the 11g syntax until I can read up on it but the 10g example doesn't meet the requirement as it will not continue.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now