Re: Ref cursor in stored proc

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 14 Oct 2008 06:31:44 -0700
Message-ID: <1223991099.948821@bubbleator.drizzle.com>


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
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Tue Oct 14 2008 - 08:31:44 CDT

Original text of this message