Do I need to explicitly close the reference cursor?

, ,
1

Hi,

I have an Oracle stored procedure that returns a reference cursor, then, I created a signature adapter service to invoke it.

Do I need to explicitly close the cursor?

Thanks in advance

2

Hello westisland,

We too have an Oracle store proc that returns a reference cursor and we use it in a JDBC adapter service. The adapter service is a storedProcedureWithSignature. We explicitly close the cursor inside the Oracle proc. See the pseudo code below for an example. I hope this helps.

create or replace PACKAGE MYPACKAGE AS
type refcur is ref cursor;
procedure get_clue_list( p_clueCursor out refcur);
END MYPACKAGE;

create or replace PACKAGE BODY MYPACKAGE AS
procedure get_clue_list( p_clueCursor out refcur) AS
BEGIN
open p_clueCursor for select field1, field2 from table1;
return;
CLOSE p_clueCursor;
END get_clue_list;
END MYPACKAGE;

3

Thanks so much Dcova! It works very well.

4

I’m not sure that this actually closes the cursor. From what I know, the return instruction is the last one executed and everything that comes after is skipped.
You may end up with oracle error “Maximum open cursors exceeded”