sqlplus return status (Was: checking sqldba return status)

In article <11132_at_inews.intel.com>, kortikar_at_mipos2.intel.com (Aniruddha Kortikar) writes:
> Oracle does not seem to like the idea of anything other than success!
> As if this is alone is not sufficient sqlplus does not even give the error
> message on stderr. Everything comes on stdout.
>
> Oracle does not seem to consider concepts such as exit status and stderr
> important. (If I remember it correctlt these problems are there since 5.x)

Well, with SQL*Plus you can say:
  whenever sqlerror exit 13
  insert into blah blah;
  update jakatijak jakatijak;
  commit;
  exit 0

This will exit the sqlplus session whenever an sql error happens and returns the exit code 13 to the caller process. Whether the caller can really use this exit code depends on the operating system, but it works at least on UNIX. I believe this feature was intoduced in sqlplus version 3.

But, beware that the exit command will COMMIT all changes in this transaction. So if the insert succeeds and update fails, sqlplus will commit the insert when exiting! So you cannot have real all-or-nothing transactions with this crippled "whenever sqlerror exit". This annoying fact forced me to use PL/SQL and Pro*C for critical transactions, where you can reliably rollback a failing transaction.

Oracle has noticed this flaw and newest versions of sqlplus have an alternate syntax for whenever, which allows you to rollback the transaction when exiting. The manual may not necessarily have been updated yet. Could somebody at Oracle tell us which version has the enhanced syntax on whenever?

I can't recall what whenever sqlerror does when you do not have an actual *SQL* error but some SQL*Plus syntax error - say you wrote SLECT instead of SELECT - look at The Fine Manual!

--
Juhani Jaakola, jaakola_at_cc.helsinki.fi