I would like to create a multi-statement table valued function in Oracle
(having done so for years in SQL Server). Functions in Oracle do not allow
a table return type, so I was wondering if someone could suggest a
workaround to replicate the same functionality?

Thanks

Mike

"Mike D" <miked (AT) hotmail (DOT) com> wrote

Oracle PL/SQL provides self-declared table types. Create a package
containing your function. Inside the package, declare a table type
that you use as a return type:

create or replace package my_pak
as
-- example: a list of strings
type mytabtype is table of varchar2(42) index by binary_integer;

function tablefunc(n number) return my_pak.mytabtype;
end my_pak;

create or replace package body my_pak
as
function tablefunc(n number)
return my_pak.mytabtype
is
retval my_pak.mytabtype;
begin
for z in (select name, id from characters where zaphod_id = n)
loop
retval(z.id) := z.name;
end loop;
return retval;
end;
end my_pak;

In other PL/SQL packages, simply you this special data type
my_pak.mytabtype.
Note that there are more types:
- tables which are not indexed by binary integers; these may be
referred to by SQL statements
- VARRAYs: these have a fixed length but provide better performance
- PL/SQL tables may be inserted into single record fields if they are
treated as objects

See also: Oracle Documentation / Application Development / PL/SQL
developer's guide

greetinx
elwood



Ät inte gul snö :=)