Home » SQL & PL/SQL » SQL & PL/SQL » returning ref cursor in procedures
returning ref cursor in procedures [message #252950] Fri, 20 July 2007 20:29 Go to next message
Messages: 16
Registered: September 2006
Junior Member
Hi all,

I just want to get everybody's opinion of the way we are coding at my work place. We are creating reports that call procedures which return ref cursors. Here is an example of what we do:
procedure p_Report (in_parameter_id    in  number,
                    out_result_set     out ref cursor)

    open out_result_set for
       select   *
       from     aaa_table
       where    parameter_id = in_parameter_id;


Now I read in one of the O'Reilly books (Oracle PL/SQL Programming) that you should always close cursors and that if you don't, you will get a memory leak and it will really degrade performance.

If this is true, since I don't explicitly close the ref cursor (because the report still needs the data from the cursor), is there something I can do to close it once the data is passed to the report?

Or is there a better way? Please share your thoughts. Any positive comments are always appreciated.

Re: returning ref cursor in procedures [message #252975 is a reply to message #252950] Sat, 21 July 2007 02:24 Go to previous messageGo to next message
Messages: 49
Registered: June 2006
Location: Romania
Try to close your cursor an see if you loose data in your report (i think not)...if you loose it try to close the cursor when you exit your report (depending on you programing environment you may have an event "OnExit", "OnClose".....and you may close the cursor when the event triggers)
Re: returning ref cursor in procedures [message #252976 is a reply to message #252950] Sat, 21 July 2007 02:29 Go to previous message
Michel Cadot
Messages: 63912
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As your procedure returns a ref cursor variable, you can use this variable to close the cursor.
In SQL*Plus:
SQL> var v refcursor
SQL> exec p_report(1,:v);
SQL> -- work with output cursor --
SQL> -- Finally close the cursor
SQL> begin
  2    if :v%isopen then close :v; end if;
  3  end;
  4  /

PL/SQL procedure successfully completed.

Finally, the cursor is closed at the end of the session.

Previous Topic: dynamic cursor
Next Topic: How to limit the ranking
Goto Forum:

Current Time: Sun Oct 23 13:36:46 CDT 2016

Total time taken to generate the page: 0.08363 seconds