This discussion is archived
2 Replies Latest reply: Sep 17, 2012 5:25 PM by EdStevens RSS

passing variable values from unix to sql file in shell script

942941 Newbie
Currently Being Moderated
Hi all,

How to pass variable values from unix to sql in shell script?
for ex :

OSPID= `ps -ef|grep OACore|grep -i app31| awk '{print $2}'`

echo $OSPID
2566 2565

the above two values (2565 ,2566) pass to sql..

Below is my code:

OSPID=`ps -ef|grep OACore| awk '{print $2}'`

echo $OSPID

for PID in `echo $OSPID`
do
sqlplus apps/****** <<EOF
spool 11.log
set feedback off
set head off
set autotrace off
select d.user_name "User Name",b.sid SID,b.serial# "Serial#",c.spid "srvPID",a.SPID "ClPID",b.module,to_char(START_TIME,'DD-MON-YY HH:MM:SS') "STime" from fnd_logins a,
v$session b,v$process c,fnd_user d where b.paddr = c.addr and a.pid=c.pid and a.spid = b.process and d.user_id = a.user_id and (d.user_name = 'USER_NAME' OR 1=1)and a.SPID ='&&PID' >>jvm.txt
# @JVM_CONN_UN.sql $PID
echo $PID
spool off
EOF
done

please suggest me what i missed?

Thanks,
Chandu

Edited by: 939938 on Sep 17, 2012 8:00 AM
  • 1. Re: passing variable values from unix to sql file in shell script
    878977 Newbie
    Currently Being Moderated
    Does ps -fe|grep ..| catch the grep process?
    Did you try ps -fe|grep ...|grep -v grep?

    meya
  • 2. Re: passing variable values from unix to sql file in shell script
    EdStevens Guru
    Currently Being Moderated
    939938 wrote:
    Hi all,

    How to pass variable values from unix to sql in shell script?
    for ex :

    OSPID= `ps -ef|grep OACore|grep -i app31| awk '{print $2}'`

    echo $OSPID
    2566 2565

    the above two values (2565 ,2566) pass to sql..

    Below is my code:

    OSPID=`ps -ef|grep OACore| awk '{print $2}'`

    echo $OSPID

    for PID in `echo $OSPID`
    do
    sqlplus apps/****** <<EOF
    spool 11.log
    set feedback off
    set head off
    set autotrace off
    select d.user_name "User Name",b.sid SID,b.serial# "Serial#",c.spid "srvPID",a.SPID "ClPID",b.module,to_char(START_TIME,'DD-MON-YY HH:MM:SS') "STime" from fnd_logins a,
    v$session b,v$process c,fnd_user d where b.paddr = c.addr and a.pid=c.pid and a.spid = b.process and d.user_id = a.user_id and (d.user_name = 'USER_NAME' OR 1=1)and a.SPID ='&&PID' >>jvm.txt
    # @JVM_CONN_UN.sql $PID
    echo $PID
    spool off
    EOF
    done

    please suggest me what i missed?
    You are missing lots of things, all around missing the difference between what sqlplus does and what the shell processor does.
    and a.SPID ='&&PID'
    This syntax (the & to indicate a parameter value) supposes the value was passed into sqlplus at the sqlplus command line. But you didn't pass it on the sqlplus command line, you are passing it in a series of lines that get processed by the shell. THere are two ways to address this. The simplest is
    and a.SPID =$PID
    also ...
    and (d.user_name = 'USER_NAME' OR 1=1)
    since 1 always equals 1, your OR condition will always evaluate true, regardless of what d.user_name is.

    also ...
    jvm.txt
    This output redirection should be on the sqlplus command line, not individual sql commands passed in the input stream (known as a 'here' document)

    also ....
    # @JVM_CONN_UN.sql $PID
    this is weird. The "#" would be a comment in a shell script. I'm really not sure what happens if it gets passed to sqlplus like that.



    Thanks,
    Chandu

    Edited by: 939938 on Sep 17, 2012 8:00 AM
    Of course, you never said what you were getting, only and indication that it doesn't work. What error messages are you getting? How about execute it at a command prompt and copy the entire session -- command and all response -- and paste back here.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points