Skip to content

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).