Re: Ref cursor in stored proc

Mark D Powell wrote:

> On Oct 14, 9:31 am, DA Morgan <damor..._at_psoug.org> 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;

>>

>>

>>

> 
> 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.org