This discussion is archived
5 Replies Latest reply: Sep 16, 2011 10:37 AM by Tam_11 RSS

sqlplus from a shell script with  unix variable

Tam_11 Newbie
Currently Being Moderated
I am having some problems calling sqlplus from a shell script....basically I am querying for a path/filename which I will assign to a to a variable called $code. Then use cp to move the file ($code) to a new directory. I think there is something wrong with my sqlplus below. echo $code doesn't even work. I thought that $code was a variable that I could display, but rather it seems like the script tries to re-execute the SQL cmd each time.

Any help would be greatly appreciated...condensed version of code is below. Thank you!

for file in $FILES
do
code=""
code=`sqlplus -s $USER_PWD<< EOF
set heading off;
set pagesize 0;
select r.outfile_name OUTFILE
from some tables
where file_name=$file;
EOF`
echo $code
cp $code $newdir
  • 1. Re: sqlplus from a shell script with  unix variable
    riedelme Expert
    Currently Being Moderated
    Are you reading the filename from the database? If so try something like this (untested) - the idea is to capture the value, then use RETURN to send it back to the shell

    Shouldn't there be a "*done*" ending the do loop somewhere???
     for file in $FILES
     do
     code=""
     sqlplus -s $USER_PWD<< EOF
    set heading off;
    --variable to hold read code
    variable v_code_c varchar2(255);
     set pagesize 0;
    begin
      select r.outfile_name OUTFILE 
        into :v_code_c
       from some tables
       where file_name=$file
        --avoid too many rows error
         and rownum = 1;
    end;
    /
      return :v_code_c;
     EOF
    #capture the return code from the sql*plus session
    code=$?;
     echo $code
     cp $code $newdir
    It might (or not, you decide) be even easier to read the value(s), spool it to a file, then loop through the resulting output file with your done loop
  • 2. Re: sqlplus from a shell script with  unix variable
    EBSDBA Expert
    Currently Being Moderated
    Hi,
    I tried this

    The file names are 1,2,3. The file_list contains 3 records with file_path as 1 2 3.
    export newdir=/home/oraclone/test
    for i in 1 2 3
    do
    code=""
    code=`sqlplus -s / as sysdba <<EOF
    set heading off;
    select file_path from file_list where file_path=$i;
    EOF`
    ocode=$?;
    echo $ocode
    cp -v $code $newdir
    done
    It worked for me.

    Thanks
  • 3. Re: sqlplus from a shell script with  unix variable
    Tam_11 Newbie
    Currently Being Moderated
    Thanks for replying. I eventually got this going....a combination of syntax errors and problems with the user/password for sqlplus was causing me a problem (the first time I have ever written a shell script). Now that I have the value being returned to $code I have run into another problem.

    How do I capture a situation where my sql query returns no rows? I echoed value of $code in this situation and am getting 'no rows selected', but when I test for this it doesn't work.

    if "no rows selected" = "$code"; then
    echo ".....file not found."
    fi

    Any suggestions would be appreciated...thanks.
  • 4. Re: sqlplus from a shell script with  unix variable
    888591 Newbie
    Currently Being Moderated
    Hi

    I hope below code will help but set feedback off so that it will not any info if no rows selected
    echo ${CODE}
    if [ -z "${CODE}" ]
    then
    echo "No rows returned"
    else
    echo "Returned"
    fi
    Regards
    CA
    uninstall security tool
    window security essentials
    windows startup config
    award bios update
  • 5. Re: sqlplus from a shell script with  unix variable
    Tam_11 Newbie
    Currently Being Moderated
    Thanks very much...feedback setting was causing the problem.

    Tam.

Legend

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