Re: ref cursor question

Ryan,

The method I use, when I can, is to:

1. Define a record type for the output of the query.
2. Create a function that fetches a row from the refcursor and returns a record.

For example:

type g_my_rectype is record (
   column_1 varchar2(10),
   column_2 varchar2(20)

function fetch_my_cursor(

   p_cursor in sys_refcursor
) return g_my_rectype
is
  l_rec g_my_rectype;
begin
  fetch p_cursor into l_rec;
  return l_rec;
end fetch_my_cursor;

If you can then arrange that everybody who uses the cursor uses the function to fetch from it, your done. Of course that might not be so easy in practice.

Regards
David

On 28/11/2007, ryan_gaffuri_at_comcast.net <ryan_gaffuri_at_comcast.net> wrote:
> I am returning REF Cursors to an external interface. I want code to test each of these procedures. The problem is that these queries often have 25+ columns in them from several tables.
>
> My test procedure really only needs 1 column to verify the query executes and so I can trace it to check the plan.
>
> so I am looking for something like
>
> fetch myrefcursor.FIRSTCOLUMNONLY in l_myval
>
> Without having to make a new procedure that either has a ref cursor with just 1 column in the select clause . The problem with this option is that every time I change a query, I have to change the code in 2 places. This can easily get out of synch.
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l