CeDeT: OIF (Oracle is funny (that way)) #9

WHENEVER OSERROR EXIT what?

Can or can't you get a meaningful exit code out of using WHENEVER OSERROR EXIT ... in SQL*Plus?

The manual

Source: SQL*Plus User's Guide and Reference, Release 9.2, Part Number A90842-01, WHENEVER OSERROR command

WHENEVER OSERROR
{EXIT [SUCCESS|FAILURE|n|variable|:BindVariable] [COMMIT|ROLLBACK]
|CONTINUE [COMMIT|ROLLBACK|NONE]}

Performs the specified action (exits SQL*Plus by default) if an operating system error occurs (such as a file writing error).

...

EXIT [SUCCESS|FAILURE|n|variable|:BindVariable]

Directs SQL*Plus to exit as soon as an operating system error is detected. You can also specify that SQL*Plus return a success or failure code, the operating system failure code, or a number or variable of your choice. See EXIT in this chapter for details.

The question

So the question boils down to: How do you return "the operating system failure code"?

SQL*Plus

C:\OraHome1\bin>ver

Microsoft Windows XP [Version 5.1.2600]

C:\OraHome1\bin>sqlplus scott/tiger

SQL*Plus: Release 9.2.0.7.0 - Production on Tir Jan 17 21:58:04 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Forbindelse er oprettet til :
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.7.0 - Production

SQL> WHENEVER OSERROR;
Format: WHENEVER OSERROR
     { CONTINUE  [ COMMIT | ROLLBACK | NONE ]  |  EXIT [ SUCCESS | FAILURE |
         WARNING | n | <variabel> | :<tilknytningsvariabel> | OSCODE ]
             [ COMMIT | ROLLBACK ] }

A possible answer

So SQL*Plus seems to suggest that you can use OSCODE to return "the operating system failure code".

WHENEVER OSERROR EXIT

SQL> WHENEVER OSERROR EXIT OSCODE;
SQL> SPOOL invalid_dir/output.lst;
SP2-0332: Spoolfil kan ikke oprettes.
O/S-meddelelse: No such file or directory
Format: { EXIT | QUIT }  [ SUCCESS | FAILURE | WARNING | n |
       <variabel> | :<tilknytningsvariabel> ]  [ COMMIT | ROLLBACK ]
Forbindelse er afbrudt til Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production

What?

So SQL*Plus displays an operating system message (O/S-meddelelse) but it also seems to complain about the Format! So is the return code actually "the operating system failure code" or what?

A test

C:\OraHome1\bin>type x.bat
@echo off
sqlplus -silent scott/tiger @x.sql
echo ERRORLEVEL is %ERRORLEVEL%

C:\OraHome1\bin>type x.sql
WHENEVER OSERROR EXIT OSCODE;
SPOOL invalid_dir/output.lst;
SELECT 'X' FROM DUAL;
SPOOL OFF;
EXIT 0;

C:\OraHome1\bin>x
SP2-0332: Spoolfil kan ikke oprettes.
O/S-meddelelse: No such file or directory
Format: { EXIT | QUIT }  [ SUCCESS | FAILURE | WARNING | n |
       <variabel> | :<tilknytningsvariabel> ]  [ COMMIT | ROLLBACK ]
ERRORLEVEL is 3

No conclusion

So is 3 "the operating system failure code"?