Oracle Pipelined Table Functions

Oracle Pipelined Table Functions
		Version 10.2

General
Data dictionary Objects	source$ dba_procedures all_procedures user_procedures dba_source all_source user_source dba_types all_types user_types

System Privileges	alter any procedure create any procedure create procedure debug any procedure drop any procedure execute any procedure select any table
Tables And Data For Demo
Table Definition	CREATE TABLE stocktable ( ticker       VARCHAR2(4), open_price   NUMBER(10), close_price  NUMBER(10));
Demo Data	INSERT INTO stocktable VALUES ('ORA', 13, 14); INSERT INTO stocktable VALUES ('MSC', 85, 92); INSERT INTO stocktable VALUES ('SUN', 23, 18); COMMIT;
Type Definition	CREATE OR REPLACE TYPE TickerType AS OBJECT ( ticker     VARCHAR2(4), pricetype  VARCHAR2(1), price      NUMBER(10)); /
Create Table Type	CREATE OR REPLACE TYPE TickerTypeSet AS TABLE OF TickerType; /
Create Package	CREATE OR REPLACE PACKAGE refcur_pkg IS TYPE refcur_t IS REF CURSOR RETURN StockTable%ROWTYPE; END refcur_pkg; /
Create Table Function	CREATE OR REPLACE FUNCTION stockpivot( p refcur_pkg.refcur_t) RETURN TickerTypeSet PIPELINED IS out_rec  TickerType := TickerType(NULL,NULL,NULL); in_rec   p%ROWTYPE; BEGIN   LOOP     FETCH p INTO in_rec;     EXIT WHEN p%NOTFOUND;     out_rec.ticker := in_rec.Ticker;     out_rec.pricetype := 'O';     out_rec.price := in_rec.Open_Price;     PIPE ROW(out_rec);     out_rec.PriceType := 'C';     out_rec.price := in_rec.Close_Price;     PIPE ROW(out_rec);   END LOOP;   CLOSE p;   RETURN; END stockpivot; / desc stockpivot col pipelined format a10 SELECT object_name, pipelined, authid FROM user_procedures;
Sample Query	SELECT * FROM TABLE(stockpivot(CURSOR(SELECT * FROM StockTable)));
Generate Date List	CREATE OR REPLACE TYPE date_array AS TABLE OF DATE; / CREATE OR REPLACE FUNCTION date_table(sdate DATE, edate DATE) RETURN date_array PIPELINED AS BEGIN   FOR i IN 0 .. (edate - sdate)   LOOP     PIPE ROW(sdate + i);   END LOOP;   RETURN; END date_table; / desc date_table col pipelined format a10 SELECT object_name, pipelined, authid FROM user_procedures; SELECT * FROM TABLE(CAST(date_table(TRUNC(SYSDATE-30), TRUNC(SYSDATE)) AS date_array)); -- joined with another table CREATE TABLE testdata ( datecol  DATE, someval  NUMBER); INSERT INTO testdata VALUES (TRUNC(SYSDATE-25), 25); INSERT INTO testdata VALUES (TRUNC(SYSDATE-20), 20); INSERT INTO testdata VALUES (TRUNC(SYSDATE-15), 15); INSERT INTO testdata VALUES (TRUNC(SYSDATE-10), 10); INSERT INTO testdata VALUES (TRUNC(SYSDATE-5), 5); COMMIT; SELECT * FROM testdata; SELECT da.column_value AS DATECOL, td.someval FROM TABLE(CAST(date_table(TRUNC(SYSDATE-30), TRUNC(SYSDATE)) AS date_array)) da, testdata td WHERE da.COLUMN_VALUE = td.datecol(+);
Turning a comma delmimited list into a row-by-row output	CREATE OR REPLACE TYPE str_array AS TABLE OF VARCHAR2(10); / CREATE OR REPLACE FUNCTION tf(stringin VARCHAR2) RETURN str_array PIPELINED IS  i    PLS_INTEGER;  str  VARCHAR2(100);  tab  sys.dbms_utility.uncl_array; BEGIN   str := '"' || REPLACE(stringin, ',', '","') || '"';   sys.dbms_utility.comma_to_table(str, i, tab);   FOR j IN 1 .. 5 LOOP     PIPE ROW(TRANSLATE(tab(j),'A"','A'));   END LOOP;   RETURN; END tf; / SELECT * FROM TABLE(CAST(tf('1001,1002,1003,1004,1005') AS str_array));
Drop Pipelined Table Functions
Drop PTF	DROP FUNCTION <function_name>;
	DROP FUNCTION stockpivot;