Example PL/SQL: How to Pass Status from PL/SQL Script to Calling Shell Script (Doc ID 73788.1)

Purpose

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 the shell script may make decisions about how to proceed.

Scope

This shell script (RunMe.sh) used in this example connects to the database using SQL*Plus and executes an associated SQL script (ret.sql). The SQL script first defines a bind variable (:ret_val) then executes an anonymous PL/SQL block. The anonymous PL/SQL block obtains the SYSDATE from the database. The month is extracted then the IF condition within the anonymous PL/SQL block checks if the month is July (JUL).

If the associated month is July (JUL) then the bind variable is set to 100. If it is some other month then the bind variable is set to 200. 

When the anonymous block completes the last line of the SQL script calls 'exit' and returns the bind variable with its associated value to the calling shell script. 

Details

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.