Using a variable in sqlplus

Shell Programming and Scripting
1

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

2

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

Jean-Pierre.

3

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.

4

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
5

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.

6

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

7

Just tried it, however same results...

8

ok, I placed some brackets around prtf in the sql statement and my output now looks like this:

But, I'm not seeing the actual row counts. The start and end times are the same as well. I'm not sure if this is a sqlplus issue or a scripting issue? This thing seems so simple too...

9

found the error - I was missing the semicolon to actually end the statement:
echo "select count (*) from dear_trade_snapshot where id_prtf_stars = '$prtf';"

10

Anyone know how I would be able to output the results to a txt file where it would include the variable from the list file next to its result?

11

not tested.....

......
( echo "prtf->[${prtf}]" >> myOutputFile; $ORACLE_HOME/bin/sqlplus -s $DBO_USER/$DBO_PASS) >> myOutputFile
12 
echo "select id_prtf_stars,count (*) from dear_trade_snapshot where id_prtf_stars = '$prtf';"

$ORACLE_HOME/bin/sqlplus -s $DBO_USER/$DBO_PASS  </dev/null >OutputFile

Jean-Pierre.