Returning multiple ref cursors from Oracle procedure to Java

Question

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 1

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.