blaal02a
asked on
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!
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!
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.