topic Re: Test return code from sql in script in Operating System - HP-UX

Test return code from sql in script

Scott Williams_5 — Mon, 08 Mar 2004 18:31:39 GMT

I know I've seen this before, but I can't find it.

I want to run an Oracle (9i) SQL script from a ksh script. Can I set a return code within the SQL script that can be tested within the ksh script?

Thanks!

Re: Test return code from sql in script

James A. Donovan — Mon, 08 Mar 2004 18:59:39 GMT

Yes you can...

http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=73788.1

Example PL/SQL: How to Pass Status from PL/SQL Script to Calling Shell Script

Overview

--------

This article contains an example of returning the status from a PL/SQL

program to a shell script.

It is intended for use by PL/SQL and shell script programmers. In

principle, this approach is transferable to all operating systems capable

of receiving a return value from a called program in a script. The example

given is (in part) UNIX specific.

SQL*Plus is commonly run interactively. It is also possible to pass

SQL*Plus the name of a SQL script file and have it run the script file

non-interactively and pass the result status back to the caller. In

this manner, SQL scripts may be executed via SQL*Plus from UNIX shell

scripts and, based upon the return status from SQL*Plus, the shell script

may make decisions about how to proceed.

Sample Code

-----------

ret.sql:

prompt Look in your SQL*Plus User's guide for other datatypes

prompt that EXIT can pass to a calling script via a bind variable

prompt

set serveroutput on

variable ret_val number

declare

dstr varchar2(40);

thedate date;

begin

select sysdate into thedate from dual;

dstr := to_char(thedate, 'MON');

dbms_output.put_line('Month: '||dstr);

if(dstr = 'JUL') then

:ret_val := 100;

else

:ret_val := 200;

end if;

end;

/

exit :ret_val;

The key is the SQL*Plus bind variable.

You must first declare a bind variable. Second, specify the anonymous

PL/SQL block which must set the bind variable to some meaningful value.

Third, run the anonymous PL/SQL block via the '/'. Then exit the SQL

script specifying the bind variable.

The exit command tells SQL*Plus to exit with the value specified. This

value is passed back to the calling UNIX script, where the command

completion status may be examined.

Here is the korn shell script:

#!/bin/ksh

sqlplus scott/tiger @ret

if [ $? -eq 100 ]

then

echo "The month is July"

else

echo "The month is not July"

fi

This script simply calls SQL*Plus using the user name and password of scott

and tiger and tells it to execute the ret.sql PL/SQL script. Subsequently,

the script checks the return status and prints out an appropriate message.

Other platforms may have their own script language into which you must embed

the SQL*Plus call.

Test Platforms:

--------------

The above scripts were tested succesfully on:

SunSolaris with rdbms 8.1.6.2.0

IBM RS/6000 AIX with rdbms 8.1

References

----------

"SQL*Plus User's Guide and Reference, Release 8.1.5", (A66736-01)

Note:2177.1 Pass a parameter with spaces to SQL*Plus

Note:6841.1 Using a PL/SQL Varianle to set the EXIT code of a SQL script

Re: Test return code from sql in script

Steven E. Protter — Mon, 08 Mar 2004 19:00:12 GMT

You should be able to retrieve the return code as follows:
#sqlscript
sqlplus internal << EOF
command
command
command
rc=$?
EOF

return $rc

The stub above is called by another script which will get the return code from here with the following code:

sqlscript
return_code=$?
SEP

Re: Test return code from sql in script

Scott Williams_5 — Mon, 08 Mar 2004 19:21:46 GMT

Thanks guys -- Really appreciate it.

Scott

Re: Test return code from sql in script

Scott Williams_5 — Tue, 09 Mar 2004 11:12:57 GMT

This is what I did. Note that the script below contains a typo in the "FROM" clause: dba_xablespaces, instead of dba_tablespaces.

#!/bin/sh
#typeset -i rc=0

sqlplus /nolog <connect / as sysdba;
whenever sqlerror exit 9
set echo on;
SELECT tablespace_name
FROM sys.dba_xablespaces
ORDER BY 1;
EOD
rc=$?

print "returning: \"$rc\""
return $rc
=====================================
Running this script gives the following output:
SQL> Connected.
SQL> SQL> buffer cleared
SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> 2 3 FROM sys.dba_xablespaces
*
ERROR at line 2:
ORA-00942: table or view does not exist

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
returning: "9"

Thanks again,

Scott