PL/SQL Procedure OUT parameter in Shell Script.

This is based on one o my posting in OTN. Many times you call PL/SQL procedure from a shell script. This is an example of handling OUT parameter in shell script.
Fist, we are creating a procedure with OUT paramater for testing.

SQL> /* My database version */
SQL> SELECT * FROM v$version;
 
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod
PL/SQL Release 10.2.0.2.0 - Production
CORE    10.2.0.2.0      Production
TNS for Solaris: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production
 
SQL> /* Creating a procedure for testing of OUT parameter */
SQL> CREATE OR REPLACE PROCEDURE test_out_param(p1 OUT VARCHAR2,p2 OUT VARCHAR2, p3 OUT NUMBER) IS
BEGIN
p1:='First Out Param';
p2:='Second Out Param';
p3:=99;
END test_out_param;
  2    3    4    5    6    7  / 
 
Procedure created.
 
SQL>

Now the shell script and the testing of that script.

$ uname -a
SunOS saubhik 5.10 Generic_142910-17 i86pc i386 i86pc
$ cat test_script
#!/bin/ksh
run_sql() {  sqlplus -s scott/tiger << EOF
SET FEEDBACK OFF;
var v1 VARCHAR2(500);
var v2 VARCHAR2(500);
var v3 NUMBER;
exec test_out_param(:v1,:v2,:v3);
print :v1 :v2 :v3
exit
EOF
}
 
set -A myarray $(run_sql)
# Now I am printing the array. I know there are 13 arguments.
# 1-> V1, 2->Space/Return, 3-> First, 4-> Out etc...
# You can do it more dynamically also (if required).
# Yu can also awk/grep/sed etc the OUT param name (for example V1)
# to know the values.
 
for i in 0 1 2 3 4 5 6 7 8 9 10 11 12 13
do
    print ${myarray[$i]}
done
# Decision making.....
 
if [ ${myarray[12]}  == "99" ]
then
echo Got it!
fi
 
$ ./test_script
V1
 
First
Out
Param
V2
 
Second
Out
Param
V3
 
99
 
Got it!

$