The DBMS_TF package contains utilities for Polymorphic Table Functions (PTF) implementation. You can use DBMS_TF subprograms to consume and produce data, and get information about its execution environment.

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.

2021年9月30日 · 从ORACLE 18C开始，ORACLE添加了PTF功能，目前在关系型数据库中，只有ORACLE支持此功能，简单来说，这个功能就是传入一个表作为参数，返回一个查询结果，而返回的这个表可以进行各种各样的定义或者修改，比如让返回的结果中自动屏蔽某些字段、自动对所有字段进行TRIM、自动对所有字段进行NVL、自动对所有number字段保留4位小数、自动添加一些字段、自动将所有date类型的字段修改为VARCHAR2类型，而且值得一提的是，一旦创建一个 …

2019年8月23日 · You must replace the TABLE argument with the DBMS_TF.table_t type and any COLUMNS arguments with DBMS_TF.column_t. The describe function must also have a return type of DBMS_TF.describe_t. The fetch_rows procedure must also have the same parameters as the PTF, minus any TABLE or COLUMNS arguments.

2021年10月3日 · 本文介绍了Oracle数据库中几种罕见但实用的SQL功能，如DBMS_TF（多态表函数）用于自定义查询结果，approx_count_distinct提供近似去重统计，以及dbms_crypto包的加密和解密功能。

Description An example of how to use polymorphic table functions in 18c to dynamically convert CSV data to columns. To create a polymorphic table function (PTF), you need to create a package a describe function. This defines new columns in the output. Fetch_rows is optional. You use this to set values for new columns.

CREATE OR REPLACE FUNCTION poly_func(p_tbl IN TABLE, p_cols DBMS_TF.COLUMNS_T ) RETURN TABLE PIPELINED ROW POLYMORPHIC USING poly_pkg;

DBMS_OUTPUT.PUT_LINE('Table PTF ' || p_tbl.ptf_name); -- the column array in the DBMS_TF_TABLE_T -- structure contains details of each column FOR counter IN 1..p_tbl.column.count LOOP DBMS_OUTPUT.PUT_LINE('Column ' || counter); -- output the column name, the length of the -- column name, the maximum value length allowed -- …

The following example is quite possibly the most simple example for a polymorphic table function using dbms_tf. Because of its simplicity, it also does not do anything useful. This example also demonstrates that the package's describe function is called once per passed table.

Polymorphic table functions (PTF) are a subset of table functions where the schema of the returned table is determined dynamically. The returned table schema can depend on the arguments you pass to the function. Code : CREATE TABLE DEPT ( DEPTNO NUMBER (2) CONSTRAINT PK_DEPT PRIMARY KEY, DNAME VARCHAR2 (14), LOC VARCHAR2 (13) ) ; CREATE TABLE EMP (