Re: Ref cursor in stored proc

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Sat, 18 Oct 2008 11:38:17 -0700 (PDT)
Message-ID: <7ae460d0-6a73-4299-aa66-3a897647eb1b@m36g2000hse.googlegroups.com>


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.

HTH -- Mark D Powell -- Received on Sat Oct 18 2008 - 13:38:17 CDT

Original text of this message