Home » SQL & PL/SQL » SQL & PL/SQL » Bulk collect and for loop iteration error (Oracle 11g)
Bulk collect and for loop iteration error [message #574562] Sat, 12 January 2013 21:11 Go to next message
swas_recall
Messages: 7
Registered: May 2012
Location: Bangalore
Junior Member

CREATE OR REPLACE
PROCEDURE prc_isdn_bearer_id_seq_24
  --(v_run_id NUMBER default null)

IS
  v_sql              VARCHAR2(4000);
  v_run_id           NUMBER :=0909;
  v_bearer_seq_no    VARCHAR2(10) ;
  v_sql_insert       VARCHAR2(4000);
  v_isdn_bearer_id   VARCHAR2(30);
  v_rec_count        NUMBER         :=0;
  v_chk_rec_count    NUMBER         :=0;
  v_chk_berrec_count NUMBER         :=0;
  v_pm_bearer_count  NUMBER         :=0;
  v_err_code         VARCHAR2(30)   :='MC_ERR_FM18';
  v_err_msg          VARCHAR2(4000) := 'Valid Bearer Id not found';
TYPE t_type_bearer_id
IS
  TABLE OF VARCHAR2(30) ;
  v_type_bearer_id t_type_bearer_id;
TYPE t_type_pm_bearer_count
IS
  TABLE OF VARCHAR2(30) ;
  v_type_pm_bearer_count t_type_pm_bearer_count;
TYPE t_type_bearer_chnls
IS
  TABLE OF VARCHAR2(30) ;
  v_type_bearer_chnls t_type_bearer_chnls;
BEGIN
  --
  -- get the list of all the billing accounts by joining
  -- driver_product_profile dpp,src_css_installation_products cip,src_css_cp_note ccn,lov_isdn li

  EXECUTE Immediate 'DELETE FROM fld_map_isdn_bearer WHERE run_id = ' || v_run_Id;
  FOR I IN
  ( SELECT DISTINCT bill_accnt_key,
    pm.district_code district_code ,
    pm.billing_account_no billing_account_no ,
    pm.inst_sequence_no inst_sequence_no,
    COUNT(pm_bearers) pm_bearers
  FROM product_map_isdn_24 pm,
    (SELECT 'CSS'
      || cip.district_code
      || cip.billing_account_no AS bill_accnt_key ,
      cip.billing_account_no ,
      cip.district_code ,
      Cip.Inst_Sequence_No
    FROM DRIVER_PRODUCT_PROFILE_24 DPP,
      SRC_CSS_CP_NOTE_24 CCN,
      SRC_CSS_INST_PRODUCTS_24 CIP,
      SRC_CSS_INSTALLATION_24 CI
    WHERE NVL(ci.WLR3_FLAG,'X') <>'Y'
    AND cip.DISTRICT_CODE        =ci.DISTRICT_CODE
    AND cip.billing_account_no   = ci.billing_account_no
    AND dpp.product_id           = cip.product_code
    AND dpp.contract_type        = cip.contract_type
    AND NVL(trim(dpp.mc_flag),0) = NVL(trim(cip.maintenance_contract),0)
    AND dpp.special_info LIKE '%Bearer%'
    AND dpp.prod_cat ='ISDN'
      --  AND SUBSTR(bcl.bill_account_key,4,2) = cip.district_code
      --    AND SUBSTR(bcl.bill_account_key,6) = cip.billing_account_no
      --   AND bcl.run_id = v_run_id
      --    AND bcl.bac_status ='CSS PENDING CHECK - SUCCESS'
      --    AND bcl.failure_flag='N'
      --     And Bcl.Stage0_Or_Isdn_Flag = 'Y'
    AND Cip.District_Code                     = Ccn.District_Code(+)
    AND Cip.Billing_Account_No                = Ccn.Billing_Account_No(+)
    AND Cip.Inst_Sequence_No                  = Ccn.Inst_Sequence_No(+)
    AND Cip.Product_Code                      = Ccn.Product_Code(+)
    AND Cip.Contract_Type                     = Ccn.Contract_Type(+)
    AND NVL(trim(cip.maintenance_contract),0) = NVL(trim(ccn.maintenance_contract(+)),0)
    ORDER BY ccn.ts_last_updated DESC
    )s
  WHERE pm.district_code   =s.district_code
  AND pm.billing_account_no=s.billing_account_no
  AND pm.inst_sequence_no  =s.inst_sequence_no
  GROUP BY s.bill_accnt_key,
    pm.district_code,
    pm.billing_account_no,
    pm.inst_sequence_no
  )
  LOOP

    -- to get the bearer id
    -- to check if the product note has the prefix from lov prefix table
    -- also check if the product note has bearer prefix along with 5/6 digits numeric value
    --
    -- SELECT COUNT(pm.pm_bearers)
    -- INTO v_pm_bearer_count
    -- FROM product_map_isdn_24 pm
    -- WHERE pm.district_code   =I.district_code
    -- AND pm.billing_account_no=I.billing_account_no
    -- AND pm.inst_sequence_no  =I.inst_sequence_no
    --     and rownum=1;
    --  group by  pm.district_code,pm.billing_account_no,pm.inst_sequence_no;
    dbms_output.put_line(I.pm_bearers);
   IF I.pm_bearers > '1' THEN
       v_sql :=
    'SELECT substr(regexp_replace((REGEXP_SUBSTR(TRIM(ccn.product_note)||'' '',''MIGR:''||li.filter_condition||''([[:digit:]]{5}|[[:digit:]]{6})([^[:digit:]]{1})''
    ,1,1,''i'')),''-$''),6,LENGTH(REGEXP_SUBSTR(TRIM(product_note)||'' '',''MIGR:''|| li.filter_condition||
    ''([[:digit:]]{5}|[[:digit:]]{6})([^[:digit:]]{1})'',1,1,''i'')) -1),
    substr(REGEXP_SUBSTR(TRIM(ccn.product_note),''-''||''([[:digit:]]{1,100})([^[:digit:]]{1})'',1,1,''i''),2
    ,length(REGEXP_SUBSTR(TRIM(ccn.product_note),''-''||''([[:digit:]]{1,100})([^[:digit:]]{1})'',1,1,''i''))-2)
    FROM bac_lov_lookup_24 li, src_css_cp_note_24 ccn,DRIVER_PRODUCT_PROFILE_24 DPP
    WHERE li.variable_name =''FM.BearerID''
    AND INSTR(upper(ccn.product_note),li.filter_condition)>=1
    AND DPP.product_id = ccn.Product_Code
    AND  DPP.contract_type = ccn.contract_type
    AND NVL(trim(dpp.mc_flag),0) =  NVL(trim(ccn.maintenance_contract(+)),0)
    AND dpp.special_info like ''%Bearer%''
    AND dpp.prod_cat =''ISDN''
    --AND NVL(trim(ccn.district_code),0) = NVL(trim(:1),0)
    --AND ccn.billing_account_no = :2
    AND NVL(trim(ccn.inst_sequence_no),0) = ''0001''
    --NVL(trim(:3),0)
    AND TRIM(ccn.maintenance_contract) IS NULL
    AND REGEXP_LIKE(TRIM(ccn.product_note)||'' '',''MIGR:''|| li.filter_condition||''([[:digit:]]{5}|[[:digit:]]{6})([^[:digit:]]{1})'',''i'')
    AND (to_number( substr(REGEXP_SUBSTR(TRIM(ccn.product_note),''-''||''([[:digit:]]{1,100})([^[:digit:]]{1})'',1,1,''i''),2
    ,length(REGEXP_SUBSTR(TRIM(ccn.product_note),''-''||''([[:digit:]]{1,100})([^[:digit:]]{1})'',1,1,''i''))-2))
    between 8 and 30)
    AND  length(trim(translate(to_number( substr(REGEXP_SUBSTR(TRIM(ccn.product_note),''-''||''([[:digit:]]{1,100})([^[:digit:]]{1})'',1,1,''i''),2
    ,length(REGEXP_SUBSTR(TRIM(ccn.product_note),''-''||''([[:digit:]]{1,100})([^[:digit:]]{1})'',1,1,''i''))-2)),'' +-.0123456789'','' '')))
    is null
    ORDER BY ccn.ts_last_updated DESC'*/
    --  ;
    --  dbms_output.put_line(v_sql);
   ELSE
    -- dbms_output.put_line(I.pm_bearers);
    v_sql :=
    'SELECT SUBSTR(REGEXP_SUBSTR(TRIM(ccn.product_note)||'' '', li.filter_condition||''([[:digit:]]{5}|[[:digit:]]{6})([^[:digit:]]{1}|[[:space:]]{1})'',1,1,''i''),            
