Re: Ref cursor in stored proc

From: Palooka <nobody_at_nowhere.com>
Date: Sat, 18 Oct 2008 22:20:43 +0100
Message-ID: <FqsKk.116849$dG5.60151@newsfe14.ams2>

Mark D Powell wrote:
> On Oct 17, 10:36 pm, DA Morgan <damor..._at_psoug.org> wrote:

>> 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:
>>>>>>> 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-Hidequoted 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
>> 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
>> damor..._at_x.washington.edu (replace x with u to respond)
>> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>>
>> - Show quoted text -

>
> Palooka, Daniel is correct in my first point: v$ verse gv$. In this
> specific case you probably always want to query v$reserved_words
> instead of gv$reserved_words since the gv$ version returns unnecessary
> duplicates. Sometimes you want data for the current instance and
> sometimes you want data from all instances. Just recognize from where
> you want and are getting your information and use the appropriate
> view.
>
> I think it is fairly clear that my comment about seeing posts that
> imply development against production do not have to do with this
> specific thread. It was just a response to Daniel's comments. Daniel
> and I both know that far too many activities that really should have
> been performed against test/development that are performed against
> production instances. Reading over my posts I do not see where any
> reference is made that points to the OP's environment.
>

It wasn't you; it was DM who started on about production clusters in this thread - completely a propos of nothing.

Palooka Received on Sat Oct 18 2008 - 16:20:43 CDT