Oracle – How to have an out ref cursor parameter in a stored procedure

oraclescoping

The standard way that our applications pass information from oracle stored procedures to the oracle .net provider is via an out ref cursor parameter.

In the past all of our stored procedures used to be in packages and had something like this:

CREATE OR REPLACE PACKAGE test_package IS
   TYPE refcur IS REF CURSOR;
   PROCEDURE get_info ( o_cursor            OUT      refcur );
END test_package;
/
CREATE OR REPLACE PACKAGE BODY test_package IS
   PROCEDURE get_info ( o_cursor            OUT      refcur ) AS
   BEGIN
     OPEN o_cursor FOR
       SELECT * FROM v$database;
   END get_info;
END test_package;
/

Now I would like to move that get_info procedure out of the package and into a regular procedure but don't know what to do to get the refcur type. How do I create it outside the package scope?

CREATE OR REPLACE TYPE refcur IS REF CURSOR;

doesn't work.

Best Answer

I can't test it here (no Oracle) but you can do:

create or replace procedure get_info(p_cursor out sys_refcursor)
is
begin
  open p_cursor for
    select *
    from   v$database; 
end;
/

In Oracle 9 and higher it is no longer needed to declare TYPE result_crsr IS REF CURSOR

Use sys_refcursor instead.

Related Topic