How to get nested cursor from a stored procedure – Java &Oracle

I have a stored procedure that returns a cursor and the cursor contains another cursor (col_ch in the bellow example). It is something like this (as I’m seeing it in my Java code)

PROCEDURE my_proc ( i_val IN VARCHAR ,i_cont IN INTEGER ,o_out OUT CURSOR ); On my Java app I’m seeing in debug values from o_out

• id
• t_id
• col_ch of type OracleResultSetImpl

How can I get the value of col_ch ? I have a simple bean that extends StoredProcedure and I’ve declared two input params and one output param(the cursor : o_result that when in debug shows the mentioned fields. Thank you and appreciate the help

Answer

1. Open the outer cursor;

2. Read the first row from the outer cursor.

3. Read the inner cursor from the column of that row;

   • a) I believe it should already be open, but I may be mistaken and you need to open it;
   • b) fetch the next row from the inner cursor;
   • c) read the columns from that row of the inner cursor;
   • d) repeat and read more rows and columns from the inner cursor until you’ve processed all the rows in that inner cursor; then

4. Repeat reading the next row from the outer cursor and iterating over its inner cursor until you’ve processed all the rows; and

5. Finally, close the outer cursor (all the inner cursors will be implicitly closed as well).