How to Pass Status from PL/SQL Script to Shell Script

By using following method you can pass specific value or status from pl/sql block to shell script.

Step 1: Define PL/SQL Program and declare 1 global variable
Assign the status to Global variable. Once you assign the status to Variable it will store into System parameter $?

Step 2: Call Pl/sql program in Shell Script and assign $? (Contains Pl/Sql global variable Value) Value to script variable.

=> Create Ret.sql

variable ret_val number
declare
varchar2(40);
the_date date;
begin
select sysdate into the_date from dual;
:= to_char(the_date, 'MON');
dbms_output.put_line('Month: '||);
if(str = 'JUL') then
:ret_val := 10;
else
:ret_val := 20;
end if;
end;
/
exit :ret_val;

When Control comes out from Ret.sql ,the return(:ret_val) value stores in $?

Run the above script At Unix prompt and check the value of $?

echo $?

The value could be 10 or 20

=>Now Create Shell Script

#!/bin/ksh
sqlplus scott/tiger @Ret #Calling Pl/SQl file Here
if [ $? -eq 10 ] #$? contains Pl/sql Return value(:ret_val)
then
echo "The month is July"
else
echo "The month is not July"
fi