Re: sql plus error - usage message

Pat wrote:

>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
>
>

Over the years I've seen a lot of people trying to kludge things together using shell scripting, etc. and my response is that it is the wrong tool for the job.

Consider how easy what you are trying to do would be using DBMS_JOB. Full error handling would be
a breeze as well as only needing to deal with a single language making long-term maintenance easier. Consider
using your shell script to execute a stored procedure: Same thing. And done properly it becomes prortable
between host shells and operating systems something code such as your can never be.

I know Korn and Perl are nice tools with great capabilities. But updating records that could be better done in SQL and/or PL/SQL is just not one of those things. (let the religious war begin).

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)