Accessing PL/SQL OUT variables in Korn Shell Script

bright_future · #1 (**permalink**) 11-01-2007

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.

jim mcnamara · #2 (**permalink**) 11-01-2007

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

Code:

#!/bin/ksh
run_sql()
{
     sqlplus -s user/pswd <<EOF
     DECLARE
             ........
     BEGIN
             .......
     END;
     /
EOF     
}
set -A arr $( run_sql )

bright_future · #3 (**permalink**) 11-02-2007

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!

kiranlalka · #4 (**permalink**) 11-28-2008

Quote:

Originally Posted by bright_future

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!

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

SFNYC · #5 (**permalink**) 12-02-2008

Is this the sort of thing you are looking for?

Code:

$ 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)

More UNIX and Linux Forum Topics You Might Find Helpful
Thread	Thread Starter	Forum	Replies	Last Post
Accessing variables of one shell script in another shell script	rsendhilmani	Shell Programming and Scripting	2	03-17-2009 01:17 AM
How to: Parse text string into variables using Korn shell	shew01	Shell Programming and Scripting	7	05-23-2008 10:01 AM
Accessing Shell Variables in awk or sed	nasersh	Shell Programming and Scripting	3	05-05-2008 07:44 AM
accessing variables declared in another perl script	gurukottur	Shell Programming and Scripting	3	11-09-2006 09:22 PM
Variables Naming in Korn Shell	aldowsary	Shell Programming and Scripting	1	03-19-2006 12:30 PM

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode	Rate This Thread:


	Powered by