Hi wrote a unix program that has a parameter startdate and I want to pass the startdate parameter to my sql statement see below:

#!/bin/ksh
homedir=/export/home/pcard
startdate=2
export homedir startdate
EMAIL=blahblah@blah.com
CHARGE_CODE=$homedir/scripts/CHARGE_CODE.txt

unalias rm
cd $homedir/scripts
sqlplus /nolog @$homedir/sql/pcardacl.sql
if [ -s $CHARGE_CODE ]
then
echo "Sucess"
mailx -s "test" $EMAIL < CHARGE_CODE.txt
# rm $CHARGE_CODE
else
echo "Fail"
echo "fail" | mailx -s "fail" blahblah@blah.com
fi

The script above then calls this sql pcardacl.sql script:

@$homedir/sql/connection.sql
Set echo off
Set heading off
Set line 280
SPOOL $homedir/scripts/CHARGE_CODE.txt
select $startdate from dual;
SPOOL OFF
exit

When I run this is does not work. It's not recognizing the $startdate unix variable. I tried using &startdate instead but it asks me to type in something and I don't want that. Sorry I am not too saavy at unix programming and any help would be much appreciated!

Thanks!!!

I don't know sqlplus but try this

sqlplus /nolog <<EOF
@$homedir/sql/connection.sql
Set echo off
Set heading off
Set line 280
SPOOL $homedir/scripts/CHARGE_CODE.txt
select $startdate from dual;
SPOOL OFF
exit
EOF

Thank you so much. It worked!