Re: Ref cursor in stored proc
Date: Fri, 17 Oct 2008 08:00:51 -0700 (PDT)
Message-ID: <7021141a-d6b0-43b9-8a7a-5b3bfb5a3328@m44g2000hsc.googlegroups.com>
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.
>> >> damor..._at_x.washington.edu (replace x with u to respond)
> >> SELECT keyword
> >> FROM gv$reserved_words
> >> WHERE keyword LIKE 'NAM%';
> >> --
> >> Daniel A. Morgan
> >> Oracle Ace Director & Instructor
> >> University of Washington
> >> Puget Sound Oracle Users Groupwww.psoug.org-Hide quoted text -
>
> >> - Show quoted text -
>> > I usually only want to look at the current instance data rather than
> > 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
> > 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.
Keep Dry.
-- Mark D Powell --
Received on Fri Oct 17 2008 - 10:00:51 CDT