Re: SQLPlus return code inconsistency

arun wrote:
>
> Through a shell script, we invoke an sqlplus session. The idea is to
> spool the output of an SQL query to a flat file. This is done on an
> iterative basis by changing the values in the where clause of the SQL
> query. The problem here is that after a few iterations and successful
> creation of output files, the sqlplus session terminates with an
> sqlerror while processing some nth sql query. After manual
> intervention, when the query is rerun it goes through without any
> problem from the point where it errored out ( the same nth query goes
> through without a fuss). This condition was never witnessed earlier
> and has started happening very recently.
>
> We are running Oracle 8.1.7.2 on sun OS 5.8. The script is provided
> below for easy reference.
>
> "
> sqlplus -s << --eof_espool 2>&1 > "$_jspool_tempfile"
> $SCOPE_SQL_USERID/$SCOPE_SQL_PASSWD@$SCOPE_TNS
> WHENEVER SQLERROR EXIT 1
> set head off
> set pagesize 0
> set timing off
> set autotrace off
> set feedback off
> set termout off
> set trimspool on
> set echo off
> set colsep ""
>
> -- This ensures that extra large SELECT clauses are got into
> the same line
> set linesize 2000
>
>
> spool $_spoolfile
> @$_jspool_tempsqlfile
> spool off
> exit 0
> --eof_espool
> sqlstatus=$?
>
> if [ "$sqlstatus" -ne 0 ]"
>
> "
>
> As explained above, the sqlstatus variable is set to non zero after
> nth query ( where n is some random number). The same sqlstatus
> variable gives a zero value when re-run. This problem is happening
> very consistently of late.

are any oracle (ORA-) messages in the spool file ?

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."