At the recent ODTUG KSCOPE conference I used this script to demonstrate 3 concepts:
- Records fetched by a Polymorphic Table Function (PTF) can be removed from the output by specifying a replication factor of zero
- Values for added columns can be replicated across returned rows
- Great care is required to coordinate the replication of fetched rows and added columns
The output of the script was this:
YEAR CITY COL1 COL2 ---- ---------- ---- ---- 2019 Seattle B 10 2019 Seattle C 20 2019 Seattle D 30 2017 San Antonio
The PTF fetched 3 rows; then replicated one of them 3 times (Seattle), removed one of them (Orlando) and replicated one of them (San Antonio) once for a total of four output rows. But the rows of added columns (COL1 and COL2) were not replicated enough so the final row had NULL values for them.
A thoughtful attendee asked if the opposite (more added rows than fetched rows could ever occur). The answer is no but I did not have time to demonstrate it so I’m doing that with this post.
I made the following change to add 100 rows to the added columns no matter what.
FOR counter IN 1..100 LOOP -- add a numeric value for NEW_COL1 in the row v_n_t.tab_number(counter) := 10 * counter;
And then after the loop I added this:
DBMS_OUTPUT.PUT_LINE('Count = ' || v_n_t.tab_number.COUNT);
Now the output is this:
YEAR CITY COL1 COL2 ---- ---------- ---- ---- 2019 Seattle B 10 2019 Seattle C 20 2019 Seattle D 30 2017 San Antonio Count = 100
The number of rows returned was not influenced by extra rows of added columns.
Thanks for reading!