469,903 Members | 1,902 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,903 developers. It's quick & easy.

FETCH arbitrary columns from a ref_cursor

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
Jul 19 '05 #1
1 8404
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
da******@x.washington.edu
(replace 'x' with 'u' to respond)

Jul 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

13 posts views Thread by RR | last post: by
4 posts views Thread by db2admin | last post: by
2 posts views Thread by technocrat | last post: by
reply views Thread by Greg Collins [Microsoft MVP] | last post: by
1 post views Thread by Agoston Bejo | last post: by
1 post views Thread by Waqarahmed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.