Re: Sqlplus and Unix : getting a non zero return code

On 20 Aug 2001, stemp1ar_at_yahoo.com wrote:

> I need to call in the experts. I would like to call sqlplus,
> pass sql as a parm, run the sql and if there is a non zero
> return code exit the script. The test script below does not
> seem to send the output to correct output files.
>
> Any help would be great...TIA
>
> check_error() {
> if [[ -a errfile ]]; then
> err_count=$(grep -c -e ^[^$] errfile)
> else
> err_count=0
> fi
> if (( err_count > 0 )); then
> echo "\nErrors have been encountered. Please refer
> to
> errfile"
> fi
>}
> sqlplus -s > test.out 1>errfile 2>logfile <<EOF

I don't use this syntax. I use
sqlplus username/pass << EOF > test.out
Then, I grep test.out for "ORA-" and other pertinent strings for errors. I don't believe the errfile or logfile will get written to by the strings you expect. They will get written to by 1 and 2 for sqlplus, not 1 & 2 for the process sqlplus was executing.

> username/pass
> whenever sqlerror exit failure
> set serveroutput on
> spool outfile
> select count(*) from table_name;
> spool off;
> exit;
> EOF
>
> RC=$?

This will always be successful unless sqlplus itself has an issue. If SQL errors out, sqlplus will still have been successful.

> if [[ $RC = 0 ]]; then
> echo "Inside zero rc=$RC"
> else
> echo "Inside non-zero RC=$RC"
> exit -1
> fi
>
> check_error

Okay, so now you run your function.

I would do something like

check_error (){

error_cnt=`grep -c "ORA-" errorfile`
if [ $error_cnt -ne 0 ]
then ....
fi
}

Another thing I have used quite a bit was (taking place of your spooling of the sql text)

cat << EOF > sqltext.out
dynamic sql to be executed
;
EOF sqlplus username/pass << EOF > sql.out
`cat sqltext.out`
EOF if error
then
  echo "there was an error executing"
  cat sql.out
  echo "The error file contained"
  cat errorfile
fi

-- 
Galen Boyer
It seems to me, I remember every single thing I know.