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

Question

I'm looking for a good explanation on how to test an Oracle stored procedure in SQL Developer or Embarcardero Rapid XE2. Thank you.

Answer 1

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.

Answer 2

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.