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

How to pass a parameter to SQL from shell script loops

Purushoth Explorer
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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! Guru
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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

Legend

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