FETCH arbitrary columns from a ref_cursor
From: Agoston Bejo <gusz1_at_freemail.hu>
Date: Tue, 17 Aug 2004 15:11:34 +0200
Message-ID: <cft0bt$kcg$1_at_news.caesar.elte.hu>
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:
Date: Tue, 17 Aug 2004 15:11:34 +0200
Message-ID: <cft0bt$kcg$1_at_news.caesar.elte.hu>
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:
- I could declare a variable with the type c%ROWTYPE or something.
- I could FETCH only the columns that I need, e.g. FETCH c(col1, col5, col7) INTO var1, var5, var7;
- 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 Received on Tue Aug 17 2004 - 15:11:34 CEST