5 Replies Latest reply: Sep 16, 2011 12:37 PM by Tam_11 RSS

    sqlplus from a shell script with  unix variable

    Tam_11
      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
          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
            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
              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
                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
                  Thanks very much...feedback setting was causing the problem.

                  Tam.