Re: Ref cursor in stored proc

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.

UT1 > select count(*) from v$reserved_words;

  COUNT(*)

       810

UT1 > select count(*) from gv$reserved_words;

  COUNT(*)

      1620

HTH -- Mark D Powell -- Received on Tue Oct 14 2008 - 11:04:13 CDT