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.

Solution 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.

Solution 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.

Solution 3

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.

Solution 4

In Toad 10.1.1.8 I use:

variable salida refcursor
exec MY_PKG.MY_PRC(1, 2, 3, :salida)  -- 1, 2, 3 are params
print salida

Then, Execute as Script.

Solution 5

I think this link will be enough for you. I found it when I was searching for the way to execute oracle procedures.

The link to the page

Short Description:

--cursor variable declaration 
variable Out_Ref_Cursor refcursor;
--execute procedure 
execute get_employees_name(IN_Variable,:Out_Ref_Cursor);
--display result referenced by ref cursor.
print Out_Ref_Cursor;

View more solutions

Comments

[Sam]

I know about running the package by right click which is great most of the time. But I also just need a way to run this in sql from the SQL Window. This is bizzarly difficult compare to SQL Server. I spend hours and hours and hours just trying to do anything.

[Stack0verflow]

I strongly agree with @Sam. I believe it is by design Oracle makes things harder to do. For example, SQL Developer could have easily displayed the PL SQL script after we enter everything we need to run a stored procedure from the dialog window. But instead, it shows us the result only, whereas we beginners still don't know how to hand craft the script to exec a procedure in a PL SQL block.

[Stack0verflow]

Not to mention Oracle error messages. They are so bizarre. For example, if you have a typo in a column name in your insert statement, you get an ORA-00984 error which says "Column not allowed here". Dude, doesn't "Invalid column" or "Column is invalid" make more sense?

[Just a HK developer]

Pardon me from this new user of Oracle SQL Browser. If the cursor returns many columns, how can we have the cursor to show all columns without specify all the output columns first?

[Carlos ABS]

Thank you. PL/Sql is a crap language.

[cheesus]

Thanks guys. Absolutely my opinion too. Oracle might have been the best database once, but today, SQL Server and Postgres are just way better, both in performance (see recent benchmarks) and in developer friendlyness. No wonder Oracle developers are way more expensive, if they need to learn how to manually loop through a cursor to do basic things like executing a procedure. Unbelievable.

[Mauricio Arias Olave]

Use SET SERVEROUTPUT ON; before the DECLARE statement.