This Oracle tutorial shows how to create Pipelined function. Follow these steps:
1. Create a Type Object
For Oracle pipelined function, we must have a type object through which we can send the rows. Create one as below:
CREATE OR REPLACE TYPE t_list AS TABLE OF VARCHAR2(2000);
/
2. Create a Stored Function as PIPELINED
The following function uses the above type to return the values. And rows produce one by one through a cursor based on a SQL query:
CREATE OR REPLACE FUNCTION get_values RETURN t_list
PIPELINED
IS l_list t_list;
w_row_count NUMBER := 0;
BEGIN
for cur in
( SELECT 'LINE' || level as value
FROM dual CONNECT BY
level <= 20
) loop
PIPE ROW ( cur.value );
w_row_count := w_row_count + 1;
end loop;
dbms_output.put_line('Total ' || w_row_count || ' rows selected');
END get_values;
/
3. Call The Function Using SQL Query
Now finally, we can call the above function and can get the rows as desired:
select * from TABLE(get_values());
Output:
COLUMN_VALUE |
---|
LINE1 |
LINE2 |
LINE3 |
LINE4 |
LINE5 |
LINE6 |
LINE7 |
LINE8 |
LINE9 |
LINE10 |
… |
10 rows of 20