1,Length(REGEXP_SUBSTR(TRIM(ccn.product_note)||'' '', li.filter_condition||''([[:digit:]]{5}|[[:digit:]]{6})([^[:digit:]]{1}|[[:space:]]{1})'',1,1,''i''))-1)                          
FROM bac_lov_lookup_24 li, src_css_cp_note_24 ccn,DRIVER_PRODUCT_PROFILE_24 DPP                        
WHERE li.variable_name =''FM.BearerID''                                                         
AND INSTR(upper(ccn.product_note),li.filter_condition)>=1                                                         
AND DPP.product_id = ccn.Product_Code                                                         
AND  DPP.contract_type = ccn.contract_type                                                         
AND NVL(trim(dpp.mc_flag),0) =  NVL(trim(ccn.maintenance_contract(+)),0)                                                         
AND dpp.special_info like ''%Bearer%''                                                            
AND dpp.prod_cat =''ISDN''                             
AND NVL(trim(ccn.district_code),0) = NVL(trim(:1),0)                             
AND ccn.billing_account_no = :2                             
AND NVL(trim(ccn.inst_sequence_no),0) =NVL(trim(:3),0)                             
AND TRIM(ccn.maintenance_contract) IS NULL                             
AND REGEXP_LIKE(TRIM(ccn.product_note)||'' '', li.filter_condition||''([[:digit:]]{5}|[[:digit:]]{6})([^[:digit:]]{1}|[[:space:]]{1}){1}'',''i'')                          
ORDER BY ccn.ts_last_updated DESC'
    ;
    dbms_output.put_line(v_sql);
    dbms_output.put_line(I.inst_sequence_no);
    dbms_output.put_line(I.district_code);
    dbms_output.put_line(I.billing_account_no);
    dbms_output.put_line(I.pm_bearers);
    dbms_output.put_line('why it is 0');
    EXECUTE IMMEDIATE v_sql BULK COLLECT INTO v_type_bearer_id,v_type_bearer_chnls USING I.district_code,I.billing_account_no,I.inst_sequence_no;
    --  END IF;
    -- dbms_output.put_line(v_type_bearer_id(I));
    dbms_output.put_line('why it is 0'||v_type_bearer_id.COUNT);
    IF v_type_bearer_id.COUNT >0 THEN
      FOR idx                IN v_type_bearer_id.FIRST .. v_type_bearer_id.LAST
      LOOP
        v_bearer_seq_no := '01';
        dbms_output.put_line(v_type_bearer_id(idx));
        dbms_output.put_line(v_type_bearer_chnls(idx));
        --    end;
        --  fm_load_bearer_chnls := v_type_bearer_chnls(idx);
        -- check bacs where same bearer is found on multiple bacs
        SELECT COUNT(1)
        INTO v_chk_rec_count
        FROM fld_map_isdn_bearer fmib
        WHERE fmib.district_code    = I.district_code
        AND fmib.billing_account_no = I.billing_account_no
        AND fmib.inst_sequence_no   = I.inst_sequence_no
        AND run_id                  = v_run_id;
        IF v_chk_rec_count          = 0 THEN
          --
          -- insert a record into fld_map_isdn_bearer if all the conditions are satisfied for bearer id

          INSERT
          INTO fld_map_isdn_bearer_24
            (
              run_id ,
              bill_accnt_key ,
              billing_account_no ,
              district_code ,
              inst_sequence_no ,
              fm_bearerid ,
              fm_bearerseqno ,
              fm_bearerchnls
            )
            VALUES
            (
              v_run_id ,
              I.bill_accnt_key ,
              I.billing_account_no ,
              I.district_code ,
              I.inst_sequence_no ,
              UPPER(v_type_bearer_id(idx)) ,
              v_bearer_seq_no ,
              UPPER(v_type_bearer_chnls(idx))
            );
        END IF;
      END LOOP;
     
    ELSE
      INSERT
        /*+ APPEND */
      INTO invalid_fm_bac_24
        (
          RUN_ID,
          RECORD_ID,
          BILL_ACCOUNT_KEY,
          ERROR_CODE,
          ERROR_REASON
        )
        VALUES
        (
          v_run_id,
          RECORD_ID_FM_SEQ.nextval,
          I.bill_accnt_key,
          v_err_code,
          v_err_msg
        );
    END IF;
  END LOOP;
  -- to handle same bearer id on multiple bacs beg
  --
  INSERT
  INTO invalid_fm_bac_24
    (
      RUN_ID,
      RECORD_ID,
      BILL_ACCOUNT_KEY,
      ERROR_CODE,
      ERROR_REASON
    )
  SELECT v_run_id,
    RECORD_ID_FM_SEQ.nextval,
    bill_accnt_key,
    v_err_code,
    v_err_msg
  FROM fld_map_isdn_bearer_24 fmib,
    (SELECT fm_bearerid,
      COUNT(1)
    FROM fld_map_isdn_bearer
    WHERE run_id = v_run_id
    GROUP BY fm_bearerid
    HAVING COUNT(1) >1
    ) fm_b
  WHERE fmib.fm_bearerid = fm_b.fm_bearerid
  AND fmib.run_id        = v_run_id;
  DELETE
  FROM fld_map_isdn_bearer_24 fmb
  WHERE fmb.fm_bearerid IN
    (SELECT fmib.fm_bearerid
    FROM fld_map_isdn_bearer_24 fmib
    WHERE fmib.fm_bearerid = fmb.fm_bearerid
    AND fmib.run_id        = v_run_id
    GROUP BY fmib.fm_bearerid
    HAVING COUNT(fmib.fm_bearerid) >1
    )
  AND fmb.run_id = v_run_id;
  --
  -- to handle same bearer id on multiple bacs end
  --
  --
  -- update the status in bac_candidate_list table for all the billing accounts that got rejected during Bearer id computation
  --
  --  UPDATE bac_candidate_list bcl
  --    SET bcl.bac_status = 'PRODUCT TABLES EXTRACTION - FAILED'
  --       , bcl.failure_flag ='Y'
  --   WHERE EXISTS ( SELECT 1
  --                               FROM invalid_fm_bac ifb
  --                          WHERE ifb.bill_account_key = bcl.bill_account_key
  --                               AND ifb.run_id = bcl.run_id
  --                              AND ifb.error_code = v_err_code
  --                              AND ifb.error_reason = v_err_msg
  --                        )
  --      AND bcl.run_id = v_run_id
  --    AND bcl.stage0_or_isdn_flag = 'Y';
  COMMIT;
