sql plus error - usage message

From: Pat <miapjp_at_chartermi.net>
Date: 1 Oct 2003 06:33:58 -0700
Message-ID: <f7a99267.0310010533.4f2800d6@posting.google.com>

Hi,

this is driving me nuts and I hope someone can help me out.

I have a UNIX script that calls sqlplus to update a table, it goes something like this:
if [ $RETURN_CODE -eq 0 ]
then

   echo "pgm executable ended, ready to update table" sqlplus $USER_ID << EOI

   whenever sqlerror exit failure rollback; -- 006 added rollback    set echo on;
   set termout on;

   UPDATE table
   SET post_date = trunc(sysdate)
   WHERE post_date is null;

   exit success commit;
EOI I'm trying to test this for an error on the update statement. When all goes well and the update succeeds, I get this:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.3.0 - Production

SQL> SQL> SQL> SQL> SQL> 2 3
10 rows updated.

SQL> SQL> Disconnected from Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production

Trying to force an error, I put in a non-existent table and I get this:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.3.0 - Production

SQL> SQL> SQL> SQL> SQL>   2    3     UPDATE non_existent_table
          *

ERROR at line 1:
ORA-00942: table or view does not exist

Usage: { EXIT | QUIT }	[ SUCCESS | FAILURE | WARNING | n |
       <variable> | :<bindvariable> ]  [ COMMIT | ROLLBACK ]

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.3.0 - Production

The ORA-00942 error is what I want, but why do I get the usage error? I checked and double-checked the syntax on both the whenever sqlerror and the exit statement and don't see anything wrong (not that that means there isn't something wrong with it!)

I've actually taken out everything in both those statements but "exit" and I still get the usage message. I also tried to update the field with the wrong datatype to see if a non-existent table was a syntax error that sqlplus checked before execution and maybe gave the error because it never executed anything and therefore didn't expect the exit statement (a thought our DBA had). Same thing.

This does "work" as it's supposed to, but it still isn't quite right.....you know what I mean??

Thanks in advance!
Pat Received on Wed Oct 01 2003 - 08:33:58 CDT