Executing stored procedure in PL/SQL Developer SQL Window

Question

I've used TOAD for awhile, but my dept has asked me to evaluate PL/SQL Developer as a possible change.

I'm trying to run the following in PL/SQL developer. It gives an error saying: ORA-00900: Invalid SQL Statement

VARIABLE mycur    refcursor;
VARIABLE errorseq NUMBER;
VARIABLE errormsg CHAR;
EXEC rums.rums_sp_tv_project_breakdown2(94090,:mycur);
print mycur;

In TOAD, I can put this in a SQL Editor and hit F5 to "Execute as Script", and the output appears just fine.

Any ideas on how to do this? I see PL/SQL Developer has a command window, but I'm not a SQLPlus guru (perhaps my problem) and can't get it to run in the command window either.

Answer 1

The PL/SQL Developer command window does not support refcursor, it displays the message REFCURSOR not supported.

But the Test Windows does support cursors. First, create a sample procedure in a separate window:

create or replace procedure test_procedure(p_cursor in out sys_refcursor) is
begin
    open p_cursor for select 'column 1' col1, 'column 2' col2 from dual;
end;
/

Open a Test Window. Add a variable of type Cursor. Add an anonymous PL/SQL block that uses that variable as a parameter to the sample procedure. Run the PL/SQL block and it will populate the cursor. .

Now expand the <Cursor> value and the resutls will appear in a separate window: