The beginning of knowledge is the discovery of something we do not understand. [Frank Herbert]

Menu

I have been working on a presentation on Polymorphic Table Functions. During this time I was looking for a real use case for Polymorphic Table Functions. I came up with an example which is not very useful in real life, but very useful to explain the technique.
At my current job I came across a piece of code that I had to copy and adjust to fit the needs for that specific case. The idea was always the same, I get a table with semi-colon separated values in one column that have to be split into the correct number of columns before checking the data to the current data in a specific table.
I thought: ‘Maybe I can solve this copy-paste-adjust process by using a Polymorphic Table Function.’

Let’s first set the current scene.
We have two tables. The well known EMP and DEPT tables.

To process the data and merge it into the main tables we use a package. We could have used a merge statement, but this implies all the rows that are the same will get an update anyway, which results in a lot of journal-ling data which is done by triggers. Using the EMP and DEPT tables this wouldn’t be too much of a problem, but we are talking 250k+ rows each time (at least once a day).
So we want a little more control and only insert/update when it’s really necessary.

As you can see, the code, especially for the cursors, is pretty much the same. Only difference is the number of columns that are generated from the semi-colon separated line.
I really don’t like to do the same thing over and over again, especially when the only difference is the number of columns and their names. But since this is what changes between the tables I think there is no way of making this generic in 12c or earlier. But then 18c came into play and they provide us with Polymorphic Table Functions.

This is what the documentation says (summary):Polymorphic Table Functions
Polymorphic Table Functions (PTF) are user-defined functions that can be invoked in the FROM clause.
They are capable of processing tables whose row type is not declared at definition time and producing a
result table whose row type may or may not be declared at definition time. Polymorphic Table Functions
allow application developers to leverage the long-defined dynamic SQL capabilities to create powerful
and complex custom functions.

In my own words: Call a function, supplying a table and get a set of columns back. You can supply the names (and number) of columns as a parameter. Also, these columns don’t have to exist in the table, you can create them on the fly. That is exactly what I need. I have different tables with pretty much the same layout but the results I need are completely different.
So I came up with the following Polymorphic Table Function to do what I want. First there is the specification of the package. What I need is the DESCRIBE function (which is mandatory) and a procedure to fetch the rows, where I can alter the results.

create or replace package body separated_ptf as
g_colcount pls_integer; -- save the number of columns requested
g_colname varchar2(128); -- save the name of the first column
function describe(tab in out dbms_tf.table_t
,cols in dbms_tf.columns_t default null) return dbms_tf.describe_t as
-- metadata for column to add
l_new_col dbms_tf.column_metadata_t;
-- table of columns to add
l_new_cols dbms_tf.columns_new_t; -- := DBMS_TF.COLUMNS_NEW_T();
begin
-- Mark the first column ReadOnly and don't display it anymore
tab.column(1).for_read := true;
tab.column(1).pass_through := false;
-- Save the name of the first column for use in the fetch_rows procedure
g_colname := tab.column(1).description.name;
-- Save the number of columns for use in the fetch_rows procedure
g_colcount := cols.count;
-- Add the new columns, as specified in the cols parameter
for indx in 1 .. cols.count loop
-- define metadata for column named cols(indx)
-- that will default to a datatype of varchar2 with
-- a length of 4000
l_new_col := dbms_tf.column_metadata_t(name => cols(indx));
-- add the new column to the list of columns new columns
l_new_cols(l_new_cols.count + 1) := l_new_col;
end loop;
-- Instead of returning NULL we will RETURN a specific
-- DESCRIBE_T that adds new columns
return dbms_tf.describe_t(new_columns => l_new_cols);
end;
procedure fetch_rows is
-- define a table type of varchar2 tables
type colset is table of dbms_tf.tab_varchar2_t index by pls_integer;
-- variable to hold the rowset as retrieved
l_rowset dbms_tf.row_set_t;
-- variable to hold the number of rows as retrieved
l_rowcount pls_integer;
-- variable to hold the new values
l_newcolset colset;
begin
-- fetch rows into a local rowset
-- at this point the rows will have columns
-- from the the table/view/query passed in
dbms_tf.get_row_set(l_rowset, l_rowcount);
-- for every row in the rowset...
for rowindx in 1 .. l_rowcount loop
-- for every column
for colindx in 1 .. g_colcount loop
-- split the row into separate values
-- splitting the regexp way: http://nuijten.blogspot.com/2009/07/splitting-comma-delimited-string-regexp.html
l_newcolset(colindx)(rowindx) := trim(regexp_substr(json_value(dbms_tf.row_to_char(l_rowset, rowindx), '$.' || g_colname)
,'[^;]+'
,1
,colindx));
end loop; -- every column
end loop; -- every row in the rowset
-- add the newly populated columns to the rowset
for indx in 1 .. g_colcount loop
dbms_tf.put_col(columnid => indx, collection => l_newcolset(indx));
end loop;
end;
end separated_ptf;
/

After creating this Polymorphic Table Function we need an interface to use it in a SQL statement:

There is absolutely some improvement possible to the current implementation, like supporting duplicate separators, making the column to be split up a parameter, making the separator character a parameter as well, but that is a nice project for a later time.

I hope it all makes a bit of sense. If you have any improvements, don’t hesitate to comment.