SQLPlus return code inconsistency

From: arun <persdoc1_at_yahoo.com>
Date: 4 Jul 2002 19:16:32 -0700
Message-ID: <867425c2.0207041816.7dd81c46@posting.google.com>

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. Received on Thu Jul 04 2002 - 21:16:32 CDT