Bulk collect not updating

[ananthaisin]

Hi All,

I have two tables 1) In_decrement_base_ch

  MSC_ID        VARCHAR2(20 ),
  PLAN_ID    VARCHAR2(20 ),
  ACT_DATE   DATE,
  DUM        VARCHAR2(5 ),
  PADJ       NUMBER(10,2));
 
2)
CREATE TABLE TMP_ADJ
(
  MSC_ID               NUMBER(20),
  NOMINALAMOUNT         NUMBER(20))
 
I have to comepare MSC_ID and update the Nominalamount in table1 (PADJ)column
 
This is the proc i have written
 
 
CREATE OR REPLACE procedure PRE_DECRE_CALC is
 
lcnt number(5);
TYPE ADJMOB IS TABLE OF tmp_adj.MSISDN%TYPE;
ADJMSISDN ADJMOB ;
 
TYPE ADJAMT IS TABLE OF tmp_adj.NOMINALAMOUNT%TYPE;
ADJNAMT ADJAMT;
 
 
 
BEGIN
 
SELECT COUNT(*) INTO LCNT FROM AUTO_SH_LOAD WHERE TRUNC(LOADDATE)=TRUNC(SYSDATE)-1
AND FILEGROUP IN ('CHPYMT','CHP2P','CHREFL','CHSTAT','CHADJ','CHCLCR','CHSFEE');
 
IF LCNT=7 THEN
 
DECLARE
 
CURSOR C1 IS 
SELECT MSC_ID,SUM(NOMINALAMOUNT)  
FROM tmp_adj A  GROUP BY
MSC_ID;
 
 
BEGIN
OPEN C1;
LOOP
FETCH C1 BULK COLLECT INTO ADJMSISDN,ADJNAMT;
EXIT WHEN C1%NOTFOUND;
 
FORALL I IN ADJMSISDN.FIRST..ADJMSISDN.LAST 
 
UPDATE IN_DECREMENT_BASE_CH V SET V.PADJ=ADJNAMT(I) WHERE  V.MSC_ID=ADJMSISDN(I);
COMMIT;
 
 
END LOOP;
END;
END IF;
END;
/

Procedure got compiled but data is not getting updated in the table 1...

Am i making any mistake here if so pls correct me...

Note : (Auto_Sh_load is a temp log table)

[magicwand]

ananthaisin,

can you post the output of:

SELECT COUNT(*) 
FROM   AUTO_SH_LOAD 
WHERE  TRUNC(LOADDATE)  =  TRUNC(SYSDATE) - 1 
AND    FILEGROUP       IN  ( 'CHPYMT','CHP2P' ,'CHREFL','CHSTAT'
                            ,'CHADJ' ,'CHCLCR','CHSFEE')
; 
 

edited:
I forgot, the output of

select count(*)
from
  ( SELECT      MSC_ID,SUM(NOMINALAMOUNT)   
    FROM         tmp_adj A  
    GROUP BY MSC_ID
  );
 

also.

[amitpatel66]

Firstly, when you are using Fetch..BULK COLLECT clause then why you need a LOOP?