Try it here
Table as a return value

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


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;

 -- 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 *

Writer profile pic

Admin on Oct 04, 2019 at 12:02 am

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

Post Comment

Comments( 0)




Forgot Password

Please enter your email address below and we will send you information to change your password.