2 Replies Latest reply: Sep 17, 2012 7:25 PM by EdStevens RSS

    passing variable values from unix to sql file in shell script

    942941
      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
          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
            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.