views:

2604

answers:

3

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.

A: 
TYPE result_crsr IS REF CURSOR;

An example of anonymous block of SQL using a ref cursor:

DECLARE
   TYPE result_crsr IS REF CURSOR;
   crsr_test_result   result_crsr;
BEGIN


   OPEN crsr_test_result FOR
        SELECT * from user_objects;
     ? := crsr_test_result;
END;
Brian
? := crsr_test_result line is because a Java JDBC application is calling this block of sql and getting the ref cursor. Its my short-hand technique when I want to accomplish something with pl/sql from java without placing the logic in a stored procedure. But it just as easily could be a stored procedure.
Brian
I dont think it can be since there has to be a parameter of type refcur which is outside the scope you create the type in.
George Mauer
A: 

Try this:

CREATE OR REPLACE PROCEDURE get_info(o_cursor OUT sys_refcursor) IS
BEGIN
  OPEN o_cursor FOR SELECT * FROM dual;
END;
/

Your question has raises two important questions:

1) You say it "doesn't work". I take that to mean that Oracle is returning an exception when you execute the statement. What is the error message Oracle is returning? It should start with ORA-nnnnn and be followed by some text.

2) What purpose would be served by moving a PROCEDURE out of a PACKAGE? There are a few more lines of code with the package, the procedure signature repeated in both the package spec and the package body, but having the procedure within a package provides several important benefits.

spencer7593
Q1: ERROR line 1, col 23, ending_line 1, ending_col 25, Found 'REF', Expecting: ARRAY OBJECT OPAQUE TABLE VARRAY VARYINGQ2 - there are only a few stored procedures used by this application and they're all being moved to a new oracle user seems unnecessary to have packages get involved
George Mauer
+5  A: 

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.

tuinstoel