Simple demonstration
The first step is to create a type that defines the names and data types (attributes) of the returned columns:
create or replace type tq84_pipeline_columns as object (
num number,
txt varchar2(10),
dat date
)
/
This type is used to create a table-table:
create or replace type tq84_pipeline_table as table of tq84_pipeline_columns
/
The function returns the table type and is declared PIPELINED:
create or replace function tq84_pipeline_func(nofRecords in integer)
return tq84_pipeline_table
PIPELINED
as begin
for r in 1..nofRecords loop
pipe row (
tq84_pipeline_columns(
r,
to_char(r, 'fmRN'), -- Roman numerals (use fm to trim leading white space)
sysdate - r
));
end loop;
end tq84_pipeline_func;
/
With this function, we can query an arbitrary number of records whose data is create by the PL/SQL function in memory.
Before Oracle 12c, the name of the function needed to be enclosed in a table(…) clause. Since 12c, this is optional.
select * from table(tq84_pipeline_func(9));
Finally, we clean up the
objects we created:
drop type tq84_pipeline_table;
drop type tq84_pipeline_columns;
drop function tq84_pipeline_func;