Posted on

KSCOPE Answer 1 of 2

At the recent ODTUG KSCOPE conference I used this script to demonstrate 3 concepts:

  1. Records fetched by a Polymorphic Table Function (PTF) can be removed from the output by specifying a replication factor of zero
  2. Values for added columns can be replicated across returned rows
  3. 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!