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!

One Reply to “KSCOPE Answer 1 of 2”

  1. Hello Darryl,

    With the modified loop, the output should be as follows:

    YEAR CITY NEW_COL1 NEW_COL2
    ——————————————————–
    2019 Seattle B 10
    2019 Seattle C 20
    2019 Seattle D 30
    2017 San Antonio E 40

    4 rows selected.
    Count = 100

    Something however looks weird in this whole logic:

    In the original script, when you use
    FOR counter IN 1..v_env.row_count LOOP …
    to populate the new columns, it populates 3 rows,
    so this loop is working on the original number of rows, BEFORE the replication.

    If so, then when replicating, I would have expected all the 3 rows
    for ‘Seattle’ to appear with the same values in the new columns
    ( B , 10), and the row for ‘San Antonio’ with values (D, 30).

    Instead, it looks like the replication only “stretches” the rows across the fetched columns, while the new column values remain in their original row locations ( where they were populated before the replication ).

    This gives you indeed the flexibility to populate the new columns
    with different values for each replica of a fetched row,
    but it makes it kind of “hard to know” in which row in the output a value that you populated in row “r” will ultimately appear.
    .
    Hope that more posts will follow, with more examples 🙂

    Thanks a lot & Best Regards,
    Iudith Mentzel

Comments are closed.