Calling Oracle package procedures which return ref cursors in straight PL/SQL

Question

I've got an Oracle 10g database which is accessed from an ASP.NET application. Although I've used SQL Server heavily in many different aspects and Oracle for querying and reporting, this is my first time using Oracle as the OLTP database for an application.

The database-level procedures in the packages are typically of the form:

-- TYPE refcur IS REF CURSOR;

PROCEDURE get_some_stuff(o_cursor OUT refcur, p_param1 IN INTEGER, p_param2 IN INTEGER) IS
BEGIN
    OPEN o_cursor FOR
    SELECT whatever
    FROM whatever
END

I assume these are done this way for the benefit of the ADO.NET layer able to use the cursor from the output param and it is my understanding that this is the acceptable best practice for calling Oracle procs from .NET.

In SQL Server, for example, we don't have explicit ref cursors, if a proc returns a result set (or several result sets), that's accessible as an output result set in both ADO.NET and SSMS, and you can simply test the SPs by doing EXEC spname param1, param2.

The problem I'm having is that I don't know how to call these directly in SQL in Toad, for example, to be able to test changes at the PL/SQL level first before going to the app. I'm very used to being able to exercise and even re-mix stored procs and functions in SQL Server to be able to refactor the database interface layer without affecting the external interface to application-level code.

Solution 1

Best solution was found in the link which OMG Ponies provided:

Easiest method to test an Oracle Stored Procedure

and here:

http://heather.koyuk.net/refractions/?p=343

Solution 2

look at the link that OMG Ponies posted, but what you can do is

    var x refcursor;


declare

    PROCEDURE GET_SOME_STUFF(O_CURSOR OUT SYS_REFCURSOR, P_PARAM1 IN NUMBER, P_PARAM2 IN NUMBER) IS
    BEGIN
         OPEN O_CURSOR FOR
         SELECT LEVEL, p_param1 ,P_PARAM2  FROM DUAL CONNECT BY LEVEL < 3;
    END ;

BEGIN
GET_SOME_STUFF(:x , 5, 10); 
END;
/

PRINT X;

you pretty much just wrap it in a anonymous block ad it will run. I use SQL Developer (highly recommmend, free with plenty of support) or SQL plus so I cannot help with TOAD, but I would expect it to be the same. In SQL Developer (and in SQL Navigator if memory serves correct) you can simply right click the package/method you wish and it will create the script for you.
in toad and navigator I believe you may be able to get the ref cursor in a pretty grid while in developer you get it in text.

SQL Developer you can unit test as well

Solution 3

Try this:

DECLARE
  aCursor       SYS_REFCURSOR;
  someVariable  SOME_TYPE;

  FUNCTION SOME_FUNC_RETURNING_A_CURSOR RETURN SYS_REFCURSOR IS
    csrLocal  SYS_REFCURSOR;
  BEGIN
    OPEN csrLocal FOR SELECT whatever FROM wherever;

    RETURN csrLocal;
  END SOME_FUNC_RETURNING_A_CURSOR;

BEGIN
  aCursor := SOME_FUNC_RETURNING_A_CURSOR;

  WHILE TRUE LOOP
    FETCH aCursor INTO someVariable;
    EXIT WHEN aCursor%NOTFOUND;

    ...do whatever with variables...
  END LOOP;

  COMMIT;
END;

Share and enjoy.

Solution 4

I found an easier way to this ...try it (This will also generate script for you)

In the Procedure Editor, load your procedure. Click on the lightning bolt to execute and you will see the Set Parameters window, which is also available via the button on the Proc Editor toolbar that has an image similar to (...) on it, next to the lightning bolt. Click on the output options button and you'll see your options. If this is a weak ref cursor then you must use the in-memory grid option. Results go to the cursor results tab at the bottom of the PE after you execute.

http://toad.10940.n7.nabble.com/display-ref-cursor-in-toad-td1427.html

View more solutions