8i | 9i | 10g | 11g | 12c | 13c | 18c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 18c » Here

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

Back to the Top.