Oracle /PL-SQL: How to return a Table from a function . dEexams.com

With collections and the table() function, a function can return a table that can be queried in an SQL statement

Create Record type

First,create a record type. In this example, the records consist of two attributes: i , a number and n , a varchar2.

The record type is created with create type:

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

Create Table type

Based on the record type, we can now create a table type.

create or replace type t_table as table of t_record;
/

Create Function

With the table type, we're ready to create a function.

The function returns a t_table.

create or replace function return_table return t_table as
  v_ret   t_table;
begin

 --
 -- Call constructor to create the returned
 -- variable:
 --
    v_ret  := t_table();

 --
 -- Add one record after another to the returned table.
 -- Note: the "table" must be extended before adding
 -- another record:
 --
    v_ret.extend; v_ret(v_ret.count) := t_record(1, 'one'  );
    v_ret.extend; v_ret(v_ret.count) := t_record(2, 'two'  );
    v_ret.extend; v_ret(v_ret.count) := t_record(3, 'three');

 --
 -- Return the record:
 --
    return v_ret;

end return_table;
/

Using the function

In order to use the function's returned value as a table in a SQL statement, we have to enclose the function within the table() statement. From the SQL's perspective, the table(...) construct behaves as though it were an actual table.

select * from table(return_table);

Oracle SQL: table(collection)

Within an SQL statement, the expression table(collection) is treated like a table:

select *
from
  table(collection);

Uk01 on Oct 04, 2015 at 12:02 am

If you like dEexams.com and would like to contribute, you can write your article here or mail your article to admin@deexams.com . See your article appearing on the dEexams.com main page and help others to learn.