Returning multiple ref cursors from Oracle procedure to Java

Tags: ,



In our web application we have 18 screens in a module.
Our user wants all the data of the 18 screens in one page so that they can print the entire data at once.

So, I wrote an Oracle procedure which fetches the data of all 18 screens (from 20 – 22 tables).
This Oracle procedure returns 13 cursors to my Java program.

Performance of the page is good and I am getting the desired result.

However, would returning that many cursors to Java create any problems?

Answer

The maximum number of opened cursors for a single session is governed by the OPEN_CURSORS parameter (default value 50, often extended in the hundreds).

If you close your cursors properly after you have finished with fetching them, as suggested by @Polppan, you should have no problem with 18 simultaneously opened cursors.

Since a cursor is just a pointer to a query, there is also no problem with returning 18 of them at once over the network.



Source: stackoverflow