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

oracle stored-procedures plsqldeveloper ref-cursor rapidsql
Ryan Fisch picture Ryan Fisch · Jul 21, 2011 · Viewed 217.5k times · Source

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

Justin Cave picture Justin Cave · Jul 21, 2011

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.

Related questions

How to parse comma delimited string in PL/SQL?

I have a comma delimited string in a PL/SQL script (e.g. data:= 'a,b,c,d,e'), that I need to parse out within the script. I would like to loop through the string and process each item. …

Read More
display resultset from oracle 10g stored procedure

I am using PL/SQL Developer and i have written a procedure to run a report and i need to procedure to output the resultset. The procedure accepts input parameters and needs to output the resultset. I cannot use a …

Read More
Run Stored Procedure in SQL Developer?

I am trying to run a stored procedure that has multiple in and out paramaters. The procedure can only be viewed in my Connections panel by navigating Other Users | | Packages | | If I right click , the menu items are "Order Members …

Read More