Pipelined Functions

Who never needed to use a memory table in a query instead of using a loop to get the values?

There is a kind of functions that allow you to do it.
The pipelined functions doesn't have the "return" statement.
Here is a example of a package that contains a record, a table for this record and a pipelined fuction:

I'm using Oracle XE 11G r2, it's available for version 10G too.
Before starting, let's create some test tables and populate it:

create table users (id number, name varchar2(40));
alter table users add constraint pk_users primary key (id);

create table permissions (id number, descr varchar2(40));
alter table permissions add constraint pk_permissions primary key (id);

create table user_permissions (id number, user_id number, id_permission number);
alter table user_permissions add constraint pk_user_permissions primary key (id);
alter table user_permissions add constraint fk_user_permissions_user foreign key (user_id) references users(id);
alter table user_permissions add constraint fk_user_permissions_perm foreign key (id_permission) references permissions(id);

insert into users (id, name) values (1,'Marcelo');
insert into users (id, name) values (2,'Seth');

insert into permissions (id,descr) values (1, 'Developer');
insert into permissions (id,descr) values (2, 'Analyst');
insert into permissions (id,descr) values (3, 'Tester');
insert into permissions (id,descr) values (4, 'Manager');

insert into user_permissions (id, user_id, id_permission) values (1,1,1);
insert into user_permissions (id, user_id, id_permission) values (2,1,2);
insert into user_permissions (id, user_id, id_permission) values (3,2,4);
insert into user_permissions (id, user_id, id_permission) values (4,2,2);
insert into user_permissions (id, user_id, id_permission) values (5,2,1);

Now we are ready to start:

create or replace package examples is

-- Author : Marcelo Cure
-- Created : 25/10/2012 11:30:49

--declare a simple record
type r_permissions is record (id number,
descr varchar2(40));
--declare a table of the record type just declared above type tb_permissions is table of r_permissions;

function get_user_permissions (p_user_id in number) return tb_permissions
pipelined;
end examples;
/
create or replace package body examples is
--returns a table of a specific record + pipelined option
function get_user_permissions (p_user_id in number) return tb_permissions pipelined is
--get user permissions
cursor c_permissions is
select b.id, b.descr
from user_permissions a,
permissions b
where a.user_id = p_user_id
and a.id_permission = b.id;

--declare the pipelined table
v_tb_users r_permissions;
v_id number;
v_descr varchar2(40);
begin

open c_permissions;
loop
fetch c_permissions into v_id, v_descr;
exit when c_permissions%notfound;

v_tb_users.id := v_id;
v_tb_users.descr := v_descr;
--inserts a row into the pipelined table
pipe row (v_tb_users);

end loop;
end get_user_permissions;
end examples;
/

Finally to test it, you can use try the following code:

select *
from table(examples.get_user_permissions(1));--passing user_id 1 as parameter