Sunday 15 May 2011

How to avoid ORA-01000: maximum open cursors exceeded

   Sometime during my work I need to convert the result of SQL query to XML format .Oracle provides many way to do this but this is not the goal of this post.

Since XML has the hierarchical structure, SQL should be also generated in this was. The way to write hierarchical SQL is by using cursors

For example:

   1: select ooha.*,
   2:       XX_OM_PICK_RELEASE_EKOL_TRY.Get_Address_Details(oola.ship_to_org_id,'SHIP_TO')    Ship_To_Details
   3: from  oe_order_headers    ooha,
   4:      oe_order_lines       oola
   5:      WHERE 1= 1 
   6:      and ooha.header_id   = oola.header_id 

Just a simple query from order management tables. XX_OM_PICK_RELEASE_EKOL_TRY.Get_Address_Details is a cursor that returns ship to address information.


Result XML will look something like this



   1: <orders>
   2:    <order_number>...</order_number>
   3:    <ship_to_details>
   4:       <country>...</country>
   5:    </ship_to_details>
   6:    .
   7:    .
   8:    .
   9: <orders>

Function is defined in the following way



   1: function Get_Address_Details( p_org_id        in number, 
   2:                               p_site_use_code in varchar2) return refcursor is 
   3:                                   
   4:       address_cursor refcursor;                           
   5:        begin 
   6:       open address_cursor for 
   7:          select   
   8:             nvl(rc.customer_number,'NULL')             Line_Reference1, 
   9:             nvl(rc.customer_number,'NULL')             Customer_number, 
  10:             nvl(rc.customer_name,'NULL')               contact_name, 
  11:             nvl(ra.country,'NULL')                     country, 
  12:             nvl(ra.address1,'NULL')                    address_line1, 
  13:             nvl(ra.address2,'NULL')                    address_line2, 
  14:             nvl(ra.city,'NULL')                        city, 
  15:             nvl(ra.postal_code,'NULL')                 post_code 
  16:          from 
  17:             ra_customers                  rc, 
  18:             ra_addresses_all              ra, 
  19:             ra_site_uses_all              rsu 
  20:          where 
  21:                 rc.customer_id          = ra.customer_id 
  22:             and ra.address_id           = rsu.address_id 
  23:             and rsu.site_use_code       = p_site_use_code--'BILL_TO' 
  24:             and rsu.site_use_id         = p_org_id ;                     
  25:        return address_cursor;                         
  26:      exception 
  27:       when others then 
  28:          dbms_output.put_line('Problem with fetch address data for  '||p_org_id||' and '||p_site_use_code ||' !!! '||sqlerrm);                                 
  29:      end  Get_Address_Details; 

As you can see , this function returns refcursor with address data


  In our system there is several millions orders, for each order, this function is called and refcursor created and returned to the major SQL. And this is exactly the problem!! All refcursors stay open until whole SQL executed and closed only after  we get the result from the query. So there is no wander that very soon we started to get ORA-01000 error. We didn’t want to change DB configuration and increase cursor limit. This will only delay the problem till the number of order will be increased and believe me, it will.


  The solution is simple. As I tolled, the problem is because refcursor is not being closed. So, lets redesign the SQL so this problem will not happen it the first place


And a way to do it ,is by converting refcursor to array.


Define array :



   1: create type xx_address_details as object 
   2: ( 
   3: Line_Reference1 varchar2(1000), 
   4: Customer_number  varchar2(1000), 
   5: contact_name varchar2(1000), 
   6: country  varchar2(1000), 
   7: address_line1  varchar2(1000), 
   8: address_line2 varchar2(1000), 
   9: city  varchar2(1000), 
  10: post_code  varchar2(1000) 
  11: ) 
  12: / 
  13: create type xx_address_details_tab as table of xx_address_details

Array holds the same fields as original refcursor


Now rewrite XX_OM_PICK_RELEASE_EKOL_TRY.Get_Address_Details  function



   1: function Get_Address_Details( p_org_id        in number, 
   2:                                  p_site_use_code in varchar2) return xx_address_details_tab is 
   3:       
   4:                                       
   5:       address_details xx_address_details_tab:=xx_address_details_tab();                           
   6:       cursor adrress_cursor is (select   
   7:             nvl(rc.customer_number,'NULL')             Line_Reference1, 
   8:             nvl(rc.customer_number,'NULL')             Customer_number, 
   9:             nvl(rc.customer_name,'NULL')               contact_name, 
  10:             nvl(ra.country,'NULL')                     country, 
  11:             nvl(ra.address1,'NULL')                    address_line1, 
  12:             nvl(ra.address2,'NULL')                    address_line2, 
  13:             nvl(ra.city,'NULL')                        city, 
  14:             nvl(ra.postal_code,'NULL')                 post_code 
  15:          from 
  16:             ra_customers                  rc, 
  17:             ra_addresses_all              ra, 
  18:             ra_site_uses_all              rsu 
  19:          where 
  20:                 rc.customer_id          = ra.customer_id 
  21:             and ra.address_id           = rsu.address_id 
  22:             and rsu.site_use_code       = p_site_use_code--'BILL_TO' 
  23:             and rsu.site_use_id         = p_org_id ); 
  24:        begin 
  25:           for rec in adrress_cursor loop 
  26:         address_details.extend; 
  27:         address_details(address_details.last) := xx_address_details(rec.Line_Reference1, 
  28:                                                                     rec.Customer_number, 
  29:                                                                     rec.contact_name, 
  30:                                                                     rec.country, 
  31:                                                                     rec.address_line1, 
  32:                                                                     rec.address_line2, 
  33:                                                                     rec.city, 
  34:                                                                     rec.post_code); 
  35:  
  36:      end loop; 
  37:          return address_details; 
  38:      exception 
  39:       when others then 
  40:          dbms_output.put_line('Problem with fetch address data for  '||p_org_id||' and '||p_site_use_code ||' !!! '||sqlerrm);                                 
  41:      end  Get_Address_Details; 

Major changes:


Declaration of array


5: address_details xx_address_details_tab:=xx_address_details_tab()


Function return type:


p_site_use_code in varchar2) return xx_address_details_tab 


Note that result of the query is not returned . Instead, it is filled into array and array is the object that returned.


Note the loop on the rec cursor. It’s job to fill data into array. After the loop is closed cursor is also closed and doesn’t take place in memory any more


That is all. Note also, we didn’t change anything in the original SQL statement.

1 comment:

  1. hola amigo muchas gracias por tu gran trabajo me sirvio muchisimo

    ReplyDelete