Hello,

I'm trying to write a script that will loop a sql statement through an external list. Basically, the script enters a loop and runs the sql statement for each entry in the list file. Currently, the script will stop at a cursor where I can then manually enter the SQL statment. This is what I have so far:

Port_count.ksh
tradePort.list
Can anyone help?

Thanks,
Chris

You can try something like that :

PRTFLIST='/home/oracle/PortConversion/tradePort.list'
DATADIR='/home/oracle/PortConversion'

# Source the login information
. $HOME/Dear/jobs/DRU/login_DRU.ksh

cd /home/oracle/chris/PortConversion

#get the row count for each portfolio
while read prtf; do
   echo "select count (*) from dear_trade_snapshot where id_prtf_stars = '$prtf'"
done > | \
$ORACLE_HOME/bin/sqlplus -s $DBO_USER/$DBO_PASS  </dev/null

Hi Jean-Pierre,

Thank you for your reply. I have tried what you suggested and still receive the same issue, with a syntax error that | is unexpected.

how about:

#!/bin/ksh

PRTFLIST='/home/oracle/PortConversion/tradePort.list'
DATADIR="$(dirname ${PRTFLIST)"

# Source the login information
. $HOME/Dear/jobs/DRU/login_DRU.ksh

cd /home/oracle/chris/PortConversion

#get the row count for each portfolio
while read prtf; do
   echo "select count (*) from dear_trade_snapshot where id_prtf_stars = '$prtf'"
done < "${PRTFLIST}" | \
$ORACLE_HOME/bin/sqlplus -s $DBO_USER/$DBO_PASS  </dev/null

vgersh99, I gave that a shot, and now i see that it connects and disconnects to sqlplus, however I don't think its running the script as it happens instantaniously (sp?) and nothing is printed between the connect and disconnect. I did remove the -s as well.

I am no sqlplus expert, but.... try removing the '< dev/null'