Return collection from packaged function for use in select

Question

I'm currently using this block of code to return a collection of rows from my function.

--Source: http://www.adp-gmbh.ch/ora/plsql/coll/return_table.html

create or replace type t_col as object (
i number,
n varchar2(30)
);
/
create or replace type t_nested_table as table of t_col;
/
create or replace function return_table return t_nested_table as
  v_ret   t_nested_table;
begin
  v_ret  := t_nested_table();

  v_ret.extend;
  v_ret(v_ret.count) := t_col(1, 'one');

  v_ret.extend;
  v_ret(v_ret.count) := t_col(2, 'two');

  v_ret.extend;
  v_ret(v_ret.count) := t_col(3, 'three');

  return v_ret;
end return_table;
/

Which I call by issuing SQL

select * from table(return_table);

Object types can not be defined in a package, I tried using the record type which worked (in PL/SQL) but I couldn't select from it in the same way as I can here.

How do I achieve this result using a function inside a package?

score 7 · Accepted Answer · answered Oct 25 '11 at 13:05

You could either use SQL objects inside your package or use pipelined functions (tested with 10gr2). Using SQL objects is straightforward, your actual function could be used as is inside a package.

Here's how you could use a pipelined function with a RECORD type:

SQL> CREATE OR REPLACE PACKAGE my_pkg IS
  2     TYPE t_col IS RECORD(
  3        i NUMBER,
  4        n VARCHAR2(30));
  5     TYPE t_nested_table IS TABLE OF t_col;
  6     FUNCTION return_table RETURN t_nested_table PIPELINED;
  7  END my_pkg;
  8  /

Package created
SQL> CREATE OR REPLACE PACKAGE BODY my_pkg IS
  2     FUNCTION return_table RETURN t_nested_table PIPELINED IS
  3        l_row t_col;
  4     BEGIN
  5        l_row.i := 1;
  6        l_row.n := 'one';
  7        PIPE ROW(l_row);
  8        l_row.i := 2;
  9        l_row.n := 'two';
 10        PIPE ROW(l_row);
 11        RETURN;
 12     END;
 13  END my_pkg;
 14  /

Package body created

SQL> select * from table(my_pkg.return_table);

         I N
---------- ------------------------------
         1 one
         2 two

What happens behind the scene is that Oracle understands that since you want to use your function in a query (because of the PIPELINED keyword), you will need SQL objects, so those objects are created behind the scene for you:

SQL> select object_name
  2    from user_objects o
  3   where o.created > sysdate - 1
  4     and object_type = 'TYPE';

OBJECT_NAME
--------------------------------------------------------------------------------
SYS_PLSQL_798806_24_1
SYS_PLSQL_798806_DUMMY_1
SYS_PLSQL_798806_9_1

SQL> select text from user_source where name='SYS_PLSQL_798806_9_1';

TEXT
--------------------------------------------------------------------------------
type        SYS_PLSQL_798806_9_1 as object (I NUMBER,
N VARCHAR2(30));

Thanks for the answer, when you say "use SQL objects inside your package" they still need to be defined outside the package right? I tried to define an object inside a package and got "PLS-00540: object not supported in this context.". I wanted to try and keep all my type definitions inside the package they were used. — TownCube, Oct 25 '11 at 20:23
Yes SQL objects are always defined outside of package. PLSQL record types defined inside a package can not be accessed from SQL. If you want to query table objects from a SELECT you will need SQL objects (defined as a type outside a package). I'd personaly rather go for a consistently named SQL object myself than an arbitrary name choosen by Oracle. — Vincent Malgrat, Oct 26 '11 at 09:07

score 0 · Answer 2 · answered Oct 25 '11 at 13:19

create or replace type t_col as object (
  i number,
  n varchar2(30)
);
/

create or replace package foo as
  type t_nested_table is table of t_col;
  function return_table return t_nested_table pipelined;
end;
/
show errors

create or replace package body foo as
  data t_nested_table := t_nested_table(t_col(1, 'one'),
                                        t_col(2, 'two'),
                                        t_col(3, 'three'));

  function return_table return t_nested_table pipelined as
  begin
    for i in data.first .. data.last loop
      pipe row(data(i));
    end loop;
    return;
  end;
end;
/
show errors

column n format a10
select * from table(foo.return_table);

         I N
---------- ----------
         1 one
         2 two
         3 three

Return collection from packaged function for use in select

2 Answers2

Linked