Table Polymorphism

All of the previous posts in this series relied on hand written code to group values together. Polymorphic Table Functions offer a way to do the grouping automatically. Its referred to as TABLE POLYMORPHISM and here is an introductory post.

First up – some demo data.

CREATE TABLE table1
( col1 NUMBER );

BEGIN
  FOR x IN 1..3 LOOP
    INSERT INTO table1
    VALUES(x);
  END LOOP;
END;

And a familiar package.

CREATE OR REPLACE PACKAGE poly_ptf AS

  FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T )
    RETURN DBMS_TF.DESCRIBE_T;
  PROCEDURE open;
  PROCEDURE fetch_rows;
  PROCEDURE close;

END;

And an equally familiar package body.

CREATE OR REPLACE PACKAGE BODY poly_ptf AS

  FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T )
    RETURN DBMS_TF.DESCRIBE_T IS
  BEGIN
    FOR counter IN 1..p_tbl.column.count LOOP
      p_tbl.column(counter).for_read := TRUE;
    END LOOP;
    RETURN NULL;
  END;

  PROCEDURE open IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('open');
  END;

  PROCEDURE fetch_rows IS
    v_rowset DBMS_TF.ROW_SET_T;
  BEGIN
    DBMS_TF.GET_ROW_SET(v_rowset);
    DBMS_OUTPUT.PUT_LINE('fetch rows');
    DBMS_TF.TRACE(v_rowset);
  END;

  PROCEDURE close IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('close');
  END;

END;

And the function. Note that TABLE is specified instead of ROW. This means that rows passed into the PTF can be processed in groups. Notice that I said they can be – this is not a requirement, it is also not the default.

CREATE OR REPLACE FUNCTION poly_func(p_tbl IN TABLE )
  RETURN TABLE PIPELINED
  TABLE POLYMORPHIC USING poly_ptf;

And now for the query. Notice that PARTITION BY is specified. This means that rows passed in will be grouped by their col1 values. So what does grouping mean? Check the results below.

SELECT *
  FROM poly_func(table1 PARTITION BY col1);

COL1
----
   1
   2
   3

open
fetch rows
.... [1] = {"COL1":1}
close
open
fetch rows
.... [1] = {"COL1":2}
close
open
fetch rows
.... [1] = {"COL1":3}
close

The OPEN, FETCH_ROWS and CLOSE procedures are executed separately (partitioned) for each of the values in COL1.

I’ll continue exploring this behaviour in future posts.

Thanks for reading!

2 Replies to “Table Polymorphism”

  1. Hello Darryl,
    I have just found something interesting, related to your “big totals” script
    in LiveSQL, that maybe also relates to this post.

    Trying to check the question that you left open at the end of that script,
    I found something really strange:
    If you add a control message to be displayed in the OPEN procedure of the first package, you will see that in the case of an empty PTF_TABLE,
    the OPEN procedure does NOT execute at all … which is very strange.
    But, somehow it looks “consistent” with your findings in this current post, namely, if you use PARTITION BY, then the OPEN executes for each partition … which is kind of “similar” to say that, if the table is empty and therefore has no partitions, there will be no OPEN executed at all !

    In your last SELECT, with the WITH clause:

    WITH x AS ( SELECT *
    FROM ptf_poly_func(ptf_table) )
    SELECT *
    FROM totals_func(dual)
    /

    your main SELECT does NOT reference the “X” table, so the summarizing is NOT reexecuted and you still get the previous totals stored in the package state.

    Strangely enough, while I performed my tests in LiveSQL, the package state kept being discarded if I did not perform all the steps in a single “run” sequence.

    So, trying to put things together, with the 3 records in the table,
    when I first tried to execute the following, I got an error:

    WITH x AS ( SELECT *
    FROM ptf_poly_func(ptf_table) )
    SELECT *
    FROM x
    UNION ALL
    SELECT *
    FROM totals_func(dual)
    /

    ORA-30732: table contains no user-visible columns

    This SELECT only worked if in the SAME run, I executed first the two
    separate SELECT’s.
    In principle, it looks to me that the UNION select above cannot work by itself, because the column names required for parsing the 2nd branch
    of the UNION are only created during the CLOSE of the 1st branch,
    which is “too late”.
    If I executing the separate SELECT’s first, then the UNION also worked.

    But, when the table is empty, then the OPEN/CLOSE procedures are NOT executed, so, on the one hand, the totals are NOT initialized, and on the other hand, the columns for the summary SELECT are not in place in the package state when that SELECT is parsed.

    I hope that I was able to explain what I intended.

    Anyway, it looks strange that for an empty table the OPEN procedure
    is not executed at all !
    Maybe you will want to investigate this issue further.

    Thanks a lot for all your posts on the PTF topic, which are very instructive 🙂

    Best Regards,
    Iudith Mentzel

  2. Thanks for the detailed analysis and reply. You have provided some more clues to figuring this one out. I have purposely avoided it for the last few weeks with a plan to review with fresh eyes. Hopefully in the next few weeks.

Comments are closed.