Re: Ref cursor in stored proc

From: Palooka <nobody_at_nowhere.com>
Date: Fri, 17 Oct 2008 21:59:30 +0100
Message-ID: <S07Kk.1025$FH4.778@newsfe21.ams2>

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:

>>>> 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
>> 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 Received on Fri Oct 17 2008 - 15:59:30 CDT