8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Polymorphic Table Functions in Oracle Database 18c
The return type of a Polymorphic Table Function (PTF) can be determined by input parameters. This differs from conventional table functions, where the output table type is fixed at compile time.
Setup
The examples in this article require on the following tables.
--DROP TABLE emp PURGE;
--DROP TABLE dept PURGE;
CREATE TABLE DEPT (
DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
DNAME VARCHAR2(14),
LOC VARCHAR2(13)
) ;
CREATE TABLE EMP (
EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT
);
INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');
INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
COMMIT;
Basic Example
We need to define a package that will be used to describe the table input, so the polymorphic table function will understand the table structure. This is really simple if we don't want to modify the structure.
CREATE OR REPLACE PACKAGE poly_pkg AS
FUNCTION describe (tab IN OUT DBMS_TF.table_t)
RETURN DBMS_TF.describe_t;
END poly_pkg;
/
CREATE OR REPLACE PACKAGE BODY poly_pkg AS
FUNCTION describe (tab IN OUT DBMS_TF.table_t)
RETURN DBMS_TF.describe_t
AS
BEGIN
RETURN NULL;
END;
END poly_pkg;
/
With the package in place, we can define a polymorphic table function which references the package. At minimum the function must accept a parameter of type TABLE and have a return type of TABLE. Notice the ROW POLYMORPHIC USING clause referencing the package. There must be a DESCRIBE function in the associated package with a matching parameter list. In this case we've defined the polymorphic table function as a standalone function, but it could have been included in a package above.
CREATE OR REPLACE FUNCTION my_ptf(tab IN TABLE) RETURN TABLE PIPELINED ROW POLYMORPHIC USING poly_pkg; /
With the package and function in place we can use the function to query different tables.
SET LINESIZE 140
SELECT * FROM my_ptf(dept);
DEPTNO DNAME LOC
---------- ------------------------------------------ ---------------------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL>
SELECT * FROM my_ptf(emp);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------------------------------ --------------------------- ---------- --------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
SQL>
Remove Columns
In the following example we have included the polymorphic table function in the package definition to make things cleaner. We've also added an extra parameter to supply a list of columns which will be excluded from the result set. Notice the DESCRIBE function has changed to match the parameter list.
CREATE OR REPLACE PACKAGE poly_pkg AS
FUNCTION my_ptf(tab IN TABLE,
col IN COLUMNS)
RETURN TABLE PIPELINED
ROW POLYMORPHIC USING poly_pkg;
FUNCTION describe (tab IN OUT DBMS_TF.table_t,
col IN dbms_tf.columns_t)
RETURN DBMS_TF.describe_t;
END poly_pkg;
/
CREATE OR REPLACE PACKAGE BODY poly_pkg AS
FUNCTION describe (tab IN OUT DBMS_TF.table_t,
col IN dbms_tf.columns_t)
RETURN DBMS_TF.describe_t
AS
BEGIN
-- Loop through all the table columns.
FOR i IN 1 .. tab.column.count() LOOP
-- Loop through all the columns listed in the second parameter.
FOR j IN 1 .. col.count() LOOP
-- Set pass_through to true for any columns not in the exclude list.
tab.column(i).pass_through := (tab.column(i).description.name != col(j));
-- Exit inner loop if you find a column that shouldn't be included.
EXIT WHEN NOT tab.column(i).pass_through;
END LOOP;
END LOOP;
RETURN NULL;
END;
END poly_pkg;
/
The implementation of the DESCRIBE function tests to see if the table column is in the exclude list. If it should be excluded the columns PASS_THROUGH element is set to false, otherwise it is set to true.
We can now query the packaged polymorphic table function, passing a table and a column exclude list. Notice how the output has changed now.
SELECT * FROM poly_pkg.my_ptf(dept, COLUMNS(loc));
DEPTNO DNAME
---------- ------------------------------------------
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS
SQL>
SELECT * FROM poly_pkg.my_ptf(emp, COLUMNS(hiredate, sal, mgr, comm));
EMPNO ENAME JOB DEPTNO
---------- ------------------------------ --------------------------- ----------
7369 SMITH CLERK 20
7499 ALLEN SALESMAN 30
7521 WARD SALESMAN 30
7566 JONES MANAGER 20
7654 MARTIN SALESMAN 30
7698 BLAKE MANAGER 30
7782 CLARK MANAGER 10
7788 SCOTT ANALYST 20
7839 KING PRESIDENT 10
7844 TURNER SALESMAN 30
7876 ADAMS CLERK 20
7900 JAMES CLERK 30
7902 FORD ANALYST 20
7934 MILLER CLERK 10
SQL>
Add Columns
The following code creates a package for a polymorphic table function that adds a JSON_DOC column on to any table passed in. The DESCRIBE function turns on the FOR_READ flag for any supported column, so it will be included in the document. The RETURN clause returns the new column metadata. The FETCH_ROWS procedure builds a collection containing a JSON doc returned by the ROW_TO_CHAR function for each row and associates the resulting collection with the new column.
CREATE OR REPLACE PACKAGE poly_pkg AS
FUNCTION my_ptf(tab IN TABLE)
RETURN TABLE PIPELINED
ROW POLYMORPHIC USING poly_pkg;
FUNCTION describe (tab IN OUT DBMS_TF.table_t)
RETURN DBMS_TF.describe_t;
PROCEDURE fetch_rows;
END poly_pkg;
/
CREATE OR REPLACE PACKAGE BODY poly_pkg AS
FUNCTION describe (tab IN OUT DBMS_TF.table_t)
RETURN DBMS_TF.describe_t
AS
BEGIN
-- Make sure the for_read flag for each real column
-- is set, or get_row_set has no values.
FOR i IN 1 .. tab.column.count LOOP
CONTINUE WHEN NOT DBMS_TF.supported_type(tab.column(i).description.TYPE);
tab.column(i).for_read := TRUE;
END LOOP;
-- Add the new JSON_DOC column.
RETURN DBMS_TF.describe_t(
new_columns => DBMS_TF.columns_new_t(1 => DBMS_TF.column_metadata_t(name =>'JSON_DOC'))
);
END;
PROCEDURE fetch_rows AS
l_row_set DBMS_TF.row_set_t;
l_new_col DBMS_TF.tab_varchar2_t;
l_row_count PLS_INTEGER;
BEGIN
DBMS_TF.get_row_set(l_row_set, row_count => l_row_count);
--DBMS_TF.trace(l_row_set);
-- Populate the new column with a JSON doc of the associated row.
FOR row_num IN 1 .. l_row_count LOOP
l_new_col(row_num) := DBMS_TF.row_to_char(l_row_set, row_num);
END LOOP;
-- Associate the new values with the new column.
DBMS_TF.put_col(1, l_new_col);
END;
END poly_pkg;
/
We can now query the JSON_DOC column produced by the packaged polymorphic table function, passing two different tables.
SET LINESIZE 100
COLUMN json_doc FORMAT A50
SELECT deptno, json_doc FROM poly_pkg.my_ptf(dept);
DEPTNO JSON_DOC
---------- --------------------------------------------------
10 {"DEPTNO":10, "DNAME":"ACCOUNTING", "LOC":"NEW YOR
K"}
20 {"DEPTNO":20, "DNAME":"RESEARCH", "LOC":"DALLAS"}
30 {"DEPTNO":30, "DNAME":"SALES", "LOC":"CHICAGO"}
40 {"DEPTNO":40, "DNAME":"OPERATIONS", "LOC":"BOSTON"
}
SQL>
SELECT empno, json_doc FROM poly_pkg.my_ptf(emp);
EMPNO JSON_DOC
---------- --------------------------------------------------
7369 {"EMPNO":7369, "ENAME":"SMITH", "JOB":"CLERK", "MG
R":7902, "HIREDATE":"17-DEC-80", "SAL":800, "DEPTN
O":20}
7499 {"EMPNO":7499, "ENAME":"ALLEN", "JOB":"SALESMAN",
"MGR":7698, "HIREDATE":"20-FEB-81", "SAL":1600, "C
OMM":300, "DEPTNO":30}
7521 {"EMPNO":7521, "ENAME":"WARD", "JOB":"SALESMAN", "
MGR":7698, "HIREDATE":"22-FEB-81", "SAL":1250, "CO
MM":500, "DEPTNO":30}
7566 {"EMPNO":7566, "ENAME":"JONES", "JOB":"MANAGER", "
MGR":7839, "HIREDATE":"02-APR-81", "SAL":2975, "DE
PTNO":20}
7654 {"EMPNO":7654, "ENAME":"MARTIN", "JOB":"SALESMAN",
"MGR":7698, "HIREDATE":"28-SEP-81", "SAL":1250, "
COMM":1400, "DEPTNO":30}
7698 {"EMPNO":7698, "ENAME":"BLAKE", "JOB":"MANAGER", "
MGR":7839, "HIREDATE":"01-MAY-81", "SAL":2850, "DE
PTNO":30}
7782 {"EMPNO":7782, "ENAME":"CLARK", "JOB":"MANAGER", "
MGR":7839, "HIREDATE":"09-JUN-81", "SAL":2450, "DE
PTNO":10}
7788 {"EMPNO":7788, "ENAME":"SCOTT", "JOB":"ANALYST", "
MGR":7566, "HIREDATE":"19-APR-87", "SAL":3000, "DE
PTNO":20}
7839 {"EMPNO":7839, "ENAME":"KING", "JOB":"PRESIDENT",
"HIREDATE":"17-NOV-81", "SAL":5000, "DEPTNO":10}
7844 {"EMPNO":7844, "ENAME":"TURNER", "JOB":"SALESMAN",
"MGR":7698, "HIREDATE":"08-SEP-81", "SAL":1500, "
COMM":0, "DEPTNO":30}
7876 {"EMPNO":7876, "ENAME":"ADAMS", "JOB":"CLERK", "MG
R":7788, "HIREDATE":"23-MAY-87", "SAL":1100, "DEPT
NO":20}
7900 {"EMPNO":7900, "ENAME":"JAMES", "JOB":"CLERK", "MG
R":7698, "HIREDATE":"03-DEC-81", "SAL":950, "DEPTN
O":30}
7902 {"EMPNO":7902, "ENAME":"FORD", "JOB":"ANALYST", "M
GR":7566, "HIREDATE":"03-DEC-81", "SAL":3000, "DEP
TNO":20}
7934 {"EMPNO":7934, "ENAME":"MILLER", "JOB":"CLERK", "M
GR":7782, "HIREDATE":"23-JAN-82", "SAL":1300, "DEP
TNO":10}
SQL>
What Next?
The examples in this article are about as simple as you get where polymorphic table functions are concerned. They can be made to do literally anything you can program in PL/SQL and present in a table structure. If you want to take this further, check out the documentation links below.
For more information see:
Hope this helps. Regards Tim...