Day: November 25, 2018

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!