The previous 2 posts demonstrated options for row replication at the PTF or row set level. This post demonstrates the third option – setting replication at the row level.
First up is some demo data.
CREATE TABLE table1 ( col1 NUMBER NOT NULL PRIMARY KEY ); INSERT INTO table1 VALUES(2018); INSERT INTO table1 VALUES(2019); INSERT INTO table1 VALUES(2020);
Most of code (package, function, etc) for this post is borrowed from the prior post except for the FETCH_ROWS procedure. Here’s the first version of it introducing the TAB_NATURALN_T datatype to store replication values for each row in the data set. In this case we set the replication as follows:
- 1 for the first row (2018)
- 2 for the second row (2019)
- 0 for the third row (2020)
PROCEDURE fetch_rows IS v_row_set DBMS_TF.ROW_SET_T; v_rep_tab DBMS_TF.TAB_NATURALN_T; BEGIN v_rep_tab(1) := 1; v_rep_tab(2) := 2; v_rep_tab(3) := 0; DBMS_TF.GET_ROW_SET( rowset => v_row_set ); DBMS_TF.PUT_ROW_SET( rowset => v_row_set, replication_factor => v_rep_tab ); END;
And here are the results.
SELECT * FROM poly_func(table1); COL1 ---- 2018 2019 2019 3 rows selected.
2018 replicated one time, 2019 replicated two times and 2020 replicated zero times.
Next up is:
- 1 for the first row (2018)
- 2 for the second row (2019)
- no value for the third row (2020)
PROCEDURE fetch_rows IS v_row_set DBMS_TF.ROW_SET_T; v_rep_tab DBMS_TF.TAB_NATURALN_T; BEGIN v_rep_tab(1) := 1; v_rep_tab(2) := 2; DBMS_TF.GET_ROW_SET( rowset => v_row_set ); DBMS_TF.PUT_ROW_SET( rowset => v_row_set, replication_factor => v_rep_tab ); END;
And the results:
SELECT * FROM poly_func(table1); COL1 ---- 2018 2019 2019 2020 4 rows selected.
2018 replicated ones time, 2019 replicated two times and 2020 replicated one time.
And here is where things get interesting:
- 1 for the first row (2018)
- no value for the second row (2019)
- 3 for the third row (2020)
PROCEDURE fetch_rows IS v_row_set DBMS_TF.ROW_SET_T; v_rep_tab DBMS_TF.TAB_NATURALN_T; BEGIN v_rep_tab(1) := 1; v_rep_tab(3) := 3; DBMS_TF.GET_ROW_SET( rowset => v_row_set ); DBMS_TF.PUT_ROW_SET( rowset => v_row_set, replication_factor => v_rep_tab ); END;
And the surprising results…
SELECT * FROM poly_func(table1); COL1 ---- 2018 2019 2020 3 rows selected.
It appears that a gap in the entries in the natural table causes the replication factor to be ignored altogether producing one of each row. I’m not sure if that is a bug or the intended behaviour.
Thanks for reading!