EXCEPTION
WHEN OTHERS THEN
  NULL;
  --   DBMS_OUTPUT.PUT_LINE('Error While Executing the Procedure --prc_compute_isdn ' || sqlerrm || ' for Run Id ' || v_run_id);
END prc_isdn_bearer_id_seq_24;



Hi All

As per the above procedure i am getting the below result from the for loop but it is processing for only the first record and not iterating through the set of records
also the execute immediate bulk collect is not populating the table type variables with values

Please let me know in case of any concerns.


Bill acct key	district code	bill accnt num	inst no	pm_bearers
CSSSW38485041	SW	        38485041	0001	1
CSSWM38396668	WM	        38396668	0001	1
CSSWM38199380	WM	        38199380	0001	1
CSSWM34962678	WM	        34962678	0001	3
CSSSM38710752	SM	        38710752	0001	2
CSSWM39100299	WM	        39100299	0001	1


Re: Bulk collect and for loop iteration error [message #574563 is a reply to message #574562] Sat, 12 January 2013 21:14 Go to previous messageGo to next message
BlackSwan
Messages: 23158
Registered: January 2009
Senior Member
since we don't have your tables or data; at best we can look at your code & say it is interesting.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Bulk collect and for loop iteration error [message #574566 is a reply to message #574562] Sun, 13 January 2013 01:20 Go to previous messageGo to next message
Michel Cadot
Messages: 60012
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Please let me know in case of any concerns.


Quote:
EXCEPTION
WHEN OTHERS THEN
  NULL;


This is the biggest bug you can write in PL/SQL.
Read WHEN OTHERS.

Regards
Michel

[Updated on: Sun, 13 January 2013 01:20]

Report message to a moderator

Re: Bulk collect and for loop iteration error [message #574589 is a reply to message #574562] Sun, 13 January 2013 16:37 Go to previous messageGo to next message
spacebar
Messages: 26
Registered: February 2007
Junior Member
Change these three staatments from:
  type t_type_bearer_id is table of varchar2( 30 );
  type t_type_pm_bearer_count is table of varchar2( 30 );
  type t_type_bearer_chnls is table of varchar2( 30 );


To and try it:
  type t_type_bearer_id is table of varchar2( 30 ) index by pls_integer;
  type t_type_pm_bearer_count is table of varchar2( 30 ) index by pls_integer;
  type t_type_bearer_chnls is table of varchar2( 30 ) index by pls_integer;
Re: Bulk collect and for loop iteration error [message #574598 is a reply to message #574589] Mon, 14 January 2013 01:05 Go to previous messageGo to next message
Michel Cadot
Messages: 60012
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why?

Regards
Michel
Re: Bulk collect and for loop iteration error [message #574676 is a reply to message #574598] Mon, 14 January 2013 16:40 Go to previous message
swas_recall
Messages: 7
Registered: May 2012
Location: Bangalore
Junior Member

Hi All

The procedure worked fine that was due fact that execute immediate was not populating the collection variable.it is now workign fine after debug.

thanks all for the valuable suggestions
Previous Topic: can we tune this query without creating any objects like indexes etc.
Next Topic: update statement help
Goto Forum:
  


Current Time: Sun Dec 21 12:51:00 CST 2014

Total time taken to generate the page: 0.12204 seconds