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...