CODEWITHSUNDEEP
oracle-databaseplsql
TownCube
TownCube

Reputation: 1310

Return collection from packaged function for use in select

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?

Upvotes: 2

Views: 17923

Answers (2)

user272735
user272735

Reputation: 10648

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

Upvotes: 0

Vincent Malgrat
Vincent Malgrat

Reputation: 67742

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));

Upvotes: 7

Related Questions