how do oracle stored procedures (w/ cursors) work?

Question

I have a following oracle stored procedure

    CREATE OR REPLACE
PROCEDURE getRejectedReasons
  (
    p_cursor IN OUT SYS_REFCURSOR)
AS
BEGIN
  OPEN p_cursor FOR SELECT * FROM reasons_for_rejection;
END;

However, when I run this stored procedure in sql-developer then I dont see anything. I just see something like this:

Connecting to the database oracleLocal.
Process exited.
Disconnecting from the database oracleLocal.

I'm coming from MS sql server and am used to seeing actual results when running a stored procedure like this. Is this stored procedure not returning results because I am using a cursor??

Answer 1

You opened the cursor. You didn't select anything from it, update it, or advance it.

All open does, effectively, to select the matching rows into temporary memory, so you can advance the cursor row by row. Which you didn't do.

Answer 2

One of the differences between Oracle and SQL Server is that the latter returns result sets naturally. I'd use a function, by the way.

In Oracle, functions typically return a single element. Cursors came later.

There's some documentation online that will help you understand the use of refcursor bind variables. Here's one such for SQL*Plus:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch5.htm#sthref1122

I think in SQL Developer you can do the same thing with autoprint on, although I haven't tested that.

Found a blog that also discusses something similar:

http://vadimtropashko.wordpress.com/cursors/

Answer 3

ETA: Ok. Ignore what I wrote. Listen to someone else. Apparently it's wrong, as I got down voted.

What tpdi said is correct. You have to do something with the cursor after you declare it.

Here's an example using two cursors in nested loops

   PROCEDURE update_insert_tree (exid_in IN NUMBER, outvar_out OUT VARCHAR2)
   IS
      nxtid         NUMBER;
      phaseid       NUMBER;
      rowcounter1   NUMBER;
   BEGIN
      rowcounter1 := 0;
      outvar_out := 0;

      FOR acur IN (SELECT dept_exercise_id, phase
                     FROM ep_dept_exercise
                    WHERE exercise_id = exid_in)
      LOOP

         <<dept_loop>>
         FOR thecur IN (SELECT document_name, thelevel, sortnum, type_flag,
                               ex_save_id
                          FROM ep_exercise_save
                         WHERE exercise_id = exid_in)
         LOOP
            phaseid := acur.phase;

            IF phaseid = 0
            THEN
               phaseid := 10;

               UPDATE ep_dept_exercise
                  SET phase = 10
                WHERE dept_exercise_id = acur.dept_exercise_id;
            END IF;

            <<doc_loop>>

Answer 4

The stored procedure is returning something it's just you aren't doing anything with the results.

You can do this simply by running the following script in SQLDeveloper:

VARIABLE csr REFCURSOR;
EXEC getRejectedReasons(:csr); -- the colon identifies the parameter as a variable
PRINT csr;

Another method is to fetch each row and do some sort of processing:

DECLARE
  -- sys_refcursor is weakly typed
  refcsr  SYS_REFCURSOR;
  -- define a record so we can reference the fields
  rej_rec Reasons_for_Rejection%ROWTYPE;
BEGIN

  getRejectedReasons(refcsr);

   -- loop through the results  
   LOOP
      -- gets one row at a time
      FETCH refcsr INTO rej_rec;
      -- if the fetch doesn't find any more rows exit the loop
      EXIT WHEN refcsr%NOTFOUND;
      -- Do something here.  
      -- For example : DBMS_OUTPUT.PUT_LINE(rej_rec.reason_desc);
    END LOOP;

END;