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

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;