Oracle Tip: Close your Ref Cursor in PL/SQL

Hello Everyone,

There is a way in Oracle that you can close your Ref Cursor in PL/SQL store procedure and still able to retain the value into .NET application. This way you don’t leave unclosed cursor on server side.
------------------------------
Sample Code -- Start
------------------------------

PROCEDURE test(crs_out_cursor out sys_refcursor) AS
crs_error_local sys_refcursor;
mydata sys_refcursor;
rr emp%rowtype;
BEGIN
open crs_error_local for
select * from emp_table;
crs_out_cursor := crs_error_local;

loop
fetch mydata into rr;
exit when mydata%notfound;
end loop;

close crs_error_local;
crs_out_cursor := mydata;
----------------------------------------------------------------------------
--EXCEPITION HANDLER
----------------------------------------------------------------------------
EXCEPTION
when others
then
END test;
-----------------------------
Sample Code -- End
-----------------------------

Enjoy the tip!