How to test an Oracle Stored Procedure with RefCursor return type?

Something like

create or replace procedure my_proc( p_rc OUT SYS_REFCURSOR )
as
begin
  open p_rc
   for select 1 col1
         from dual;
end;
/

variable rc refcursor;
exec my_proc( :rc );
print rc;

will work in SQL*Plus or SQL Developer. I don't have any experience with Embarcardero Rapid XE2 so I have no idea whether it supports SQL*Plus commands like this.

Something like this lets you test your procedure on almost any client:

DECLARE 
  v_cur SYS_REFCURSOR;
  v_a   VARCHAR2(10);
  v_b   VARCHAR2(10);
BEGIN
  your_proc(v_cur);

  LOOP
    FETCH v_cur INTO v_a, v_b;
    EXIT WHEN v_cur%NOTFOUND;
    dbms_output.put_line(v_a || ' ' || v_b);
  END LOOP;
  CLOSE v_cur;
END;

Basically, your test harness needs to support the definition of a SYS_REFCURSOR variable and the ability to call your procedure while passing in the variable you defined, then loop through the cursor result set. PL/SQL does all that, and anonymous blocks are easy to set up and maintain, fairly adaptable, and quite readable to anyone who works with PL/SQL.

Another, albeit similar way would be to build a named procedure that does the same thing, and assuming the client has a debugger (like SQL Developer, PL/SQL Developer, TOAD, etc.) you could then step through the execution.

In SQL Developer you can right-click on the package body then select RUN. The 'Run PL/SQL' window will let you edit the PL/SQL Block. Clicking OK will give you a window pane titled 'Output Variables - Log' with an output variables tab. You can select your output variables on the left and the result is shown on the right side. Very handy and fast.

I've used Rapid with T-SQL and I think there was something similiar to this.

Writing your own delcare-begin-end script where you loop through the cursor, as with DCookie's example, is always a good exercise to do every now and then. It will work with anything and you will know that your code works.