passing values from sql to shell script

Shell Programming and Scripting
1

Hi guyz,

Posting a thread after a long time.

I want to pass two variables to unix shell script from sql script.

Note: I am calling sql script from unix script. sql script has 2 variables one is the return code for status of program run and second one email flag. I don't know how to capture email flag in unix.

2

Hey dude, this question was asked before.. kindly check the replies for the same.
Anyways posting the reply.....

RETVAL=`sqlplus -s user/pwd@host <<EOF
SET SERVEROUTPUT ON SIZE 100000
Declare

OUT_MSG VARCHAR2(200);

Begin

select name into OUT_MSG from table_success;

dbms_output.put_line ('KeepThis '||nvl(OUT_MSG,''));
End;
/
SET SERVEROUTPUT OFF
EXIT;
EOF`

X=`echo $RETVAL | grep KeepThis | awk '{print $2}'`
Y=`echo $RETVAL | grep KeepThis | awk '{print $3}'`
Z=`echo $RETVAL | grep KeepThis | awk '{print $4}'`

echo "The Query output is: "

echo "Query OUT_MSG 1= $X "
echo "Query OUT_MSG 2= $Y "
echo "Query OUT_MSG 3= $Z "

If you have more outputs from query, print those results in $5, $6,......

3

thank you so much!

4

Hi!

here is the sql statement within shell script

---------
---------

sqlplus system/manager@SID @myreport

myreport passes 3 values so how should i store it in a varibale.
i know i have to use array but i dont know how to use it.

Regards,

Sachin