How to print SYS_REFCURSOR with PLSQLDeveloper win

问题:

In the below query how to get IO_CURSOR values in PL/SQL's "SQL Window"

DECLARE
  SOME_VAR_1 VARCHAR2(20);
  SOME_VAR_2 VARCHAR2(20);
  SOME_VAR_3 DECIMAL;
  IO_CURSOR SYS_REFCURSOR;
BEGIN
  SOME_VAR_1 := 'test1';
  SOME_VAR_2 := 'test2';
  SOME_VAR_3 := 1;
  IO_CURSOR := NULL;
  Get_Analysis_Data(p_in_symbol_type => SOME_VAR_1,
                    p_in_symbol => SOME_VAR_2, 
                    p_in_isr_id => SOME_VAR_3,
                    isr_main_view => IO_CURSOR);
  PRINT IO_CURSOR
END;

回答1:

If by "SQL Window" you mean SQL*PLUS ,then to print(using PRINT command) the contents of a cursor, you need to declare a bind variable outside the PL/SQL block, assign a value to that bind variable inside the PL/SQL block by executing the block and then print the contents by using PRINT command:

SQL> variable  IO_CURSOR refcursor;

SQL> DECLARE
  2    SOME_VAR_1 VARCHAR2(20);
  3    SOME_VAR_2 VARCHAR2(20);
  4    SOME_VAR_3 DECIMAL;
  5    --IO_CURSOR SYS_REFCURSOR;
  6    BEGIN
  7      SOME_VAR_1 := 'test1';
  8      SOME_VAR_2 := 'test2';
  9      SOME_VAR_3 := 1;
  10     --IO_CURSOR := NULL;  -- no need to do that
  11     Get_Analysis_Data(p_in_symbol_type => SOME_VAR_1,
  12                       p_in_symbol => SOME_VAR_2, 
  13                       p_in_isr_id => SOME_VAR_3,
  14                       isr_main_view => :IO_CURSOR);
  15   END;
  16 /

  SQL> print io_cursor;

EDIT:

To see the contents of a cursor in PL/SQL Developer, as one of the options, you could simply do the following:

1. File\New\Test window
2. Copy/Paste your anonymous PL/SQL block there. Prior to this remove IO_CURSOR SYS_REFCURSOR; variable declaration. There is no need of it. Also change isr_main_view => IO_CURSOR to isr_main_view => :IO_CURSOR. You need to use bind variable in this case.
3. In the variables window at the bottom of the test window specify variable name of your ref cursor the contents of which you want to see (IO_CURSOR without preceding semicolon ) and select type cursor.
4. Execute the block by pressing green triangle.
5. After PL/SQL block is executed refer to the column value of the variables window. Press the button with ellipsis on it to see the contents of the ref cursor IO_CURSOR.

回答2:

If Get_Analysis_Data is a stored procedure you can execute it by opening it and using the "Run" command (the green arrow in your toolbar). The content of the refcursor is printed in the "Variables Output" window like a table.

A brief tutorial can be found here: http://www.thatjeffsmith.com/archive/2011/12/sql-developer-tip-viewing-refcursor-output/

EDIT:

Sorry, got confused with the IDE-Tools names. This answer applies only if you are using Oracle SQL-Developer.... If you don't use it i suggest you to switch asap :)