Ploymorphic table functions have the ability to return N rows for every input row. N is called the "replication factor". It can be 0, 1, or any other positive integer.
Setting the replication factor is a two step process.
- in describe() set ROW_REPLICATION = TRUE
in fetch_rows() do one of the following:
- call DBMS_TF.ROW_REPLICATION()
- call PUT_ROW_SET( REPLICATION_FACTOR => ... )
ROW_REPLICATION()
Let's look at an exmaple using the ROW_REPLICATION() method.
create or replace package ptf_demo as
function describe(
p_input_table_metadata in out dbms_tf.table_t,
p_replication_factor in pls_integer )
return
dbms_tf.describe_t ;
procedure fetch_rows( p_replication_factor pls_integer ) ;
function replicate_rows( p_table table, p_replication_factor pls_integer )
return table pipelined row polymorphic using ptf_demo ;
end ptf_demo ;
/
create or replace package body ptf_demo as
function describe(
p_input_table_metadata in out dbms_tf.table_t,
p_replication_factor in pls_integer )
return
dbms_tf.describe_t
as
begin
--
-- step (1) set ROW_REPLICATION = TRUE
--
return dbms_tf.describe_t( row_replication => true ) ;
end describe ;
procedure fetch_rows( p_replication_factor pls_integer ) as
begin
--
-- step (2) set the REPLICATION FACTOR
--
DBMS_TF.ROW_REPLICATION( P_REPLICATION_FACTOR );
end fetch_rows ;
end;
/
Let's test the PTF using various replication factors.
select *
from
ptf_demo.replicate_rows(
p_table => scott.dept,
p_replication_factor => 0 ) ;
no rows selected
select *
from
ptf_demo.replicate_rows(
p_table => scott.dept,
p_replication_factor => 1 ) ;
DEPTNO DNAME LOC
---------- ---------- ----------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
select *
from
ptf_demo.replicate_rows(
p_table => scott.dept,
p_replication_factor => 2 ) ;
DEPTNO DNAME LOC
---------- ---------- ----------
10 ACCOUNTING NEW YORK
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
20 RESEARCH DALLAS
30 SALES CHICAGO
30 SALES CHICAGO
40 OPERATIONS BOSTON
40 OPERATIONS BOSTON
PUT_ROW_SET( REPLICATION_FACTOR => ... )
Now let's look at an exmple using the PUT_ROW_SET( REPLICATION_FACTOR => ... ) method. We only need to change the package body for this example. The package specification remains the same as above.
create or replace package body ptf_demo as
function describe(
p_input_table_metadata in out dbms_tf.table_t,
p_replication_factor in pls_integer )
return
dbms_tf.describe_t
as
begin
--
-- step (1) set ROW_REPLICATION = TRUE
--
return dbms_tf.describe_t( ROW_REPLICATION => TRUE ) ;
end describe ;
procedure fetch_rows( p_replication_factor pls_integer ) as
v_input_row_set dbms_tf.row_set_t ;
begin
dbms_tf.get_row_set( rowset => v_input_row_set ) ;
--
-- step (2) set the REPLICATION_FACTOR
--
dbms_tf.put_row_set( rowset => v_input_row_set, REPLICATION_FACTOR => P_REPLICATION_FACTOR );
end fetch_rows ;
end;
/
select *
from
ptf_demo.replicate_rows(
p_table => scott.dept,
p_replication_factor => 3 ) ;
DEPTNO DNAME LOC
---------- ---------- ----------
10 ACCOUNTING NEW YORK
10 ACCOUNTING NEW YORK
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
20 RESEARCH DALLAS
20 RESEARCH DALLAS
30 SALES CHICAGO
30 SALES CHICAGO
30 SALES CHICAGO
40 OPERATIONS BOSTON
40 OPERATIONS BOSTON
40 OPERATIONS BOSTON
Matching the number of output rows to the number of input rows
We need to ensure the number of rows in our output row set accounts for the replication factor. Here's what happens when we don't. (The following code is a deliberate example of a bug. The output will not appear the way we actually want it to.)
create or replace package body ptf_demo as
function describe(
p_input_table_metadata in out dbms_tf.table_t,
p_replication_factor in pls_integer )
return
dbms_tf.describe_t
as
v_new_column_desc dbms_tf.describe_t ;
begin
p_input_table_metadata.column(1).for_read := true ;
--
-- define a new column
--
v_new_column_desc.new_columns(1).name := 'INTROSPECTION' ;
v_new_column_desc.new_columns(1).type := dbms_tf.type_varchar2 ;
v_new_column_desc.new_columns(1).max_len := 100 ;
-- enable row replication
v_new_column_desc.row_replication := true ;
return v_new_column_desc ;
end describe ;
procedure fetch_rows( p_replication_factor pls_integer ) as
v_for_read_column dbms_tf.tab_number_t ;
v_new_column dbms_tf.tab_varchar2_t ;
i pls_integer ;
begin
dbms_tf.get_col( 1, v_for_read_column );
--
-- loop through the for_read rows and set new column values
--
i := v_for_read_column.first ;
while i is not null loop
-- this next command demonstrates the wrong way to generate output
-- rows when replication is used
v_new_column(i) :=
'for deptno #' || v_for_read_column(i) ||
', input row #' || i ||
', output row #' || i ;
i := v_for_read_column.next(i) ;
end loop ;
dbms_tf.row_replication( p_replication_factor );
dbms_tf.put_col( 1, v_new_column );
end fetch_rows;
end;
/
select
deptno, dname, loc, introspection
from
ptf_demo.replicate_rows(
p_table => scott.dept,
p_replication_factor => 2 ) ;
DEPTNO DNAME LOC INTROSPECTION
---------- ---------- ---------- ------------------------------------------------------------
10 ACCOUNTING NEW YORK for deptno #10, input row #1, output row #1
10 ACCOUNTING NEW YORK for deptno #20, input row #2, output row #2
20 RESEARCH DALLAS for deptno #30, input row #3, output row #3
20 RESEARCH DALLAS for deptno #40, input row #4, output row #4
30 SALES CHICAGO
30 SALES CHICAGO
40 OPERATIONS BOSTON
40 OPERATIONS BOSTON
Note how the number of output rows (8) does not match the number of input rows (4). Note also the mismatch between DEPTNO values displayed in the output column (INTROSPECTION) and those of the input column (DEPTNO).
This solution works better.
create or replace package body ptf_demo as
function describe(
p_input_table_metadata in out dbms_tf.table_t,
p_replication_factor in pls_integer )
return
dbms_tf.describe_t
as
v_new_column_desc dbms_tf.describe_t ;
begin
p_input_table_metadata.column(1).for_read := true ;
--
-- define a new column
--
v_new_column_desc.new_columns(1).name := 'INTROSPECTION' ;
v_new_column_desc.new_columns(1).type := dbms_tf.type_varchar2 ;
v_new_column_desc.new_columns(1).max_len := 100 ;
-- enable row replication
v_new_column_desc.row_replication := true ;
return v_new_column_desc ;
end describe ;
procedure fetch_rows( p_replication_factor pls_integer ) as
v_for_read_column dbms_tf.tab_number_t ;
v_new_column dbms_tf.tab_varchar2_t ;
v_input_row_number pls_integer ;
v_output_row_number pls_integer := 1 ;
begin
dbms_tf.get_col( 1, v_for_read_column );
--
-- loop through the for_read rows and set new column values
--
v_input_row_number := v_for_read_column.first ;
while v_input_row_number is not null loop
for v_copy_number in 1 .. p_replication_factor loop
v_new_column(v_output_row_number) :=
'for deptno #' || v_for_read_column(v_input_row_number) ||
', input row #' || v_input_row_number ||
', copy #' || v_copy_number ||
', output row #' || v_output_row_number ;
v_output_row_number := v_output_row_number + 1 ;
end loop ;
v_input_row_number := v_for_read_column.next(v_input_row_number) ;
end loop ;
dbms_tf.row_replication( p_replication_factor );
dbms_tf.put_col( 1, v_new_column );
end fetch_rows;
end;
/
select
deptno, dname, loc, introspection
from
ptf_demo.replicate_rows(
p_table => scott.dept,
p_replication_factor => 2 ) ;
DEPTNO DNAME LOC INTROSPECTION
---------- ---------- ---------- ------------------------------------------------------------
10 ACCOUNTING NEW YORK for deptno #10, input row #1, copy #1, output row #1
10 ACCOUNTING NEW YORK for deptno #10, input row #1, copy #2, output row #2
20 RESEARCH DALLAS for deptno #20, input row #2, copy #1, output row #3
20 RESEARCH DALLAS for deptno #20, input row #2, copy #2, output row #4
30 SALES CHICAGO for deptno #30, input row #3, copy #1, output row #5
30 SALES CHICAGO for deptno #30, input row #3, copy #2, output row #6
40 OPERATIONS BOSTON for deptno #40, input row #4, copy #1, output row #7
40 OPERATIONS BOSTON for deptno #40, input row #4, copy #2, output row #8
This time the PTF correctly generated 8 output rows and the DEPTNO values in the output column match the input values.