Returning multiple ref cursors from an Oracle procedure in Java

Question

In our web application, we have 18 screens in the module. Our user wants all the data from 18 screens to be on one page so that they can print all the data at once.

So, I wrote an Oracle procedure that retrieves data from all 18 screens (from 20 to 22 tables). This Oracle procedure returns 13 cursors to my Java program.

The performance of the page is good, and I get the desired result.

However, would it return that many cursors for Java pose any problems?

Answer 1

The maximum number of open cursors for one session is determined by the OPEN_CURSORS parameter (the default value is 50, often expanding into hundreds).

If you close your cursors correctly after you have finished extracting them, as suggested by @Polppan , you should have no problem with 18 cursors open at the same time.

Since the cursor is just a pointer to the request, there are also no problems returning 18 of them directly over the network.