My Oracle Support Banner

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

Last updated on JANUARY 30, 2022

Applies to:

SQL*Plus - Version and later
PL/SQL - Version and later
Information in this document applies to any platform.


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.


This shell script ( 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. 


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

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