Re: Ref cursor in stored proc

Palooka wrote:

> Mark D Powell wrote:
>> On Oct 16, 12:53 am, DA Morgan <damor..._at_psoug.org> wrote:
>>> 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
>>> damor..._at_x.washington.edu (replace x with u to respond)
>>> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>>>
>>> - Show quoted text -
>>
>>>> I would hope no one is doing development against on a production 
>>>> cluster.
>> <<
>>
>> Daniel, Still an optimist.  We generally do not have any development
>> being performed against prodcution though when I see the same package
>> updated two or three days in a row in production I sometimes wonder.
>>
>> However, I have seen too many posts where from the information posted
>> it appears this is unfortunately not true.
>>
> I don't understand where he gets his inference that anything is being 
> done against a production cluster. Why does he assume that it not a test 
> cluster?
> 
> Palooka

I don't think Mark is making that inference. Just pointing out that one must understand the implications of using v$ and gv$. A point that bears repeating given that most people don't even know the gv$ objects exist.

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