This content has been marked as final. Show 5 replies
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???
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
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
I tried this
The file names are 1,2,3. The file_list contains 3 records with file_path as 1 2 3.
It worked for me.
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
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."
Any suggestions would be appreciated...thanks.