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

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 Received on Mon Sep 18 2006 - 22:41:26 CEST