Re: PLSQL: Function Returning a table

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 14 Sep 1998 19:22:59 GMT
Message-ID: <36036cb4.25446169@192.86.155.100>

A copy of this was sent to mark_aurit_at_mail.northgrum.com (Mark Aurit) (if that email address didn't require changing) On Mon, 14 Sep 1998 18:29:14 GMT, you wrote:

>I need to return a PL/SQL Table from a Function, and have had no luck
>(Im using a publicly defined table and accessing it through a
>Procedure in the mean time. Works, but its ugly) I get various
>type-related errors when trying something like this:
>
>CREATE OR REPLACE PACKAGE mine IS
> TYPE tblfoo1 TABLE OF VARCHAR2(4) INDEX BY BINARY_INTEGER;
> itblfoo1 tblfoo1;
> FUNCTION myfunc (id IN VARCHAR2) RETURN itblfoo1;
>END;
>
>CREATE OR REPLACE PACKAGE BODY mine IS
> FUNCTION myfunc (id IN VARCHAR2) RETURN itblfoo1 IS
> BEGIN
> itblfoo1(1) := 'TOAD';
> RETURN itblfoo1;
> END;
>END;
>
>Any help is greatly appreciated.
>Mark
>mark_aurit AT-SIGN mail.northgrum.com

the problem is you are confusing a TYPE with a VARIABLE. Your example above shows a function, myfunc, returning a VARIABLE but functions return something of a TYPE, not a variable.

I've rewritten your example below. Basically, you will define a type, define a function that returns that type, create the function that populates a variable of that type and returns that.

Hope this helps

SQL> create or replace package mine
  2 as
  3 type tblfoo1 is table of varchar2(4) index by binary_integer;   4
  4 function myfunc( id in varchar2 ) return tblfoo1;   5 end;
  6 /
Package created.

SQL> create or replace package body mine   2 as
  3
  3 function myfunc( id in varchar2 ) return tblfoo1   4 is
  5 l_temp_tbl tblfoo1;
  6 begin

  7          l_temp_tbl(1) := 'TOAD';
  8          return l_temp_tbl;

9 end;
10
10 end;
11 /
Package body created.

SQL> declare
2 The_Function_Return_Value mine.tblfoo1; 3 begin
4 The_Function_Return_Value := mine.myfunc( 5 ); 5

  5          for i in 1 .. The_Function_Return_Value.count loop
  6                  dbms_output.put_line( The_Function_Return_Value(i) );
  7          end loop;

8 end;
9 /
TOAD PL/SQL procedure successfully completed.

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Mon Sep 14 1998 - 14:22:59 CDT