Table is not updating

rkrish · March 26, 2012, 8:03am

Hi,
I have script lyk:

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
SELECT COUNT(*) INTO CNT FROM USAGE_INTRNL_ERROR WHERE AUDIT_GROUP_ID='&1';

IF ( CNT != &3) THEN
  SELECT INTRL_DROP_QTY,INTRL_ERROR_QTY INTO DROPCNT,ERRORCNT 
     FROM USAGE_AUDIT_GROUP WHERE AUDIT_GROUP_NBR='&1'; 

     DROPCNT :=DROPCNT-CNT;

     UPDATE USAGE_AUDIT_GROUP 
      SET INTRL_DROP_QTY='&DROPCNT' ,INTRL_ERROR_QTY='&CNT'  
      WHERE  AUDIT_GROUP_NBR='&1' ;
COMMIT;

END IF;
END;
EXIT;
EOF`
done

While executing it didnt throw me any errors but the values are not updating in the table.
Can anyone please help me in this

Scott · March 26, 2012, 8:05am

Perhaps add a slash after EXIT;

EXIT;
/
EOF`

to execute the PL/SQL.

rkrish · March 26, 2012, 8:20am

Tried that still the same

cero · March 26, 2012, 8:43am

I think the table is updated, but INTRL_DROP_QTY and INTRL_ERROR_QTY are set to NULL. In the set clause of your update statement you refer to DROPCNT and CNT as SQL*Plus variables &DROPCNT and &CNT, which are not defined. Use the PL/SQL variables you worked with before the updatestament:

UPDATE USAGE_AUDIT_GROUP 
   SET INTRL_DROP_QTY=to_char(DROPCNT) ,INTRL_ERROR_QTY=to_char(CNT)
 WHERE AUDIT_GROUP_NBR='&1' ;

Scott · March 26, 2012, 8:47am

...and, sorry, the / should be before the EXIT;. as that's not part of the PL/SQL.

rkrish · March 26, 2012, 9:46am

