By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
462,143 Members | 709 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 462,143 IT Pros & Developers. It's quick & easy.

Bulk collect not updating

P: 15
Hi All,

I have two tables 1) In_decrement_base_ch
Expand|Select|Wrap|Line Numbers
  1.   MSC_ID        VARCHAR2(20 ),
  2.   PLAN_ID    VARCHAR2(20 ),
  3.   ACT_DATE   DATE,
  4.   DUM        VARCHAR2(5 ),
  5.   PADJ       NUMBER(10,2));
  6.  
  7. 2)
  8. CREATE TABLE TMP_ADJ
  9. (
  10.   MSC_ID               NUMBER(20),
  11.   NOMINALAMOUNT         NUMBER(20))
  12.  
  13. I have to comepare MSC_ID and update the Nominalamount in table1 (PADJ)column
  14.  
  15. This is the proc i have written
  16.  
  17.  
  18. CREATE OR REPLACE procedure PRE_DECRE_CALC is
  19.  
  20. lcnt number(5);
  21. TYPE ADJMOB IS TABLE OF tmp_adj.MSISDN%TYPE;
  22. ADJMSISDN ADJMOB ;
  23.  
  24. TYPE ADJAMT IS TABLE OF tmp_adj.NOMINALAMOUNT%TYPE;
  25. ADJNAMT ADJAMT;
  26.  
  27.  
  28.  
  29. BEGIN
  30.  
  31. SELECT COUNT(*) INTO LCNT FROM AUTO_SH_LOAD WHERE TRUNC(LOADDATE)=TRUNC(SYSDATE)-1
  32. AND FILEGROUP IN ('CHPYMT','CHP2P','CHREFL','CHSTAT','CHADJ','CHCLCR','CHSFEE');
  33.  
  34. IF LCNT=7 THEN
  35.  
  36. DECLARE
  37.  
  38. CURSOR C1 IS 
  39. SELECT MSC_ID,SUM(NOMINALAMOUNT)  
  40. FROM tmp_adj A  GROUP BY
  41. MSC_ID;
  42.  
  43.  
  44. BEGIN
  45. OPEN C1;
  46. LOOP
  47. FETCH C1 BULK COLLECT INTO ADJMSISDN,ADJNAMT;
  48. EXIT WHEN C1%NOTFOUND;
  49.  
  50. FORALL I IN ADJMSISDN.FIRST..ADJMSISDN.LAST 
  51.  
  52. UPDATE IN_DECREMENT_BASE_CH V SET V.PADJ=ADJNAMT(I) WHERE  V.MSC_ID=ADJMSISDN(I);
  53. COMMIT;
  54.  
  55.  
  56. END LOOP;
  57. END;
  58. END IF;
  59. END;
  60. /

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)
May 26 '10 #1
Share this Question
Share on Google+
2 Replies

P: 41
ananthaisin,

can you post the output of:

Expand|Select|Wrap|Line Numbers
  1. SELECT COUNT(*) 
  2. FROM   AUTO_SH_LOAD 
  3. WHERE  TRUNC(LOADDATE)  =  TRUNC(SYSDATE) - 1 
  4. AND    FILEGROUP       IN  ( 'CHPYMT','CHP2P' ,'CHREFL','CHSTAT'
  5.                             ,'CHADJ' ,'CHCLCR','CHSFEE')
  6.  
edited:
I forgot, the output of

Expand|Select|Wrap|Line Numbers
  1. select count(*)
  2. from
  3.   ( SELECT      MSC_ID,SUM(NOMINALAMOUNT)   
  4.     FROM         tmp_adj A  
  5.     GROUP BY MSC_ID
  6.   );
  7.  
also.
May 30 '10 #2

amitpatel66
Expert 100+
P: 2,367
Firstly, when you are using Fetch..BULK COLLECT clause then why you need a LOOP?
Jun 7 '10 #3

Post your reply

Sign in to post your reply or Sign up for a free account.