SQLPLUS within Unix script

[tusker]

The following does not work. I am trying to substitue the FILE_NAME into my sql and then running it. The sql does not seem to translate the $FILE_NAME

for FILE_NAME in `ls -1`
do
my_count=$(sqlplus -s $DB_USER/$DB_PASSWORD <<END
set feed off
set pages 0
set head off

select count(*) from pload_audit
where filename='$FILE_NAME';
commit;
exit;

END)

echo &quot;$my_count&quot;

 

[bigoldbulldog]

The problems I see are

END)

which should be

END
)

because the here statement requires a line with just END on it to terminate.

The other is the use of '$FILE_NAME' is interpreted as &quot;your-file&quot; where double quotes are disallowed. Try making another variable to concatenate single quotes to the filename. ie. SQ_FILE_NAME=&quot;'${FILE_NAME}'&quot;
You could also skip the echo &quot;$my_count&quot; because the database should do it for you. You may want to deal with the leading spaces though.

So your script will be
for FILE_NAME in `ls -1`
do
SQ_FILE_NAME=&quot;'${FILE_NAME}'&quot;
my_count=$(sqlplus -s $DB_USER/$DB_PASSWORD <<END
set feed off
set pages 0
set head off

select count(*) from pload_audit
where filename=$SQ_FILE_NAME;
commit;
exit;

END
)

echo &quot;$my_count&quot;

Cheers,
ND