Re: FETCH arbitrary columns from a ref_cursor

Agoston Bejo wrote:

> The problem is as follows:
> I have a stored proc. that returns a types.ref_cursor (where
> types.ref_cursor is defined as 'ref cursor'):
> PROCEDURE P(c OUT TYPES.ref_cursor);
> The returned cursor has a bunch of columns, but I only need a few of them.
> Anyway, with FETCH INTO I have to provide variables for every column. So I
> have to do this:
> var1 Type1;
> var2 Type2;
> ...
> FETCH c INTO var1, var2, ..., var20, ... var_n;
> Where Type1, etc. ... are as specified in the documentation of P. (Since I
> couldn't find a way to determine the record type of an arbitrary
> ref_cursor.)
> Is there a way to make this type of situation easier?
> Some solutions came into my mind, such as:
>
> 1. I could declare a variable with the type c%ROWTYPE or something.
> 2. I could FETCH only the columns that I need, e.g.
> FETCH c(col1, col5, col7) INTO var1, var5, var7;
> 3. I could somehow find a way for the procedure to return something other
> than a ref_cursor which seems a nightmare to work with.
>
> Unfortunately, I have found nothing like this either on the net or in the
> Oracle documentation.
>
> Any ideas?
>
> Thx

The code has more problems than you may think:

select keyword
from v$reserved_words
where keyword like 'TY%';

Assuming Oralce 9i ... you don't say ... redefine as:

PROCDURE P (c OUT SYSREFCURSOR)

The solution to your problem can be found at: http://www.psoug.org/reference/ref_cursors.html

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)