Bulk collect not updating

[ananthaisin]

Hi All,

I have two tables 1) In_decrement_ba se_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?