UNIX for Dummies Questions & Answers

add the below before the sql
set term off
and see whether you are getting the required functionality.

still the same error.
set term off didn;t help.

Actually you are redirecting both the standard output and standard error stream to sqlerror.log. If you want to redirect only the error stream, try this.
sqlplus -S $USER/$PASS@$DB << EOF 1>/dev/null 2>sqlerror.log
This redirects the standard output to a null file.

If you want the output, just remove the 1>/dev/null.

sorry..it's fail. The script still will would not redirect the error "sqlerror.log" .
when have error, the error log is capture to "bulk_main.dat "

------------------------------------------------
sqlplus -S $USER/$PASS@$DB << EOF 1>bulk_main.dat 2>sqlerror.log
set pages 0
set term off
SET HEAD off FEEDBACK off VERIFY off ECHO off SERVEROUTPUT off;
select filename , module from MXS_BULK_MAIN ;
quit;
EOF

The first thing is that you are spooling the output of the query to the file bulk_main.dat. So, even if the query fails, the resulting output of the failure would go into the spooled file.

If you have to check for errors, the first form given by you
sqlplus -S $USER/$PASS@$DB << EOF > sqlerror.log 2>&1

will work fine. After the execution, check for errors in the log file and then decide to use the output file. IF you find errors in the logfile, donot continue and come out.

grep -i ora- sqlerror.log>/dev/null && (echo "Ora Error";exit -1)
grep -i sp2 sqlerror.log >/dev/null && (echo "SQL Error";exit -1)

I hope it is clear.

I think I know how to do already.