Re: Oracle TOAD: Dislaying Ref Cursor in DataGrid?

Mario wrote:
> I've spent far too long trying to discover the answer to this very
> simple question. How do I use TOAD to display the result set from a
> stored procedure? In Microsoft SQL Server, this is effortless. You
> don't have to jump through any hoops or do anything funny! In TOAD for
> Oracle I've seen many people ask this question, but I haven't found a
> satisfactory answer. I even saw someone mention TOAD already having
> the built-in functionality to display ref cursors, but I can't seem to
> find the feature setting.
>
> Why is something so simple as executing a stored procedure that returns
> a result set (and viewing that result set) so cryptic?!
>
> How do you do it?
>
> I have TOAD 8.6.1.0 for Oracle.
>
> My stored proc follows this pattern:
>
> CREATE OR REPLACE PROCEDURE sp_get_recs (
> i_parm1 IN NUMBER,
> o_rs OUT types.resultset -- defined in a common package as a "ref
> cursor"
> )
> AS
>
> BEGIN
>
> OPEN o_rs FOR SELECT * FROM MYTABLE WHERE PARM1 = i_parm1;
>
> END sp_get_recs;
> /

>From the TOAD 8.5.x help file

REF CURSOR Results Window
Note: This extended Toad feature is only available in Toad for Oracle editions that include debugging functionality.

You can specify to have REF CURSOR results output to a separate window. This window is dockable to the other debugger windows.

When a debug session terminates, this window displays each table that was created for REF CURSORS for the Create and write to table option described in Setting Parameters. Each table is shown in a grid on a separate tab in the window.

Note: The REF CURSOR window has the following limitations:

Works only with strongly-typed REF CURSORs

Works only when the REF CURSOR type is declared in a package belonging to the currently logged-in user

The three toolbar buttons are:

Refresh - refreshes the grid in the current tab (re-executes the select * statement)

Close Tab - closes the current tab

Drop Table - drops the current table and closes the tab

The grid supports all the usual popup editors, but it is a read-only query.  

-- 
Sybrand Bakker
Senior Oracle DBA