Shell Programming and Scripting

 1LMXTJJD0W28TX2
 1LS1XJGDEVWAC5T
 1LK81JVDE2HRNDG
 1LMXTJJD0W28TX2
 1LS1XJGDEVWAC5T
 1LK81JVDE2HRNDG
 1LMXTJJD0W28TX2

while read usage_id
do
echo "select  usage_id,intrl_drop,intrl_err from usage_id where  usage_id= '$usage_id' ;" >> ./$DirectoryName/SQLQuery_$TimeStamp.sql
done < ./List.dat
ret=`sqlplus -s  /nolog << EOF
connect $db_user/$db_pwd@$db_sid;
SPOOL  ./$DirectoryName/UsageTableData_$TimeStamp.txt;
set pagesize 0
set feedback off 
set verify off 
set heading off 
set echo off 
set linesize 300 
@./$DirectoryName/SQLQuery_$TimeStamp.sql
SPOOL OFF;
EXIT;
EOF`
nawk '{{print $1,$2,$3}}'  ./$DirectoryName/UsageTableData_$TimeStamp.txt | while read a b c
do
echo $a
echo $b 
echo $c
 
done

after this piece of code:
echo $a
echo $b 
echo $c

sqlplus -s xxxx/xxxx@$ORACLE_SID  $a $b $c <<EOF
SET HEADING OFF
SET SERVEROUTPUT ON
SET FEEDBACK OFF
DECLARE
CNT  NUMBER(5);
BCNT NUMBER(5);
BEGIN

SELECT COUNT(*) INTO CNT FROM USG_ERROR WHERE USAGE_ID='&1';

IF ( CNT != &3) THEN
   -- Fetch the  intrl_drp from usage_id and decrement it by &2.
   -Write the update query.
END IF;



END;
/
exit;
EOF

nawk '{{print $1,$2,$3}}'  ./$DirectoryName/AuditGroupTableTableData_$TimeStamp.txt | while read a b c
do
echo $a
echo $b 
echo $c
ret=`sqlplus -s $db_user/$db_pwd@$db_sid  $a $b $c <<EOF
SET HEADING OFF
SET SERVEROUTPUT ON
SET FEEDBACK OFF
DECLARE
CNT  NUMBER(5);
DROPCNT NUMBER(5);
ERRORCNT NUMBER(5);
BEGIN
echo "SELECT COUNT(*) INTO CNT FROM USAGE_ INTRNL_ERROR WHERE AUDIT_GROUP_ID='&1'";
IF ( CNT != &3) THEN
echo "SELECT INTRL_DROP_QTY,INTRL_ERROR_QTY INTO DROPCNT,ERRORCNT FROM USAGE_AUDIT_GROUP WHERE AUDIT_GROUP_ID='&1';">> ./$DirectoryName/SELECTQuery_$TimeStamp.sql
SET DROPCNT=DROPCNT-CNT;
echo "UPDATE USAGE_AUDIT_GROUP SET INTRL_DROP_QTY='&DROPCNT' ,INTRL_ERROR_QTY='&CNT'  WHERE USAGE_AUDIT_GROUP WHERE AUDIT_GROUP_ID='&1' ;" >>./$DirectoryName/UPDATEQuery_$TimeStamp.sql
END IF;
END;
EXIT;
EOF`
done