4 Replies Latest reply: Mar 16, 2011 3:59 AM by Purushoth Branched to a new discussion. RSS

    How to pass a parameter to SQL from shell script loops

    Purushoth
      Hi All,
      I am new to Unix commands. I got one requirement to pass parameter to SQL from shell script. I am able to pass the parameter if there is no loops. But inside the loops i tried to pass the parameter, but it is passing null values. My code as follows.

      for FILENAME in `ls $P_FILE_PREFIX*`; do
      FILE=`ls $FILENAME`
      FCP_LOGIN='apps/apps'
      P_RESP_NM=`sqlplus -s apps/apps<< START
      SET HEADING OFF
      SELECT '"'||responsibility_name||'"'
      FROM fnd_responsibility_vl
      WHERE responsibility_id = (SELECT responsibility_id
      FROM fnd_concurrent_requests
      WHERE ARGUMENT2=*$FILENAME*); EXIT START`

      Here the parameter $FILENAME returns null. Anybody can suggest me what i missed.

      Thanks in advance.
        • 1. Re: How to pass a parameter to SQL from shell script loops
          Nik
          Hi.


          Try add replace
          $FILENAME  by \\$FILENAME
          So script will:
          #!/bin/sh
          for FILENAME in `ls $P_FILE_PREFIX*`; do
           FILE=`ls $FILENAME` 
           FCP_LOGIN='apps/apps'
           P_RESP_NM=`sqlplus -s apps/apps<< START
          SET HEADING OFF
          SELECT '"'||responsibility_name||'"' FROM fnd_responsibility_vl
          WHERE responsibility_id = (SELECT responsibility_id
          FROM fnd_concurrent_requests
          WHERE ARGUMENT2=*\\$FILENAME*); EXIT 
          START`
          Regards.

          PS. Main problem, that forum damage script. So some time it's different what you see and what want say author......

          Edited by: Nik on 16.03.2011 0:00

          Edited by: Nik on 16.03.2011 0:03
          • 2. Re: How to pass a parameter to SQL from shell script loops
            bilger1
            You could split your script.

            myscript.sql
            SET HEADING OFF
            SELECT '"'||responsibility_name||'"'
            FROM fnd_responsibility_vl
            WHERE responsibility_id = (SELECT responsibility_id
            FROM fnd_concurrent_requests
            WHERE ARGUMENT2=*&1*); 
            exit;
            myscript.sh
            for FILENAME in `ls $P_FILE_PREFIX*`; do
            FILE=`ls $FILENAME` 
            FCP_LOGIN='apps/apps'
            P_RESP_NM=`sqlplus -s apps/apps @myscript.sql $FILENAME`
            • 3. Re: How to pass a parameter to SQL from shell script loops
              Dude!
              I cannot test the below, but it might work in your case:
              #!/bin/sh
              filelist=$P_FILE_PREFIX*
              for file in $filelist; do
                 FCP_LOGIN='apps/apps'
                 P_RESP_NM=`sqlplus -s /nolog <<-START
                 connect $FCP_LOGIN
                 set pages 0 feed off
                 SELECT responsibility_name FROM fnd_responsibility_vl
                 WHERE responsibility_id = (SELECT responsibility_id
                 FROM fnd_concurrent_requests
                 WHERE ARGUMENT2 like '%$file%');
              START
              `
              echo $P_RESP_NM
              done
              • 4. Re: How to pass a parameter to SQL from shell script loops
                Purushoth
                Hi All,
                Thanks to every one for your valuable suggestions which relived me from the current issue. Now i am able to pass the parameter when i change it as "WHERE ARGUMENT2 like '%$file%');" .


                Thanks ....

                Regards,
                Purushothaman M