I have tried with this one but still it is not reflecting:(

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
SELECT COUNT(*) INTO CNT FROM USAGE_INTRNL_ERROR WHERE AUDIT_GROUP_ID='&1';

IF ( CNT != &3) THEN
  SELECT INTRL_DROP_QTY,INTRL_ERROR_QTY INTO DROPCNT,ERRORCNT 
     FROM USAGE_AUDIT_GROUP WHERE AUDIT_GROUP_NBR='&1'; 

     DROPCNT :=DROPCNT-CNT;

     UPDATE USAGE_AUDIT_GROUP 
      SET INTRL_DROP_QTY=to_char(DROPCNT) ,INTRL_ERROR_QTY=to_char(CNT)
      WHERE  AUDIT_GROUP_NBR='&1' ;
COMMIT;

END IF;
END;
/
EXIT;
EOF`
done

---------- Post updated at 08:46 AM ---------- Previous update was at 08:44 AM ----------

cero:

I think the table is updated, but INTRL_DROP_QTY and INTRL_ERROR_QTY are set to NULL. In the set clause of your update statement you refer to DROPCNT and CNT as SQL*Plus variables &DROPCNT and &CNT, which are not defined. Use the PL/SQL variables you worked with before the updatestament:
UPDATE USAGE_AUDIT_GROUP 
   SET INTRL_DROP_QTY=to_char(DROPCNT) ,INTRL_ERROR_QTY=to_char(CNT)
 WHERE AUDIT_GROUP_NBR='&1' ;

I have done the change and tried but it is not reflecting in tables:(

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
SELECT COUNT(*) INTO CNT FROM USAGE_INTRNL_ERROR WHERE AUDIT_GROUP_ID='&1';

IF ( CNT != &3) THEN
  SELECT INTRL_DROP_QTY,INTRL_ERROR_QTY INTO DROPCNT,ERRORCNT 
     FROM USAGE_AUDIT_GROUP WHERE AUDIT_GROUP_NBR='&1'; 

     DROPCNT :=DROPCNT-CNT;

     UPDATE USAGE_AUDIT_GROUP 
      SET INTRL_DROP_QTY=to_char(DROPCNT) ,INTRL_ERROR_QTY=to_char(CNT)
      WHERE  AUDIT_GROUP_NBR='&1' ;
COMMIT;

END IF;
END;
/
EXIT;
EOF`
done

can we print the update statements,select statements on to the screen so that it can be verified if the variables are passing correct or not...is it possible ? :-/

Scott · March 26, 2012, 9:53am

What (in SQL*Plus) is the output of:

SELECT COUNT(*) FROM USAGE_INTRNL_ERROR WHERE AUDIT_GROUP_ID='='&1'

(replacing &1 with a value that comes from text file?

What would the equivalent "&3" from that file be?

Is the IF test ever successful?

Try to run the update (with rollback) with those values. Does it succeed?

methyl · March 26, 2012, 9:53am

For diagnostic purposes:

ret=`
EOF`

If you lose the backticks and the ret= assignment the output should now be to the screen not to a (pointless?) environment variable.
Also take out the "-s" (silent) from the sqlplus command line.

rkrish · March 26, 2012, 10:22am

scott:

What (in SQL*Plus) is the output of:
SELECT COUNT(*) FROM USAGE_INTRNL_ERROR WHERE AUDIT_GROUP_ID='='&1'
(replacing &1 with a value that comes from text file?

What would the equivalent "&3" from that file be?

Is the IF test ever successful?

Try to run the update (with rollback) with those values. Does it succeed?

Yeah it got updated with the values from the text file.
I have tested it manullay from sql plus session.
The values got updated.
The contents of the file will be as follows:

1LK81JVDE2HRNDG              3               0                                                                         
                                                                                                                       
                                                              
1LMXTJJD0W28TX2              3               0                                                                         
                                                                                                                       
                                                              
1LS1XJGDEVWAC5T              7               1

binlib · March 26, 2012, 10:54am

Add this line after your update statement:

DBMS_OUTPUT.PUT_LINE('Updated ' || SQL%ROWCOUNT || ' rows:');

rkrish · March 26, 2012, 11:06am

yeah added but nothing printed over the screen :wall:

---------- Post updated at 10:06 AM ---------- Previous update was at 10:04 AM ----------

methyl:

For diagnostic purposes:
ret=`
EOF`
If you lose the backticks and the ret= assignment the output should now be to the screen not to a (pointless?) environment variable.
Also take out the "-s" (silent) from the sqlplus command line.

yeah removed backticks and tried:
got this over the screen:


1LK81JVDE2HRNDG
3
0
ModifyUsgAuditGroupTable.ksh[74]: secadmin/vivadmin@ltd121a:  not found
1LMXTJJD0W28TX2
3
0
ModifyUsgAuditGroupTable.ksh[74]: secadmin/vivadmin@ltd121a:  not found
1LS1XJGDEVWAC5T
7
1
ModifyUsgAuditGroupTable.ksh[74]: secadmin/vivadmin@ltd121a:  not found

methyl · March 26, 2012, 12:24pm

Perhaps you accidentally deleted the "sqlplus" command too (not just the -s parameter).

rkrish · March 26, 2012, 12:30pm

No I didnt delete that one..it is there

nawk '{{print $1,$2,$3}}'  ./$DirectoryName/AuditGroupTableTableData_$TimeStamp.txt | while read a b c
do
echo $a
echo $b 
echo $c
ret=sqlplus $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
SELECT COUNT(*) INTO CNT FROM USAGE_INTRNL_ERROR WHERE AUDIT_GROUP_ID='&1';

IF ( CNT != &3) THEN
  SELECT INTRL_DROP_QTY,INTRL_ERROR_QTY INTO DROPCNT,ERRORCNT 
     FROM USAGE_AUDIT_GROUP WHERE AUDIT_GROUP_NBR='&1'; 

     DROPCNT :=DROPCNT-CNT;

     UPDATE USAGE_AUDIT_GROUP 
      SET INTRL_DROP_QTY='DROPCNT' ,INTRL_ERROR_QTY='CNT'  
      WHERE  AUDIT_GROUP_NBR='&1' ;
COMMIT;

END IF;
END;
/
EXIT;
EOF
done

methyl · March 26, 2012, 12:36pm

Lose the "ret=". We were trying to stop output from sqlplus disappearing into a variable.

sqlplus $db_user/$db_pwd@$db_sid  $a $b $c <<EOF

Btw: This is getting very confusing. Have you got the same problem posted under user Rajesh_Putnala ?

rkrish · March 26, 2012, 2:49pm

methyl:

Lose the "ret=". We were trying to stop output from sqlplus disappearing into a variable.
sqlplus $db_user/$db_pwd@$db_sid  $a $b $c <<EOF
Btw: This is getting very confusing. Have you got the same problem posted under user Rajesh_Putnala ?

Ha yes..I have only posted that.
As it is very urgent I have posted it here also:)

---------- Post updated at 01:49 PM ---------- Previous update was at 01:41 PM ----------

methyl:

Lose the "ret=". We were trying to stop output from sqlplus disappearing into a variable.
sqlplus $db_user/$db_pwd@$db_sid  $a $b $c <<EOF
Btw: This is getting very confusing. Have you got the same problem posted under user Rajesh_Putnala ?

No it also didnt work..
It just displayed usage of sqlplus over the screen:wall:

methyl · March 26, 2012, 3:03pm

This is meant as an amendment to the diagnostic version of the script, not a free-standing command.

Scott · March 26, 2012, 3:05pm

That brings into question the values of $db_user/$db_pwd@$db_sid ...

methyl · March 26, 2012, 3:14pm

I agree ... and the whole content of the command line.
What if the variable "${ret}" has been hiding the output from a fundamental problem?
Would be nice to see this current version of the script and the exact error messages because there might be a clue there.

rkrish · March 26, 2012, 3:15pm

No the values are correct only,
Because I have already used them inthe script:


# *************************************************************
# * ModifyUsgAuditGroupTable.ksh.ksh - this script is used to update
#* the audit_group_table for the audit_group_id's which
#* are failed with internal error qty less than 1 error.
# 
# *************************************************************
# * Loading DB connection credentials
# *************************************************************
db_user=$DB_USER_NAME
db_pwd=$DB_PASSWORD
db_sid=$TWO_TASK



if [ $# -ne 1 ]
then
echo "\tUsage: ModifyUsgAuditGroupTable.ksh <yymmdd>"
exit 1
fi

TimeStamp=`date +%Y%m%d%H%M%S`
DirectoryName="AuditGroupLogs_$TimeStamp"

echo "********Started processing at $TimeStamp**********"

if [ !  -d  $DirectoryName ]
then
mkdir $DirectoryName
echo " log file path is : ./$DirectoryName/AuditUpdate_$TimeStamp.LOG";
else
echo " log file path is : ./$DirectoryName/AuditUpdate_$TimeStamp.LOG ";
fi

# *************************************************************
# *Extracting Failed acct numbers from BC log file and 
# * storing the acct nbr's in a file MUFailedAccts_$TimeStamp
# * in directory $DirectoryName
# *************************************************************

grep -i 'RTUServer Error:Count of Internal Error'  $1* |sed 's/.*-\(.*\),.*,.*,.*,.*/\1/g' > ./List.dat
sort -u ./List.dat -o ./List.dat

while read audit_group_id
do
echo "select  audit_group_nbr,intrl_drop_qty,intrl_error_qty  from usage_audit_group where  audit_group_nbr = '$audit_group_id' ;" >> ./$DirectoryName/SQLQuery_$TimeStamp.sql
done < ./List.dat

ret=`sqlplus -s  /nolog << EOF
connect $db_user/$db_pwd@$db_sid;
SPOOL  ./$DirectoryName/AuditGroupTableTableData_$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/AuditGroupTableTableData_$TimeStamp.txt | while read a b c
do
echo $a
echo $b 
echo $c
sqlplus $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
SELECT COUNT(*) INTO CNT FROM USAGE_INTRNL_ERROR WHERE AUDIT_GROUP_ID='&1';

IF ( CNT != &3) THEN
  SELECT INTRL_DROP_QTY,INTRL_ERROR_QTY INTO DROPCNT,ERRORCNT 
     FROM USAGE_AUDIT_GROUP WHERE AUDIT_GROUP_NBR='&1'; 

     DROPCNT :=DROPCNT-CNT;

     UPDATE USAGE_AUDIT_GROUP 
      SET INTRL_DROP_QTY='DROPCNT' ,INTRL_ERROR_QTY='CNT'  
      WHERE  AUDIT_GROUP_NBR='&1' ;
COMMIT;

END IF;
END;
/
EXIT;
EOF
done

This is the snippet of the whole script.
I have used these values earlier itself for creating AuditGroupTableTableData_$TimeStamp.txt
The file is created well.
I thnk the problem is with the statement :

sqlplus $db_user/$db_pwd@$db_sid  $a $b $c <<EOF

any changes need to be done for this statement:mad:

Scott · March 26, 2012, 3:19pm

And the values of $DB_USER_NAME, $DB_PASSWORD and $TWO_TASK, would be?

(skip the detail, but are they set?)