Re: Ref cursor in stored proc
Date: Wed, 15 Oct 2008 21:53:59 -0700
Mark D Powell wrote:
> On Oct 14, 9:31 am, DA Morgan <damor..._at_psoug.org> wrote:
>> Tim Mickelson wrote:
>>> Hello >>> If I call a function from a stored procedure and the function returns >>> a ref cursor. The only info I have and want is that this ref cursor has >>> a field, "NAME", but I know nothing of all other fields, how do I fetch >>> this field name? >>> E.g. this does not work since the ref cursor has many values, not just >>> NAME. >>> FUNCTION TESTFUNCTION(input_var IN VARCHAR2) RETURN VARCHAR2 IS >>> TYPE RecType IS RECORD(nome IMPRESA_PARIX.NAME%TYPE); >>> Cursore IMP_REFCUR; >>> rec RecType; >>> BEGIN >>> -- Call function that returns IMP_REFCUR (ref cursor) >>> Cursore := SEARCH_PARAMETRICA('MKM S.R.L.'); >>> LOOP >>> FETCH Cursore INTO rec; >>> DBMS_OUTPUT.PUT_LINE(rec.NAME); >>> EXIT WHEN Cursore%NOTFOUND; >>> END LOOP; >>> RETURN NULL; >>> END TESTFUNCTION;
>> I'm not aware of any way to do it I will be thrilled if someone knows
>> how to do it with a weakly typed ref cursor.
>> But be advised "NAME" is a reserved word and should not be used for
>> objects, columns, or variables.
>> SELECT keyword
>> FROM gv$reserved_words
>> WHERE keyword LIKE 'NAM%';
>> Daniel A. Morgan
>> Oracle Ace Director & Instructor
>> University of Washington
>> damor..._at_x.washington.edu (replace x with u to respond)
>> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>> - Show quoted text -
> > Just a note that I would suggest using the v$reserved_words version > over the gv$ versions since on RAC the gv$ version of the query will > run against all instances. I find when looking at v$ information that > I usually only want to look at the current instance data rather than > the data for all instances.
Your point is well made though I would hope no one is doing development against on a production cluster.
I tend to use gv$ by default these days because far too often it is the opposite situation where people are looking at v$parameter or v$session and not seeing what they need to see.
-- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.orgReceived on Wed Oct 15 2008 - 23:53:59 CDT