Calling Oracle Package Procedures That Return Cursors in Direct PL / SQL

Question

I have an Oracle 10g database that is accessed from an ASP.NET application. Although I used SQL Server in many different ways and Oracle for queries and reports, this is my first experience using Oracle as an OLTP database for an application.

Database-level procedures in packages usually take 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 this is done for the benefit of the ADO.NET layer, which is able to use the cursor from the output parameter, and I understand that this is an acceptable best practice for invoking Oracle processes from .NET.

In SQL Server, for example, we don’t have explicit reflex cursors if proc returns a result set (or multiple result sets) that is available as the output result set in both ADO.NET and SSMS, and you can just test the SP, by executing EXEC spname param1, param2 .

The problem I am facing is that I don’t know how to call them directly in SQL in Toad, for example, to be able to test the changes at the PL / SQL level before going into the application. I’m very used to being able to implement and even remix the stored processes and functions in SQL Server so that I can reorganize the database interface layer without affecting the external interface for the application level code.

Answer 1

The best solution was found in the link provided by OMG Ponies:

The easiest way to test an Oracle stored procedure

and here:

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

Answer 2

look at the link 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 with the anonymous block ad it will work with. I use SQL Developer (highly recommend, free with lots of support) or SQL plus, so I can not help with TOAD, but I would expect it to be the same. In SQL Developer (and in SQL Navigator, if the memory is correct), you can simply right-click on the package / method you have selected and it will create a script for you.
in the toad and navigator. I believe that you can get the ref cursor in a pretty grid, while in the developer you will get it in the text.

SQL Developer you can also unit test

Answer 3

Try the following:

 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.

Answer 4

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

In the Procedure Editor, load your procedure. Click on the zipper bolt to execute, and you will see the Set Parameters window, which is also accessible using the button on the Proc Editor toolbar, which has an image similar to (...) on it, next to the zipper. Click on the exit button and you will see your options. If this is a weak reference cursor, then you should use the grid option in the memory location. The results go to the cursor results tab at the bottom of the PE after execution.

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