PTF’s offer a row replication feature that automatically adds rows to the result set. There are two steps to implement this; first the DESCRIBE function tells the compiler to be ready to replicate rows (row_replication=>TRUE) and second the FETCH_ROWS procedure sets the number of rows to replicate (replication_factor=>n).
One small, but very important point is the replication_factor defines the total number for rows included – not the number of times to replicate an existing row. Thus if the replication factor is zero then zero rows will be returned as shown in this example:
CREATE OR REPLACE PACKAGE poly_pkg AS FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T ) RETURN DBMS_TF.DESCRIBE_T; PROCEDURE fetch_rows; END; / CREATE OR REPLACE PACKAGE BODY poly_pkg AS FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T ) RETURN DBMS_TF.DESCRIBE_T AS BEGIN -- Dear Compiler, -- Please get ready for row replication. -- Sincerely, -- Developer RETURN DBMS_TF.DESCRIBE_T ( row_replication => true); END; PROCEDURE fetch_rows IS BEGIN -- Dear Executor, -- Please return zero rows no matter what. -- Sincerely, -- Developer DBMS_TF.ROW_REPLICATION(replication_factor => 0); END; END; / CREATE OR REPLACE FUNCTION poly_func(p_tbl IN TABLE ) RETURN TABLE PIPELINED ROW POLYMORPHIC USING poly_pkg;
And here are the results:
-- Querying the PTF returns zero rows SELECT * FROM poly_func(table1); no rows selected -- even though the table has 2 rows SELECT * FROM table1; COL1 COL2 COL3 ---- ---- ---- 19 99 9 19 98 8
Setting the replication factor to a negative number fails with this error.
SELECT * * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error ORA-06512: at "D.POLY_PKG", line 19
A replication factor of one produces this result:
-- replication_factor = 1 SELECT * FROM poly_func(table1); COL1 COL2 COL3 ---- ---- ---- 19 99 9 19 98 8
A replication factor of two produces this result:
-- replication_factor = 2 SELECT * FROM poly_func(table1); COL1 COL2 COL3 ---- ---- ---- 19 99 9 19 99 9 19 98 8 19 98 8
Thanks for reading!