Accessing PL/SQL OUT variables in Korn Shell Script

UNIX for Advanced & Expert Users
1

Hello All,

I was just wondering if there is any direct way to access PL/SQL OUT variables from Korn Shell Script.

I could already figure out how to return a single value back from PL/SQL to Shell Script (using bind variable).
But, what if we want to return multiple values?

One option I found in some previous posts, is to print all the OUT variables (using DBMS_OUTPUT.PUT_LINE feature) to some log file and then use egrep, sed or awk to find those variables.
But, that's an indirect way (a work-around) I would say.
Isn't there any direct solution available with Unix?

Please help me.

Quick responses would be appreciated.

2

Ways to get variable values out of PL/SQL:

  1. call dbms_output, search log file or use grep in the script to find values
  2. call utl_file, write values -> read values from file in script
  3. make pl/sql call a subprocess, then set into envrionment variable probably an array
#!/bin/ksh
run_sql()
{
     sqlplus -s user/pswd <<EOF
     DECLARE
             ........
     BEGIN
             .......
     END;
     /
EOF     
}
set -A arr $( run_sql )
3

Many thanks for your quick reply.

However, the solutions do not solve the problem I am facing.

First 2 solutions are indirect ways, like I mentioned in my original post.

The 3rd solution is good infact, but I forgot to mention in my original post that my PL/SQL block prints many messages using dbms_output feature.
Hence, if I store everything in "arr" variable you suggested, than I will have to use grep again, to find out the variables I need.

If there is no other direct way to output PL/SQL variables to Shell script, then I may go with the 3rd solution (as the last choice).

Anyway, thanks again!

4

Dude,

have you got any better way.... if yes can share the code...

even if you used the above one can you share the code

5

Is this the sort of thing you are looking for?

$ cat test_get_oracle_vars.ksh
#!/bin/ksh
run_sql()
{
  $ORACLE_HOME/bin/sqlplus -S <<EOF
  user/psw
  SET PAGESIZE 0;
  SET FEEDBACK OFF;
  SET SERVEROUT ON;
  VAR major NUMBER;
  VAR minor NUMBER;
  EXEC DBMS_PROFILER.GET_VERSION (:major, :minor);
  PRINT major;
  PRINT minor;
EXIT;
EOF
}


set -A ARRAY $(run_sql)
ARRAYCOUNT=${#ARRAY[*]}
ARRAYIDX=0

while (( $ARRAYIDX < $ARRAYCOUNT ))
do
 echo  "ARRAY[$ARRAYIDX]=(${ARRAY[ARRAYIDX]})"
 ARRAYIDX=$(($ARRAYIDX+1))
done

exit 0

$ ./test_get_oracle_vars.ksh
ARRAY[0]=(2)
ARRAY[1]=(0)