Polymorphic Table Functions
I am a Certified Senior Oracle Developer/Data Architect with a passion for all things data and an advocate for the Oracle APEX low code platform that I have seen mature to become a fully-fledged enterprise level solution. I have several decades of experience in multiple industries such as Finance, Energy and the Public Sector and hope that I can impart some of that experience in my blog posts.
Introduced in Oracle 18c, the wonderfully named Polymorphic Table Function (PTF) feature took pipelined functions to a new level of abstraction. You may have heard the term Polymorphic used in object-oriented programming to define a function/object/variable as being able to morph into many different types or forms, so what use do they actually offer us in Oracle?
If you are familiar with pipelined functions, you will know that you can stream rows of data from the PLSQL engine to the SQL engine when used in the FROM clause. This allows you to implement your own logic in PLSQL to manipulate the output in whatever way you wish before it is streamed as a pseudo-table of data. An example of this is where you have an external table that you want to transform and load into conventional heap table and this is one mechanism to achieve that. The steps would be:
Create an user defined object type to represent the output that the function will return. Typically you would define the row output as a RECORD object and then the array/collection object type AS TABLE OF this row.
Create the pipelined function that would accept a cursor parameter that would be the external table's data shape, returning the object created in the prior step.
Implement all the transformation logic in the body of the pipelined function.
Use the pipelined function in the FROM clause of an INSERT INTO..SELECT..FROM TABLE(mypipelinedfunc) statement.
PTF's are very much like pipelined function with the key difference being that you do not have to define the output or the input. Data can be shaped in any form as input and returned in any form as output. Basically, they are pipelined functions on steroids.
I have to say that I struggled to find a good use case for them when they were first introduced as there were few problems that couldn't be solved with pure SQL, traditional pipelined functions or later, by SQL Macros. So, I decided to finally pay PTF's some attention when I wondered if they could be used to overcome a minor limitation in Oracle APEX collections; the fact they accept a maximum of 50 VARCHAR2 columns.
I previously wrote this blog that has some background as to what APEX collections are and what they do for you in an Oracle APEX session; essentially they are a generic global temporary table solution. It is rare that you would ever need to use more than 50 VARCHAR2 columns in a collection but as a use-case for a PTF I put this package together to see if a generic solution could be created.
Here's the package specification:
CREATE OR REPLACE PACKAGE APEX_COLL_PTF_PKG AS
FUNCTION SPREAD_TO_COLLECTIONS
(
TAB IN TABLE,
KEY_COLUMN IN COLUMNS
) RETURN TABLE
PIPELINED ROW POLYMORPHIC USING APEX_COLL_PTF_PKG;
FUNCTION DESCRIBE
(
TAB IN OUT DBMS_TF.TABLE_T,
KEY_COLUMN IN DBMS_TF.COLUMNS_T
) RETURN DBMS_TF.DESCRIBE_T;
PROCEDURE FETCH_ROWS;
PROCEDURE CLOSE;
END APEX_COLL_PTF_PKG;
PTF's must have a DESCRIBE function and, optionally, FETCH_ROWS, CLOSE and OPEN procedures. The DBMS_TF package plays a major role in PTF's.
The SPREAD_TO_COLLECTIONS function is my Polymorphic Table Function that just happens to reside in the same package for this demonstration. I will leave the package body at the end of this blog as it is quite long but this is what the package does:
The DESCRIBE function is a passthrough that allows FETCH_ROWS to see all values passed in. It also stores the key column parameter passed in so it can be used later.
FETCH_ROWS is where the work is done, it takes the records fed into the PTF and parses the metadata about the "shape" of the data. If it has more than 50 columns, it will use multiple collections and spread the data across them.
The APEX_COLLECTION API is leveraged to create as many collections as needed to hold all the columns, naming them 'COLL_nnn'. It uses column C001 in each collection to hold the common key value that DESCRIBE stored so that these collections can later be joined together in SQL.
We also create a helper collection called 'COLL_META' that stores the mappings of the data's columns and which collection they ended up in.
The rest of the function is looping through the data, fanning it out into one or more collections using the APEX_COLLECTION.ADD_MEMBERS function. This is the procedure that is the bulk load approach APEX offers for collection manipulation.
Just to reiterate, this PTF would rightly raise ORA-14551 when run due to the implicit DML being performed by the use of the APEX API, but I forced it to circumvent this by using an autonomous transaction.
So how would this be executed? As it's a PTF, I cannot call it as a standalone function, it has to be part of a SQL statement. So not only is the package a demonstration of bad practice, it also means you have to run it counter-intuitively via a SELECT statement for it to load data. Yuk!
Make sure you are in an APEX session, either in an application or from the database having run APEX_SESSION.CREATE_SESSION. Then you can run the SQL statement that will populate the collections.
WITH SRC AS
(SELECT *
FROM MY_VIEW_WITH_55_COLUMNS)
SELECT SUM(1)
FROM APEX_COLL_PTF_PKG.SPREAD_TO_COLLECTIONS(SRC, COLUMNS(TABLE_PK));
I use a CTE as that allows me to add any WHERE predicates or refine the query further before passing it to the PTF function SPREAD_TO_COLLECTIONS. Additionally, I pass in the key column that I happen to have imaginatively named as TABLE_PK in my source view. The SUM(1) is used to ensure that all rows are processed into the collections in one pass as depending on your client, it may only process a subset if you simply do a SELECT *.
Having executed this query I can now use the COLL_META collection to help me build my 55 column-wide query that will now make use of the two collections it created.
-- SQL to generate the column list
SELECT AC.SEQ_ID,
AC.C001,
C002,
'AC' || REGEXP_REPLACE(AC.C001, '[^[:digit:]]') || '.' || 'C' || TO_CHAR(N001, 'FM000') || ' AS ' || AC.C002 || ',' AS COLNO
FROM APEX_COLLECTIONS AC
WHERE AC.COLLECTION_NAME = 'COLL_META';
Here is a sample of the final SQL I used for extracting the data from the now populated collections:
SELECT AC001.C001 AS "TABLE_PK",-- columns from coll 1
AC001.C002 AS "SOME_COLUMN2",
AC001.C003 AS "SOME_COLUMN3",
........
AC001.C050 AS "SOME_COLUMN50",-- end coll 1
AC002.C002 AS "SOME_COLUMN51",-- columns from coll 2
AC002.C003 AS "SOME_COLUMN52",
AC002.C004 AS "SOME_COLUMN53",
AC002.C005 AS "SOME_COLUMN54",
AC002.C006 AS "SOME_COLUMN55"
FROM APEX_COLLECTIONS AC001
JOIN APEX_COLLECTIONS AC002
ON AC001.C001 = AC002.C001 -- join on key column in C001
AND AC002.COLLECTION_NAME = 'COLL_002'
WHERE AC001.COLLECTION_NAME = 'COLL_001';
This worked as I had hoped, I now had all 55 columns returned and the collections populated. If that initial query in the CTE was complex and slow or it "didn't play well" with an APEX interactive report, now that the data is in the equivalent of a global temporary table, I can save on re-querying the data if the user applies a filter, changes the column selection, and so on.
To conclude, the this was a useful exercise to play with PTF's but it is no more than a demonstration of their capability and should not be seen as a viable approach for the use case posed in this blog because:
Executing DML in a SELECT is bad, bad, bad. Hiding implicit DML in a function that advertises itself as a data source is irresponsible at best, and negligent development.
Performance on large datasets is bad. Even though we are using the "bulk" ADD_MEMBERS procedure, it still has to process row by row prior to taking advantage of this API.
The final SQL that joins multiple collections does not scale particularly well in practice.
Hopefully, for these rare cases, the Oracle APEX team will offer more than 50 columns in a future release.
Here is the package body:
CREATE OR REPLACE PACKAGE BODY APEX_COLL_PTF_PKG AS
---------------------------------------------------------------------------
-- Each APEX collection has C001..C050.
-- C001 is reserved for the key value, so source data uses C002..C050.
---------------------------------------------------------------------------
C_GROUP_DATA_COLS CONSTANT PLS_INTEGER := 49;
---------------------------------------------------------------------------
-- Track whether collections have been initialized for this PTF execution.
---------------------------------------------------------------------------
TYPE T_BOOL_BY_XID IS TABLE OF BOOLEAN INDEX BY VARCHAR2(1024);
G_INITIALIZED_BY_XID T_BOOL_BY_XID;
---------------------------------------------------------------------------
-- Normalize simple, non-quoted column names.
---------------------------------------------------------------------------
FUNCTION NORMALIZE_STRING(P_NAME IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
RETURN UPPER(TRIM(BOTH '"' FROM P_NAME));
END NORMALIZE_STRING;
---------------------------------------------------------------------------
-- Data collection names.
---------------------------------------------------------------------------
FUNCTION COLL_NAME(P_GROUP IN PLS_INTEGER) RETURN VARCHAR2 IS
BEGIN
RETURN 'COLL_' || LPAD(P_GROUP, 3, '0');
END COLL_NAME;
---------------------------------------------------------------------------
-- Convert DBMS_TF column value to VARCHAR2.
-- Character values going into APEX collection Cxxx columns are capped at 4000.
---------------------------------------------------------------------------
FUNCTION COL_TO_VARCHAR2
(
P_COL IN DBMS_TF.COLUMN_DATA_T,
P_ROW IN PLS_INTEGER
) RETURN VARCHAR2 IS
BEGIN
RETURN SUBSTR(DBMS_TF.COL_TO_CHAR(P_COL, P_ROW), 1, 4000);
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END COL_TO_VARCHAR2;
---------------------------------------------------------------------------
-- DESCRIBE runs at SQL parse/compile time.
--
-- KEY_COLUMN must be supplied as:
--
-- COLUMNS(TABLE_PK)
--
-- not as:
--
-- 'TABLE_PK'
---------------------------------------------------------------------------
FUNCTION DESCRIBE
(
TAB IN OUT DBMS_TF.TABLE_T,
KEY_COLUMN IN DBMS_TF.COLUMNS_T
) RETURN DBMS_TF.DESCRIBE_T IS
L_FOUND BOOLEAN := FALSE;
L_KEY VARCHAR2(128);
L_DESC DBMS_TF.DESCRIBE_T;
L_CSTORE_CHR DBMS_TF.CSTORE_CHR_T;
BEGIN
IF KEY_COLUMN.COUNT <> 1 THEN
RAISE_APPLICATION_ERROR(-20000,
'Exactly one key column must be supplied, for example COLUMNS(TABLE_PK)');
END IF;
L_KEY := NORMALIZE_STRING(KEY_COLUMN(1));
FOR I IN 1 .. TAB.COLUMN.COUNT LOOP
--------------------------------------------------------------------
-- Required so FETCH_ROWS can see the incoming column values.
--------------------------------------------------------------------
TAB.COLUMN(I).FOR_READ := TRUE;
--------------------------------------------------------------------
-- Keep the original input row shape/output unchanged.
--------------------------------------------------------------------
TAB.COLUMN(I).PASS_THROUGH := TRUE;
IF NORMALIZE_STRING(TAB.COLUMN(I).DESCRIPTION.NAME) = L_KEY THEN
L_FOUND := TRUE;
END IF;
END LOOP;
IF NOT L_FOUND THEN
RAISE_APPLICATION_ERROR(-20001, 'Key column "' || KEY_COLUMN(1) || '" not found in PTF input');
END IF;
------------------------------------------------------------------------
-- COLUMNS arguments are not passed to FETCH_ROWS.
-- Store the key column name in CSTORE so FETCH_ROWS can retrieve it.
------------------------------------------------------------------------
L_CSTORE_CHR('KEY_COLUMN') := KEY_COLUMN(1);
L_DESC.CSTORE_CHR := L_CSTORE_CHR;
RETURN L_DESC;
END DESCRIBE;
---------------------------------------------------------------------------
-- FETCH_ROWS may run multiple times for one SQL execution.
--
-- This procedure writes to APEX collections, so it uses an autonomous
-- transaction to avoid ORA-14551 when invoked from SQL.
---------------------------------------------------------------------------
PROCEDURE FETCH_ROWS IS
PRAGMA AUTONOMOUS_TRANSACTION;
L_ROWSET DBMS_TF.ROW_SET_T;
L_ROW_COUNT PLS_INTEGER;
L_COL_COUNT PLS_INTEGER;
L_XID VARCHAR2(1024);
L_KEY_COLUMN_NAME VARCHAR2(128);
L_KEY VARCHAR2(128);
L_KEY_COL PLS_INTEGER := NULL;
TYPE T_VC_TAB IS TABLE OF APEX_APPLICATION_GLOBAL.VC_ARR2 INDEX BY PLS_INTEGER;
L_EMPTY_VC_ARR APEX_APPLICATION_GLOBAL.VC_ARR2;
L_KEY_ARR APEX_APPLICATION_GLOBAL.VC_ARR2;
L_META_COLL_ARR APEX_APPLICATION_GLOBAL.VC_ARR2;
L_META_NAME_ARR APEX_APPLICATION_GLOBAL.VC_ARR2;
L_META_ATTR_ARR APEX_APPLICATION_GLOBAL.N_ARR;
L_META_SRC_ARR APEX_APPLICATION_GLOBAL.N_ARR;
L_COL_ARR T_VC_TAB;
L_C_ARR T_VC_TAB;
L_GROUPS PLS_INTEGER;
L_IDX PLS_INTEGER;
L_SRC_COL_IDX PLS_INTEGER;
L_ATTR_NO PLS_INTEGER;
BEGIN
L_XID := DBMS_TF.GET_XID;
------------------------------------------------------------------------
-- Retrieve compile-time key column name from CSTORE.
------------------------------------------------------------------------
DBMS_TF.CSTORE_GET('KEY_COLUMN', L_KEY_COLUMN_NAME);
IF L_KEY_COLUMN_NAME IS NULL THEN
RAISE_APPLICATION_ERROR(-20002, 'KEY_COLUMN was not found in DBMS_TF CSTORE');
END IF;
L_KEY := NORMALIZE_STRING(L_KEY_COLUMN_NAME);
------------------------------------------------------------------------
-- Read current input rowset.
------------------------------------------------------------------------
DBMS_TF.GET_ROW_SET(ROWSET => L_ROWSET, ROW_COUNT => L_ROW_COUNT, COL_COUNT => L_COL_COUNT);
IF L_COL_COUNT IS NULL THEN
L_COL_COUNT := 0;
END IF;
IF L_ROW_COUNT IS NULL THEN
L_ROW_COUNT := 0;
END IF;
L_GROUPS := CEIL(L_COL_COUNT / C_GROUP_DATA_COLS);
------------------------------------------------------------------------
-- Locate key column in the rowset.
------------------------------------------------------------------------
FOR C IN 1 .. L_COL_COUNT LOOP
IF NORMALIZE_STRING(L_ROWSET(C).DESCRIPTION.NAME) = L_KEY THEN
L_KEY_COL := C;
EXIT;
END IF;
END LOOP;
IF L_KEY_COL IS NULL THEN
RAISE_APPLICATION_ERROR(-20003, 'Key column "' || L_KEY_COLUMN_NAME || '" not available in rowset');
END IF;
------------------------------------------------------------------------
-- Initialize nested associative-array entries before assigning elements.
------------------------------------------------------------------------
FOR C IN 1 .. L_COL_COUNT LOOP
L_COL_ARR(C) := L_EMPTY_VC_ARR;
END LOOP;
FOR P IN 1 .. C_GROUP_DATA_COLS LOOP
L_C_ARR(P) := L_EMPTY_VC_ARR;
END LOOP;
------------------------------------------------------------------------
-- Initialize APEX collections once per PTF execution.
------------------------------------------------------------------------
IF NOT G_INITIALIZED_BY_XID.EXISTS(L_XID) OR
G_INITIALIZED_BY_XID(L_XID) = FALSE THEN
APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION('COLL_META');
FOR G IN 1 .. L_GROUPS LOOP
APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION(COLL_NAME(G));
END LOOP;
---------------------------------------------------------------------
-- Metadata rows:
--
-- COLL_META.C001 = data collection name
-- COLL_META.C002 = original source column name
-- COLL_META.N001 = APEX character attribute number, 2..50
-- COLL_META.N002 = original source column ordinal
---------------------------------------------------------------------
L_IDX := 0;
FOR C IN 1 .. L_COL_COUNT LOOP
L_IDX := L_IDX + 1;
L_META_COLL_ARR(L_IDX) := COLL_NAME(CEIL(C / C_GROUP_DATA_COLS));
L_META_NAME_ARR(L_IDX) := L_ROWSET(C).DESCRIPTION.NAME;
------------------------------------------------------------------
-- C001 is key. Data columns start at C002.
------------------------------------------------------------------
L_ATTR_NO := MOD(C - 1, C_GROUP_DATA_COLS) + 2;
L_META_ATTR_ARR(L_IDX) := L_ATTR_NO;
L_META_SRC_ARR(L_IDX) := C;
END LOOP;
IF L_IDX > 0 THEN
APEX_COLLECTION.ADD_MEMBERS(P_COLLECTION_NAME => 'COLL_META',
P_C001 => L_META_COLL_ARR,
P_C002 => L_META_NAME_ARR,
P_N001 => L_META_ATTR_ARR,
P_N002 => L_META_SRC_ARR);
END IF;
G_INITIALIZED_BY_XID(L_XID) := TRUE;
END IF;
------------------------------------------------------------------------
-- Nothing to add for an empty rowset.
------------------------------------------------------------------------
IF L_ROW_COUNT = 0 THEN
COMMIT;
RETURN;
END IF;
------------------------------------------------------------------------
-- Build key array.
------------------------------------------------------------------------
FOR R IN 1 .. L_ROW_COUNT LOOP
L_KEY_ARR(R) := COL_TO_VARCHAR2(L_ROWSET(L_KEY_COL), R);
END LOOP;
------------------------------------------------------------------------
-- Build source column arrays.
------------------------------------------------------------------------
FOR C IN 1 .. L_COL_COUNT LOOP
FOR R IN 1 .. L_ROW_COUNT LOOP
L_COL_ARR(C)(R) := COL_TO_VARCHAR2(L_ROWSET(C), R);
END LOOP;
END LOOP;
------------------------------------------------------------------------
-- Add data to each collection.
--
-- Data collection layout:
-- C001 = key value
-- C002-C050 = up to 49 source columns for that collection group
------------------------------------------------------------------------
FOR G IN 1 .. L_GROUPS LOOP
---------------------------------------------------------------------
-- Reset group arrays before each ADD_MEMBERS call.
---------------------------------------------------------------------
FOR P IN 1 .. C_GROUP_DATA_COLS LOOP
L_C_ARR(P) := L_EMPTY_VC_ARR;
END LOOP;
FOR P IN 1 .. C_GROUP_DATA_COLS LOOP
L_SRC_COL_IDX := (G - 1) * C_GROUP_DATA_COLS + P;
FOR R IN 1 .. L_ROW_COUNT LOOP
IF L_SRC_COL_IDX <= L_COL_COUNT THEN
L_C_ARR(P)(R) := L_COL_ARR(L_SRC_COL_IDX) (R);
ELSE
L_C_ARR(P)(R) := NULL;
END IF;
END LOOP;
END LOOP;
APEX_COLLECTION.ADD_MEMBERS(P_COLLECTION_NAME => COLL_NAME(G),
P_C001 => L_KEY_ARR,
P_C002 => L_C_ARR(1),
P_C003 => L_C_ARR(2),
P_C004 => L_C_ARR(3),
P_C005 => L_C_ARR(4),
P_C006 => L_C_ARR(5),
P_C007 => L_C_ARR(6),
P_C008 => L_C_ARR(7),
P_C009 => L_C_ARR(8),
P_C010 => L_C_ARR(9),
P_C011 => L_C_ARR(10),
P_C012 => L_C_ARR(11),
P_C013 => L_C_ARR(12),
P_C014 => L_C_ARR(13),
P_C015 => L_C_ARR(14),
P_C016 => L_C_ARR(15),
P_C017 => L_C_ARR(16),
P_C018 => L_C_ARR(17),
P_C019 => L_C_ARR(18),
P_C020 => L_C_ARR(19),
P_C021 => L_C_ARR(20),
P_C022 => L_C_ARR(21),
P_C023 => L_C_ARR(22),
P_C024 => L_C_ARR(23),
P_C025 => L_C_ARR(24),
P_C026 => L_C_ARR(25),
P_C027 => L_C_ARR(26),
P_C028 => L_C_ARR(27),
P_C029 => L_C_ARR(28),
P_C030 => L_C_ARR(29),
P_C031 => L_C_ARR(30),
P_C032 => L_C_ARR(31),
P_C033 => L_C_ARR(32),
P_C034 => L_C_ARR(33),
P_C035 => L_C_ARR(34),
P_C036 => L_C_ARR(35),
P_C037 => L_C_ARR(36),
P_C038 => L_C_ARR(37),
P_C039 => L_C_ARR(38),
P_C040 => L_C_ARR(39),
P_C041 => L_C_ARR(40),
P_C042 => L_C_ARR(41),
P_C043 => L_C_ARR(42),
P_C044 => L_C_ARR(43),
P_C045 => L_C_ARR(44),
P_C046 => L_C_ARR(45),
P_C047 => L_C_ARR(46),
P_C048 => L_C_ARR(47),
P_C049 => L_C_ARR(48),
P_C050 => L_C_ARR(49));
END LOOP;
COMMIT;
END FETCH_ROWS;
---------------------------------------------------------------------------
-- CLOSE runs at end of PTF execution.
---------------------------------------------------------------------------
PROCEDURE CLOSE IS
L_XID VARCHAR2(1024);
BEGIN
L_XID := DBMS_TF.GET_XID;
IF G_INITIALIZED_BY_XID.EXISTS(L_XID) THEN
G_INITIALIZED_BY_XID.DELETE(L_XID);
END IF;
END CLOSE;
END APEX_COLL_PTF_PKG;
