Re: PL/SQL - returning RECORD from function

Hi,

The problem is the 'scope' of the 'TYPE' declaration.

Although they have the same name and definition, as far as Oracle is concerned they are two completely different types. Why not try:

FUNCTION GetARow RETURN lt_menu%ROWTYPE IS

row_found lt_menu%ROWTYPE;

CURSOR c IS
  SELECT * FROM lt_menu;
BEGIN
 OPEN c;
   FETCH c INTO row_found;
 CLOSE c;
RETURN row_found;
END; Another way is to declare your type in a package specification then use that definition in your function and code.

graham

On Sat, 10 Apr 1999 05:24:25 GMT, <NewName_at_mel.switch.net.au> wrote:

>I have the following function declared and this works fine.
>
>FUNCTION GetARow RETURN table_row IS
>
>TYPE table_row IS RECORD (rec lt_menu%ROWTYPE);
>row_found table_row;
>
>CURSOR c IS
> SELECT * FROM lt_menu;
>BEGIN
> OPEN c;
> FETCH c INTO row_found.rec;
> CLOSE c;
> RETURN row_found;
>END;
>
>But how do I call it .. I've tried the variations following without success
>
>DECLARE
>
>TYPE table_row IS RECORD (rec lt_menu%ROWTYPE);
>row_found table_row;
>menu_description lt_menu.menu_description%TYPE;
>
>BEGIN
>
>row_found.rec := GetARow; -- expression is of wrong type
>row_found.rec := GetARow.rec; -- subprogram GETAROW reference is out of
>range
>row_found := GetARow; -- expression is of wrong type
>
>END;
>
>If I include a dummy parameter in the function and call, then the following
>works fine :
>
>row_found.rec := GetARow( '' ).rec ;
>
>But even though the Oracle doco I've seen recommends that "if no parameters
>are required, then declare a local user-defined record to which you can
>assign functionresult and then reference the fields directly", I'm at a
>loss to know
>how???
Received on Sun Apr 11 1999 - 21:00:01 CEST