SQL*Plus Error & Message Handling

Posted on 2011-04-22
Last Modified: 2013-11-11
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:
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.
Question by:blaal02a
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    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.
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    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.
    LVL 23

    Accepted Solution

    - er... you were right slightwv, on someone will proves you wrong ;)

    - 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
    - for handling SQL error

    - further here:

    - 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> run your script here

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

    - further here:
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Suggested Solutions

    Title # Comments Views Activity
    Oracle View 13 33
    just a small ORACLE update query 27 65
    substr - oracle 5 49
    Space Delimited Sql File 4 58
    Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
    Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
    This video shows how to recover a database from a user managed backup
    This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now