trouble defining weakly defined ref cursor

Question

Hi all,

I'm attempting to write a stored proc that takes in a number, n, and returns the first n results for a given query, exclusively locking those n rows. I'm a little new to SQL and I'm having a bit of difficulty matching data types correctly.

My package spec looks like this:

PACKAGE package IS
   Type out_result_type is REF CURSOR;
   PROCEDURE stored_proc
     (in_n IN NUMBER DEFAULT 10, 
        out_list IN OUT out_result_type);

I then define the cursor in the procedure body, like so:

CURSOR OUT_RESULT_TYPE IS
        SELECT a.id
            FROM schema.table a
            WHERE (some conditions) AND rownum <= in_n;

A bit later on I then try to extract the results of the cursor into the output variable:

OPEN OUT_RESULT_TYPE;
FETCH OUT_RESULT_TYPE INTO out_list; -- error on this line
CLOSE OUT_RESULT_TYPE;

But alas this code doesn't compile; oracle complains that out_list has already been defined with a conflicting data type. Any idea how I can resolve this issue? It's driving me crazy!

Thanks in advance.

Answer 1

CREATE OR REPLACE PACKAGE pkg_test
AS
        TYPE    tt_cur IS REF CURSOR;
        PROCEDURE prc_cur (retval OUT tt_cur);
END;

CREATE OR REPLACE PACKAGE BODY pkg_test
AS
        PROCEDURE prc_cur (retval OUT tt_cur)
        AS
        BEGIN
                OPEN    retval
                FOR
                SELECT  *
                FROM    dual;
        END;
END;

If you want to lock, use:

CREATE OR REPLACE PACKAGE BODY pkg_test
AS
        PROCEDURE prc_cur (retval OUT tt_cur)
        AS
        BEGIN
                OPEN    retval
                FOR
                SELECT  a.id
                FROM    schema.table a
                WHERE   (some conditions)
                        AND rownum <= in_n
                ORDER BY
                        column
                -- Never forget ORDER BY!
                FOR UPDATE;
        END;
END;

Answer 2

Your out_list must be of wrong type. Consider (script run on 10.2.0.3):

CREATE TABLE t AS SELECT ROWNUM ID FROM all_objects WHERE ROWNUM <= 100;

CREATE OR REPLACE PACKAGE cursor_pck AS
   TYPE out_result_type is REF CURSOR;
   PROCEDURE stored_proc (p_in IN NUMBER DEFAULT 10, 
                          p_out_list IN OUT out_result_type);
END cursor_pck;
/

If you want to select and lock the rows at the same time you would use the FOR UPDATE clause:

CREATE OR REPLACE PACKAGE BODY cursor_pck AS
   PROCEDURE stored_proc (p_in IN NUMBER DEFAULT 10,  
                          p_out_list IN OUT out_result_type) IS
   BEGIN
      OPEN p_out_list FOR SELECT a.id FROM t a WHERE ROWNUM <= p_in FOR UPDATE;
   END stored_proc;
END cursor_pck;
/

With the following setup, you will call the procedure like this:

SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
  2     l_cursor cursor_pck.out_result_type;
  3     l_id t.id%TYPE;
  4  BEGIN
  5     cursor_pck.stored_proc(3, l_cursor);
  6     LOOP
  7        FETCH l_cursor INTO l_id;
  8        EXIT WHEN l_cursor%NOTFOUND;
  9        dbms_output.put_line(l_id);
 10     END LOOP;
 11  END;
 12  /

1
2
3

PL/SQL procedure successfully completed

Answer 3

Two remarks:

1. A cursor doesn't lock.
2. You don't have to do Type out_result_type is REF CURSOR;, use default type sys_refcursor. See here: http://stackoverflow.com/questions/869055/oracle-how-to-have-an-out-ref-cursor-parameter-in-a-stored-procedure/869730#869730

Answer 4

This is not going to work the way it's written, because

1. out_list expects a cursor, not a cursor result.
2. The name out_result_type is already used for a type, so you can't redefine it to be a cursor in the same scope.

Answer 5

Oracle provides a pre-defined weak reference cursor: sys_refcursor. In usage it would look like:

CREATE OR REPLACE PACKAGE pkg_test
AS
    PROCEDURE prc_cur (p_retval OUT sys_refcursor,
                       p_lookup IN  VARCHAR2);
END pkg_test;


CREATE OR REPLACE PACKAGE BODY pkg_test 
AS
    PROCEDURE prc_cur(p_retval OUT sys_refcursor
                      p_lookup IN  VARCHAR2) 
    IS
    BEGIN
        OPEN retval FOR SELECT a.value 
                          FROM tblname a 
                         WHERE a.id <= p_lookup;
    END prc_cur;
END pkg_test;

This saves you the trouble of needing to declare a type. The sys_refcursor is a pointer to a result set from an open cursor. If you are familiar with Java, it's the same concept as the java.sql.ResultSet object which provides a way to get at the results of a query.