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;
/

Mario wrote:

T.O.A.D. is a 3rd-party tool, neither supported nor endorsed by Oracle,
so I think you should send your question and associated rant to its
developers (Quest Software) or tech support staff and see what they
have to say. Maybe this is a missing feature that they can add. In
SQL*Plus, which is Oracle-supplied tool, you would do it like this:

VAR x REFCURSOR
EXEC sp_get_recs(1, :x);
PRINT x

And please don't compare Oracle to MSSQL - they are completely
different in architecture, features, programming environments and what
not. I can name a ton of things I can effortlessly do in Oracle but
can't in MSSQL. This won't cause me to say that MSSQL sucks - it's just
different.

Regards,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com

Thanks for your reply!

Mario wrote:

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

Mario wrote:

I usually create a block that calls the procedure and keep it around
for unit testing later on. Note the little :colon before the cursor
variable. This is what lets you see the results in Toad. A little pop
up appears and asks you what :cur is. Tell it you really mean cursor.

DECLARE

type x is ref cursor;

cur x;

BEGIN

sp_get_recs(1,:x);

END;

Don't need the fancy edition of Toad to do it that way.