Home » SQL & PL/SQL » SQL & PL/SQL » Bulk Collect & For all in PL/SQL (Oracle 11g)
Bulk Collect & For all in PL/SQL [message #577705] Tue, 19 February 2013 15:24 Go to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hi All,

Below is my procedure which I used bulk collect and for all statements to select and insert the data in temp table.

The select SQl is returning one row. But its not inserting this row into temp table.

Its not throwing any exceptions. Used ref cursor because the select statement is going for every cursor.

here modified the code and provided only one cursor.


Create Or Replace Procedure Sales_Hist_Update_Bkp Is
  Type Type_Name Is Record(
    Sku_Item_Key            Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Sku_Item_Key%Type,
    Locationno              Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Locationno%Type,
    Bsns_Unit_Key           Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Bsns_Unit_Key%Type,
    Act_Item_Cost_Amt       Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Item_Cost_Amt%Type,
    Act_Rglr_Unit_Price_Amt Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Rglr_Unit_Price_Amt%Type,
    Cost_Cal_Amt            Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Rglr_Unit_Price_Amt%Type,
    Rtl_Cal_Amt             Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Rglr_Unit_Price_Amt%Type,
    Rtl_Cal_Amt1            Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Rglr_Unit_Price_Amt%Type,
    Unitssold               Stage_Ordm.Dwh_Skulocationbymonth.Unitssold%Type,
    Markdownunitssold       Stage_Ordm.Dwh_Skulocationbymonth.Markdownunitssold%Type);
  Type Type_Name_1 Is Record(
    Trx_Nbr               Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Trx_Nbr%Type,
    Trx_Line_Item_Seq_Nbr Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Trx_Line_Item_Seq_Nbr%Type,
    Markdwn               Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Mrkdn_Amt%Type,
    Markdup               Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Mrkup_Amt%Type);
  t_Type_Name Type_Name;
  Type Rtl_Tbl Is Table Of t_Type_Name%Type Index By Pls_Integer;
  t_Rtl_Tbl_1 Rtl_Tbl;

  --l_Error_Count Number;
  Ex_Dml_Errors Exception;
  Pragma Exception_Init(Ex_Dml_Errors, -24381);
  l_Error_Count1 Number;
  Type Ref_Cursor Is Ref Cursor;
  C1 Ref_Cursor;

  v_Sql Varchar2(4000);
  --cursor to update values for Markdwn, Markdup columns.

Begin

  --Common cursor to update values for Item_Cost_Amt, Rglr_Unit_Price_Amt

  v_Sql := 'Select /*+ leading (b,a,c) */ distinct a.Sku_Item_Key,
           a.Locationno,
           a.Bsns_Unit_Key,
           a.Item_Cost_Amt,
           a.Rglr_Unit_Price_Amt,
           c.Salescostdollars / c.Unitssold Cost_Cal_Amt,
           (c.Salesretaildollars - c.Markdownretaildollars) /
           (c.Unitssold - c.Markdownunitssold) Rtl_Cal_Amt,
           c.Salesretaildollars / c.Unitssold Rtl_Cal_Amt1,
           c.Unitssold,
           c.Markdownunitssold
      From Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_bkp a,
           Stage_Ordm.Dwh_Voyageschedule        b,
           Stage_Ordm.Dwh_Skulocationbymonth    c
     Where a.Bsns_Unit_Key = b.Voyageno
       And a.Locationno = b.Locationno
       And a.Sku_Item_Key = c.Sku
       And a.Locationno = c.Locationno
       And b.Fiscal_Month_Year = c.Month_Date
       And Unitssold != 0
       And a.sku_item_key=1794844 And a.bsns_unit_key=''3208'' And a.locationno=24';-- Query retrieving one row for these values.

  Open C1 For v_Sql || ' ' || ' and a.Item_Cost_Amt is null';
  Loop
    Begin
      Fetch C1 Bulk Collect
        Into t_Rtl_Tbl_1 Limit 10000;
      Exit When C1%Notfound;
    
      Forall Indx In 1 .. t_Rtl_Tbl_1.Count Save Exceptions
      
        Insert Into Temp
          (Sku_Item_Key, Locationno, Bsns_Unit_Key, Cost_Cal_Amt)
        Values
          (t_Rtl_Tbl_1(Indx).Sku_Item_Key,
           t_Rtl_Tbl_1(Indx).Locationno,
           t_Rtl_Tbl_1(Indx).Bsns_Unit_Key,
           t_Rtl_Tbl_1(Indx).Cost_Cal_Amt);-- Zero rows inserted
    
    Exception
      When Ex_Dml_Errors Then
        l_Error_Count1 := Sql%Bulk_Exceptions.Count;
        Dbms_Output.Put_Line('Number of failures l_Error_Count1: ' ||
                             l_Error_Count1);
        For i In 1 .. l_Error_Count1 Loop
          Dbms_Output.Put_Line('Error: ' || i || ' Array Index: ' || Sql%Bulk_Exceptions(i)
                               .Error_Index || ' Message: ' ||
                               Sqlerrm(-sql%Bulk_Exceptions(i).Error_Code));
        End Loop;
    End;
    Commit;
  End Loop;
  Close C1;

End;



Any help really appreciated.
Re: Bulk Collect & For all in PL/SQL [message #577706 is a reply to message #577705] Tue, 19 February 2013 15:27 Go to previous messageGo to next message
Michel Cadot
Messages: 58641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Replace the whole procedure by a single INSERT ... SELECT ...

Regards
Michel
Re: Bulk Collect & For all in PL/SQL [message #577707 is a reply to message #577706] Tue, 19 February 2013 15:30 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
It has 19 Million data.

The same data set is used with additional filters to update different columns of the same table.
Re: Bulk Collect & For all in PL/SQL [message #577708 is a reply to message #577707] Tue, 19 February 2013 15:44 Go to previous messageGo to next message
BlackSwan
Messages: 22554
Registered: January 2009
Senior Member
TEMP tables are rarely justified in Oracle.
Eliminate, remove & delete the whole & complete EXCEPTION handler.

do as single INSERT ... SELECT statement.

>Its not throwing any exceptions

my car won't go
tell me how to make my car go.

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

http://www.orafaq.com/forum/t/88153/0/
Re: Bulk Collect & For all in PL/SQL [message #577709 is a reply to message #577708] Tue, 19 February 2013 15:48 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Here is the complete proc.

Hope Swan now you understands. Why I said its not throwing any exception.


CREATE OR REPLACE Procedure Sales_Hist_Update_bkp Is
  Type Type_Name Is Record(
    Sku_Item_Key            Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_bkp.Sku_Item_Key%Type,
    Locationno              Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_bkp.Locationno%Type,
    Bsns_Unit_Key           Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_bkp.Bsns_Unit_Key%Type,
    Act_Item_Cost_Amt       Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_bkp.Item_Cost_Amt%Type,
    Act_Rglr_Unit_Price_Amt Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_bkp.Rglr_Unit_Price_Amt%Type,
    Cost_Cal_Amt            Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_bkp.Rglr_Unit_Price_Amt%Type,
    Rtl_Cal_Amt             Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_bkp.Rglr_Unit_Price_Amt%Type,
    Rtl_Cal_Amt1            Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_bkp.Rglr_Unit_Price_Amt%Type,
    Unitssold               Stage_Ordm.Dwh_Skulocationbymonth.Unitssold%Type,
    Markdownunitssold       Stage_Ordm.Dwh_Skulocationbymonth.Markdownunitssold%Type);
  Type Type_Name_1 Is Record(
    Trx_Nbr               Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_bkp.Trx_Nbr%Type,
    Trx_Line_Item_Seq_Nbr Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_bkp.Trx_Line_Item_Seq_Nbr%Type,
    Markdwn               Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_bkp.Mrkdn_Amt%Type,
    Markdup               Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_bkp.Mrkup_Amt%Type);
  t_Type_Name  Type_Name;
  t_Type_Name1 Type_Name_1;
  Type Rtl_Tbl Is Table Of t_Type_Name%Type Index By Pls_Integer;
  Type Rtl_Tbl_1 Is Table Of t_Type_Name1%Type Index By Pls_Integer;
  t_Rtl_Tbl_1 Rtl_Tbl;
  t_Rtl_Tbl_2 Rtl_Tbl;
  t_Rtl_Tbl_3 Rtl_Tbl;
  t_Rtl_Tbl_4 Rtl_Tbl_1;
  --l_Error_Count Number;
  Ex_Dml_Errors Exception;
  Pragma Exception_Init(Ex_Dml_Errors, -24381);
  l_Error_Count1 Number;
  l_Error_Count2 Number;
  l_Error_Count3 Number;
  l_Error_Count4 Number;
  Type Ref_Cursor Is Ref Cursor;
  C1    Ref_Cursor;
  C2    Ref_Cursor;
  C3    Ref_Cursor;
  v_Sql Varchar2(4000);
  --cursor to update values for Markdwn, Markdup columns.
  Cursor C4 Is
    Select t.Trx_Nbr,
           t.Trx_Line_Item_Seq_Nbr,
           Case
             When t.Act_Unit_Price_Amt < t.Rglr_Unit_Price_Amt Then
              (t.Rglr_Unit_Price_Amt - t.Act_Unit_Price_Amt) * t.Qty
             Else
              Null
           End Markdwn,
           Case
             When t.Act_Unit_Price_Amt > t.Rglr_Unit_Price_Amt Then
              (t.Act_Unit_Price_Amt - t.Rglr_Unit_Price_Amt) * t.Qty
             Else
              Null
           End Markdup
      From Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_bkp t;
Begin

  --Common cursor to update values for Item_Cost_Amt, Rglr_Unit_Price_Amt

  v_Sql := 'Select /*+ leading (b,a,c) */ distinct a.Sku_Item_Key,
           a.Locationno,
           a.Bsns_Unit_Key,
           a.Item_Cost_Amt,
           a.Rglr_Unit_Price_Amt,
           c.Salescostdollars / c.Unitssold Cost_Cal_Amt,
           (c.Salesretaildollars - c.Markdownretaildollars) /
           (c.Unitssold - c.Markdownunitssold) Rtl_Cal_Amt,
           c.Salesretaildollars / c.Unitssold Rtl_Cal_Amt1,
           c.Unitssold,
           c.Markdownunitssold
      From Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_bkp a,
           Stage_Ordm.Dwh_Voyageschedule        b,
           Stage_Ordm.Dwh_Skulocationbymonth    c
     Where a.Bsns_Unit_Key = b.Voyageno
       And a.Locationno = b.Locationno
       And a.Sku_Item_Key = c.Sku
       And a.Locationno = c.Locationno
       And b.Fiscal_Month_Year = c.Month_Date
       And Unitssold != 0
       And a.sku_item_key=1794844 And a.bsns_unit_key=''3208'' And a.locationno=24';

 Open C1 For v_Sql || ' ' || ' and a.Item_Cost_Amt is null';
  Loop
    Begin
      Fetch C1 Bulk Collect
        Into t_Rtl_Tbl_1 Limit 10000;
      Exit When C1%Notfound;

      Forall Indx In 1 .. t_Rtl_Tbl_1.Count Save Exceptions

                         --Update Dwi_Rtl_Sls_Retrn_Line_bkp for Item_Cost_Amt values from cusror where Item_Cost_Amt Is Null
                          
        Update Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_bkp t
           Set t.Item_Cost_Amt = t_Rtl_Tbl_1(Indx).Cost_Cal_Amt
         Where t.Sku_Item_Key = t_Rtl_Tbl_1(Indx).Sku_Item_Key
           And t.Locationno = t_Rtl_Tbl_1(Indx).Locationno
           And t.Bsns_Unit_Key = t_Rtl_Tbl_1(Indx).Bsns_Unit_Key
           And t.Item_Cost_Amt Is Null;

    Exception
      When Ex_Dml_Errors Then
        l_Error_Count1 := Sql%Bulk_Exceptions.Count;
        Dbms_Output.Put_Line('Number of failures l_Error_Count1: ' ||
                             l_Error_Count1);
        For i In 1 .. l_Error_Count1 Loop
          Dbms_Output.Put_Line('Error: ' || i || ' Array Index: ' || Sql%Bulk_Exceptions(i)
                               .Error_Index || ' Message: ' ||
                               Sqlerrm(-sql%Bulk_Exceptions(i).Error_Code));
        End Loop;
    End;
    Commit;
  End Loop;
  Close C1;

Open C2 For v_Sql || ' ' || ' and c.Unitssold > c.Markdownunitssold And  a.Rglr_Unit_Price_Amt Is Null';
  Loop
    Begin
      Fetch C2 Bulk Collect
        Into t_Rtl_Tbl_2 Limit 10000;
      Exit When C2%Notfound;

      Forall Indx In 1 .. t_Rtl_Tbl_2.Count Save Exceptions

                         --Update Dwi_Rtl_Sls_Retrn_Line_bkp for Rglr_Unit_Price_Amt values from cusror where Rglr_Unit_Price_Amt Is Null and
                         --Unitssold > Markdownunitssold of Dwh_Skulocationbymonth table
                          
                          Update Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_bkp t
           Set t.Rglr_Unit_Price_Amt = t_Rtl_Tbl_2(Indx).Rtl_Cal_Amt
         Where t.Sku_Item_Key = t_Rtl_Tbl_2(Indx).Sku_Item_Key
           And t.Locationno = t_Rtl_Tbl_2(Indx).Locationno
           And t.Bsns_Unit_Key =t_Rtl_Tbl_2(Indx).Bsns_Unit_Key
           And t.Rglr_Unit_Price_Amt Is Null;

    Exception
      When Ex_Dml_Errors Then
        l_Error_Count2 := Sql%Bulk_Exceptions.Count;
        Dbms_Output.Put_Line('Number of failures l_Error_Count2: ' ||
                             l_Error_Count2);
        For i In 1 .. l_Error_Count2 Loop
          Dbms_Output.Put_Line('Error: ' || i || ' Array Index: ' || Sql%Bulk_Exceptions(i)
                               .Error_Index || ' Message: ' ||
                               Sqlerrm(-sql%Bulk_Exceptions(i).Error_Code));
        End Loop;
    End;
    Commit;
  End Loop;
  Close C2;

  Open C3 For v_Sql || ' ' || ' and  c.Unitssold <= c.Markdownunitssold And  a.Rglr_Unit_Price_Amt Is Null';
  Loop
    Begin
      Fetch C3 Bulk Collect
        Into t_Rtl_Tbl_3 Limit 10000;
      Exit When C3%Notfound;

      Forall Indx In 1 .. t_Rtl_Tbl_3.Count Save Exceptions

                         --Update Dwi_Rtl_Sls_Retrn_Line_bkp for Rglr_Unit_Price_Amt values from cusror where Rglr_Unit_Price_Amt Is Null and
                         --Unitssold <= Markdownunitssold of Dwh_Skulocationbymonth table
                          Update Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_bkp t
           Set t.Rglr_Unit_Price_Amt = t_Rtl_Tbl_3(Indx).Rtl_Cal_Amt
         Where t.Sku_Item_Key = t_Rtl_Tbl_3(Indx).Sku_Item_Key
           And t.Locationno = t_Rtl_Tbl_3(Indx).Locationno
           And t.Bsns_Unit_Key = t_Rtl_Tbl_3(Indx).Bsns_Unit_Key
           And t.Rglr_Unit_Price_Amt Is Null;

    Exception
      When Ex_Dml_Errors Then
        l_Error_Count3 := Sql%Bulk_Exceptions.Count;
        Dbms_Output.Put_Line('Number of failures l_Error_Count3: ' ||
                             l_Error_Count3);
        For i In 1 .. l_Error_Count3 Loop
          Dbms_Output.Put_Line('Error: ' || i || ' Array Index: ' || Sql%Bulk_Exceptions(i)
                               .Error_Index || ' Message: ' ||
                               Sqlerrm(-sql%Bulk_Exceptions(i).Error_Code));
        End Loop;
    End;
    Commit;
  End Loop;
  Close C3;

  Open C4;
  Loop
    Begin
      Fetch C4 Bulk Collect
        Into t_Rtl_Tbl_4 Limit 10000;
      Exit When C4%Notfound;
      --Update markdwn and markup values for table Dwi_Rtl_Sls_Retrn_Line_bkp from cursor 2.
      --This update is done after updation of Item_Cost_Amt, Rglr_Unit_Price_Amt values.
      Forall Indx In 1 .. t_Rtl_Tbl_4.Count Save Exceptions

                          Update Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_bkp t
           Set t.Mrkdn_Amt = t_Rtl_Tbl_4(Indx).Markdwn,
               t.Mrkup_Amt = t_Rtl_Tbl_4(Indx).Markdup
         Where t.Trx_Nbr = t_Rtl_Tbl_4(Indx).Trx_Nbr
           And t.Trx_Line_Item_Seq_Nbr = t_Rtl_Tbl_4(Indx).Trx_Line_Item_Seq_Nbr;

    Exception
      When Ex_Dml_Errors Then
        l_Error_Count4 := Sql%Bulk_Exceptions.Count;
        Dbms_Output.Put_Line('Number of failures l_Error_Count4: ' ||
                             l_Error_Count4);
        For i In 1 .. l_Error_Count4 Loop
          Dbms_Output.Put_Line('Error: ' || i || ' Array Index: ' || Sql%Bulk_Exceptions(i)
                               .Error_Index || ' Message: ' ||
                               Sqlerrm(-sql%Bulk_Exceptions(i).Error_Code));
        End Loop;
    End;
    Commit;
  End Loop;
  Close C4;

End;


[Updated on: Tue, 19 February 2013 15:49]

Report message to a moderator

Re: Bulk Collect & For all in PL/SQL [message #577710 is a reply to message #577709] Tue, 19 February 2013 16:01 Go to previous messageGo to next message
BlackSwan
Messages: 22554
Registered: January 2009
Senior Member
Realize that since we don't have your tables or data, we can't run, test or improve posted code.
Re: Bulk Collect & For all in PL/SQL [message #577711 is a reply to message #577710] Tue, 19 February 2013 16:04 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
That is the reason in my previous post i have given a simplified piece of code and mentioned that the select query is returning one row.

But the same is not inserted in temp table.
Re: Bulk Collect & For all in PL/SQL [message #577714 is a reply to message #577705] Tue, 19 February 2013 17:35 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
I tried to debug the code and found below things.

Its not fetching cursor c1 records into t_rtl_tbl_1.

But when i checked the cursor sql it has 8 rows.

Fetch C1 Bulk Collect
        Into t_Rtl_Tbl_1 Limit 10000;
      Exit When C1%Notfound;


Any help why cursor records are not able to fetch into t_rtl_tbl_1 using bulk collect.

Thanks in advance
Re: Bulk Collect & For all in PL/SQL [message #577716 is a reply to message #577714] Tue, 19 February 2013 17:51 Go to previous messageGo to next message
cookiemonster
Messages: 10860
Registered: September 2008
Location: Rainy Manchester
Senior Member
If the cursor fetches less than the limit then cursor%notfound becomes true.
So it is finding data, it's just exiting the loop before you can do anything with it.
Re: Bulk Collect & For all in PL/SQL [message #577717 is a reply to message #577716] Tue, 19 February 2013 17:53 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Thanks Cookiemaster for your reply.

So its better to place this statement at the end of the block right.
Re: Bulk Collect & For all in PL/SQL [message #577719 is a reply to message #577717] Tue, 19 February 2013 18:10 Go to previous messageGo to next message
cookiemonster
Messages: 10860
Registered: September 2008
Location: Rainy Manchester
Senior Member
It's better to rewrite the whole thing as two update statements as well told you in this thread
Failing that use:
EXIT WHEN t_Rtl_Tbl_4.Count = 0
Re: Bulk Collect & For all in PL/SQL [message #577720 is a reply to message #577719] Tue, 19 February 2013 18:47 Go to previous message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Thanks for your reply.

Its a iterative process. how can we write whole proc into two update statements.

Each cursor has its own filter criteria and its a iterative process.


In first process its not updated only those records are processed in second process.

Previous Topic: Append collection variable
Next Topic: error
Goto Forum:
  


Current Time: Fri Aug 01 08:21:42 CDT 2014

Total time taken to generate the page: 0.26335 seconds