Re: sqlplus return status (Was: checking sqldba return status)

In <11132_at_inews.intel.com> kortikar_at_mipos2.intel.com (Aniruddha Kortikar):
> 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.
>
> for example on my UNIX machine following will also return 0 !
>
> echo "selt * from dual" | sqlplus -s scott/tiger >/dev/null
> echo $? will return 0
> and nothing is displayed on tty

Ignoring the missing 'whenever' syntax from your example, I'm guessing that syntax errors don't exit with failure:

     $ sqlplus scott/tiger <<ENDIT > /dev/null
     > whenever sqlerror exit failure
     > selt * from dual;
     > exit success
     > ENDIT
     $ echo $?
     0
     

If the statement passes the syntax check but fails to execute, it will return a success 0 or failure 1 (scott doesn't have dba privileges so the following fails with ORA-942):

     $ sqlplus scott/tiger <<ENDIT > /dev/null
     > whenever sqlerror exit failure
     > select * from dba_users;
     > exit success
     > ENDIT
     $ echo $?
     1

The moral is to debug syntax before installing scripts.

As for stderr vs. stdout, for a while some Oracle frontends (such as exp) output a mixture of stderr and stdout. I make it a habit to output both to a log just in case:

     ( sqlplus -s userid/password _at_sql_script ) >> log_file 2>&1
     if test $? -ne 0
     then
	     ... handle the error ...
     fi

+-----------------------------------------------------------------------+
| Jean Anderson, DBA                       email:  jean_at_seismo.css.gov  |
| SAIC Open Systems Division, MS A2-F         or:  jean_at_esosun.css.gov  |
| 10210 Campus Point Drive                 phone:  (619)458-2727        |
| San Diego, CA  92121                       fax:  (619)458-4993        |
+-----------------------------------------------------------------------+