Re: Oracle TOAD: Dislaying Ref Cursor in DataGrid?

From: skippydigits <sherrikalm_at_hotmail.com>
Date: 26 Sep 2006 19:30:05 -0700
Message-ID: <1159324205.562172.21370_at_h48g2000cwc.googlegroups.com>


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

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; Received on Wed Sep 27 2006 - 04:30:05 CEST

Original text of this message