Re: Generated data file contains error messages !!!

"Laura" <lgm_at_tid.es> wrote in message
news:1127479376.522647.119070_at_g47g2000cwa.googlegroups.com...
> Hi:
> I have a process that generates a datafile whith a query executed from
> sqlplus:
>
> sqlplus .... << eof > my_data_file
> set ...
> set ..
> select ...
> eof
>
> The problem is that when an oracle error happens, the error message is
> included inside de data file.
> How could I do for obtain de error message in a separated file?
>

SQL*Plus is very simple-minded, so there's not direct way to do it (unless I'm missing a relatively new feature)

1. use grep, awk and/or other *NIX utilties to post-process the output
2. (more complex) write a PL/SQL procedure that traps errors and either use UTL_FILE to write to output and errors to different files, or use package variables (likely PL/SQL tables) to buffer errors, interspersing SQL*Plus SPOOL commands with calls to your package that output via DBMS_SQL

++ mcs Received on Fri Sep 23 2005 - 08:06:44 CDT