Passing Oracle SQL*Plus SQL status to UNIX

During many occasion, we need to develop a UNIX script or Windows script to interact with SQL*Plus. Following is the approach to get SQL*Plus return code.

The limitation of storing the return code is by OS, where the max value is 255 (1 byte), while Oracle return code is the standard ORA- number, which is normally in range of 1 - 19,000. User defined error code is >= 20,000.

Therefore, when user execute a PL/SQL and throw smallest error code 20,000, UNIX will unable to store as 20,000, and becomes 32 after overflow. I believe this is the same behavior in Windows

Now, this is the code to return SQL status from SQL*Plus to OS

Demo 1: Invalid password

SQL> whenever sqlerror exit sql.sqlcode

SQL> whenever sqlerror exit SQL.SQLCODE

SQL> connect wrong_user/password@iccs

ERROR:

ORA-01017: invalid username/password; logon denied

$ echo $?

249

----

Demo 2: TNS name not defined

SQL> whenever sqlerror exit SQL.SQLCODE

SQL> connect wrong_user/password@iccs

ERROR:

ORA-12154: TNS:could not resolve the connect identifier specified

$ echo $?

122

----

Demo 3: Wrong table name, or missing privilege

SQL> whenever sqlerror exit SQL.SQLCODE

SQL> select col1 from report.employee;

select col1 from report.employee

                        *

ERROR at line 1:

ORA-00942: table or view does not exist

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

$ echo $?

174

----

Demo 4: PL/SQL with custom user defined error

Use smallest user define error -20,000 in this example. If no row, then throw error code 20,000, which becomes 32 in UNIX

SQL> whenever sqlerror exit SQL.SQLCODE

SQL> declare

  2      n_row_count number;

begin

  3    4     -- Put in table row check here

  5     select count(*) into n_row_count from report.axxon_study_list;

  6  

   -- Generate error -20000 when table empty. Don't change error code

  7    8     if n_row_count = 0 then

      RAISE_APPLICATION_ERROR (-20000, 'Query returns no row');

  9   10     end if;

 11  end;

/

 12  declare

*

ERROR at line 1:

ORA-20000: Query returns no row

ORA-06512: at line 9

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

$ echo $?

32

Reference: http://www.oracle.com/technology/support/tech/sql_plus/htdocs/sub_var4.html

There are several other build in system variables which can be used.

It is possible to manually check the return code. Following these procedure

1. Don't use "whenever sqlerror"

2. Type: show sqlcode

3. To manually return value to UNIX, type: exit sql.sqlcode

4. To manually return 4 to UNIX, type: exit 4 (instead of using grep and assign value to UNIX return code)

Please use following PayPal donate if my post helped