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!