1

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.

user2059532
  • 69
  • 1
  • 2
  • 7
  • Sorry, the above code has two variables that aren't used in this case, but it doesn't affect anything... but I did notice that I left that in there (I was querying another procedure before this one) – user2059532 Nov 19 '13 at 18:29

1 Answers1

5

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. .PL/SQL Developer Test Window

Now expand the <Cursor> value and the resutls will appear in a separate window: PL/SQL Developer cursor results

Jon Heller
  • 34,999
  • 6
  • 74
  • 132
  • Would it be possible to give an example using the above code? I'm trying (and Googling) but not getting it – user2059532 Nov 19 '13 at 22:14
  • Sure, see the new answer. Btw, you have a very difficult job ahead of you. Although I strongly prefer PL/SQL Developer compared to TOAD, both tools have their share of quirks and a learning curve. It would takes weeks or months to get comfortable enough to truly compare the two. You really need to work directly with an experienced user to pick up all the tricks and tips that make it so useful. For example, did you know about Tools-->Preferences-->SQL Window-->AutoSelect statement? Until I found that I absolutely hated the program. Good luck! – Jon Heller Nov 20 '13 at 05:39
  • Thanks. I had a hard time doing what you explained above, but in the end it was as you said: I just couldn't figure out how to do it in the new tool (specifically, I couldn't figure out how to expand the cursor... then I saw that little elipsis in the far right-hand corner...) Thanks for your help! (and yes - and funny - the "autoselect" was the FIRST thing I desperately needed to discover... and did... going from Toad's convenient Shft-F9 to having to highlight my SQL was just not going to cut it) – user2059532 Nov 22 '13 at 18:29