Shell Programming and Scripting

sql1="select date_type, offset, mail_list, reminder_offset from osr_cbb_offset_dates"
$JAVA icoredp.tools.oracleUnload -c ${CSORDHOME}/conf -s "$sql1" >> ${output}
NO_OF_REC=`wc -l ${output}| cut -c1-9`
echo "Records in first sql $NO_OF_REC"
cut -f1 $output| while read LINE1
i=0;
do
echo "in first loop"
DATE_TYPE=`echo $LINE1|cut -f1 -d "|"`
OFFSET=`echo $LINE1|cut -f2 -d "|"`
MAIL_LIST=`echo $LINE1|cut -f3 -d "|"`
REMINDER_OFFSET=`echo $LINE1|cut -f4 -d "|"`





sql2="Select unique P.project_id, project_title, location_clli, prem_city, prem_state, OSR1.ptnii_equip_name, OSR2.ptnii_equip_name, Planner.name, Planner.phone, PM.name, PM.phone, date_type, mail_list,P.$DATE_TYPE
From voip_project P, voip_project_det D, equipment OSR1, equipment OSR2, planners_and_pms Planner, planners_and_pms PM, osr_cbb_offset_dates, ip_complex
Where P.project_id = D.project_id
And ip_complex.clli = P.location_clli
And D.equip_id_1 = OSR1.equip_id
And D.equip_id_2 = OSR2.equip_id
And P.planner_id = Planner.plpm_id
And P.pm_id = PM.plpm_id
And date_type = '$DATE_TYPE'
and P.STATUS != 'WITHDRAWN'
and to_char(P.$DATE_TYPE - nvl('0',0),'MM/DD/YYYY')=to_char(sysdate,'MM/DD/YYYY')"

$JAVA icoredp.tools.oracleUnload -c ${CSORDHOME}/conf -d "," -s "$sql2" > ${DISCORDFILE}
NO_OF_RECORDS=`wc -l ${DISCORDFILE}| cut -c1-9`
echo "Records in 2nd sql $NO_OF_RECORDS"
if [ $NO_OF_RECORDS -eq 0 ]; then
echo "No Records found for Service"

else
cut -f1 $DISCORDFILE| while read LINE1

do
echo "in 2nd loop"
PROJECT_ID=`echo $LINE1|cut -f1 -d ","`
PROJECT_TITLE=`echo $LINE1|cut -f2 -d ","`
PREM_CITY=`echo $LINE1|cut -f4 -d ","`
PREM_STATE=`echo $LINE1|cut -f5 -d ","`
OSR1=`echo $LINE1|cut -f6 -d ","`
OSR2=`echo $LINE1|cut -f7 -d ","`
PLANNER_NAME=`echo $LINE1|cut -f8 -d ","`
PLANNER_PHONE=`echo $LINE1|cut -f9 -d ","`
PM_NAME=`echo $LINE1|cut -f10 -d ","`
PM_PHONE=`echo $LINE1|cut -f11 -d ","`
DATE_TYPE=`echo $LINE1|cut -f12 -d ","`
MAIL_LIST1=`echo $LINE1|cut -f13 -d ","`
DATE_OF_PROJECT=`echo $LINE1|cut -f14 -d ","`

echo "value of i is $i"
PROJECT_ID_array[$i]=$PROJECT_ID;
echo ${PROJECT_ID_array[$i]}
PREM_CITY_array[$i]=$PREM_CITY;
PREM_STATE_array[$i]=$PREM_STATE;
PROJECT_TITLE_aray[$i]=$PROJECT_TITLE;
DATE_OF_PROJECT_array[$i]=$DATE_OF_PROJECT;
DATE_TYPE_array[$i]=$DATE_TYPE;
PLANNER_NAME_array[$i]=$PLANNER_NAME;
PLANNER_PHONE_array[$i]=$PLANNER_PHONE;
PM_NAME_array[$i]=$PM_NAME;
PM_PHONE_array[$i]=$PM_PHONE;
OSR1_array[$i]=$OSR1;
echo ${OSR1_array[$i]}
OSR2_array[$i]=$OSR2;
echo ${OSR2_array[$i]}

i=$((i+1))
done


echo records for 2nd value is ${PROJECT_ID_array[1]}
fi
done

"in the above code SQL 1 generates 9 records...so the outer loop should continue for 9 records bt it is going beyond 9..."

It's a bit hard to tell without any of the debugging output that this would generate,
but there are a couple of small things to check:

You have this on the second line to create the output file which feeds the loop :
$JAVA icoredp.tools.oracleUnload -c ${CSORDHOME}/conf -s "$sql1" >> ${output}
You use ">>" and APPEND to $output, so if this was attempted more than once,
there could be more than 9 lines it $output.


Depending on your shell, it might be unwise to have BOTH while loops use the same variable:

cut -f1 $output| while read LINE1
cut -f1 $DISCORDFILE| while read LINE1