Hello
I will be grateful if someone could please help me to resolve this problem with retrieving dates saved to a parameter file. The code is supplied below. What am trying to do is to loop round sets of dates saved from a query to a parameter file. There are two issues with this script. The first is that the first row created by this query is blank which I don't know why and this could be my problem. There are 3 rows instead of 2. So this is the output of the parameter file;
row1
row2 29-MAY-20
row3 30-MAY-20
There should be just the 2 rows with the dates. I have confirmed this on sql developer
The second problem is that I think my syntax for the cut command to retrieve the dates within the loop is wrong. All I want to do is to pass individual dates as (VRDATE) to the SQL script. At the moment, it is not passing the dates as the parameter.
Thanks for your support.
#-----------------------------------------------------------------
DD_SYSOUT1=$WORKFILE/sysout/pgm4605
export DD_SYSOUT1
#Variables to run job
VSHELL="pgm4605"
echo "`date` ${VSHELL} starting..."
VSQLEX="pgm4605"
echo "Start ${VSQLEX}.sql"
#
sqlplus -s / << EOF >> $DD_SYSOUT1
set serverout on size 1000000
set pause off
set feedback off
set verify off
set heading off
set lines 132
select distinct trunc(xdate1)
from table1
order by trunc(xdate1);
exit;
EOF
if test -s $DD_SYSOUT1
then
vcount=0
cat $DD_SYSOUT1 | while read line
do
#VRDATE =`echo $line | cut -f1 -d`
#VRDATE =`echo $line | cut -c1-9`
VRDATE =`echo $line`
vcount=`$vcount + 1`
vseq=$1"_"$vcount
VOUTFILE="/users/$2/sysout/file1_${vcount}.csv"
sqlplus -s / @$WORKDIR/sqlex/${VSQLEX}.sql $VRDATE
sleep 1
done
fi