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
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
Function is defined in the following way
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 :
Array holds the same fields as original refcursor
Now rewrite XX_OM_PICK_RELEASE_EKOL_TRY.Get_Address_Details function
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.