Oracle DBMS_TF
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.2 to 12.2.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Do you remember when mainframes were replaced by client-server? When client-server was replaced by n-tier architecture? The "Cloud" is a distraction ... DevOps is not. Prepare for the future.
Purpose Support for polymorphic table functions to consume and produce data, and get information about the execution environment for Table Functions. It appears that these functions will only run if executed from within a polymorphic table function.
AUTHID CURRENT_USER
Constants
Name Data Type Value
TYPE_VARCHAR2 PLS_INTEGER 1
TYPE_NUMBER PLS_INTEGER 2
TYPE_DATE PLS_INTEGER 12
TYPE_BINARY_FLOAT PLS_INTEGER 100
TYPE_BINARY_DOUBLE PLS_INTEGER 101
TYPE_RAW PLS_INTEGER 23
TYPE_CHAR PLS_INTEGER 96
TYPE_CLOB PLS_INTEGER 112
TYPE_BLOB PLS_INTEGER 113
TYPE_TIMESTAMP PLS_INTEGER 180
TYPE_TIMESTAMP_TZ PLS_INTEGER 181
TYPE_INTERVAL_YM PLS_INTEGER 182
TYPE_INTERVAL_DS PLS_INTEGER 183
TYPE_TIMESTAMP_LTZ PLS_INTEGER 231
Data Types /* Column Metatdata */
TYPE COLUMN_METADATA_T IS RECORD(
col_type            PLS_INTEGER,
col_max_len         PLS_INTEGER,
col_name            VARCHAR2(32767),
col_name_len        PLS_INTEGER, /* following two attributes are used for numerical data */
col_precision       PLS_INTEGER,
col_scale           PLS_INTEGER, /* following three attributes are used for character data */
col_charsetid       PLS_INTEGER,
col_charsetform     PLS_INTEGER,
col_collation       PLS_INTEGER, /* following attributes may be used in future */
col_schema_name     DBMS_ID,
col_schema_name_len PLS_INTEGER,
col_type_name       DBMS_ID,
col_type_name_len   PLS_INTEGER);

/* Column Descriptor */
TYPE COLUMN_T IS RECORD(
description  COLUMN_METADATA_T, /* Column metadata */
pass_through BOOLEAN,           /* Pass-through column? */
for_read     BOOLEAN);          /* Column data will be read? */

/* Table Descriptor */
TYPE TABLE_T IS TABLE OF COLUMN_T;

/* Collection containing columns metadata */
TYPE COLUMNS_WITH_TYPE_T IS TABLE OF COLUMN_METADATA_T;

/* Collection containing column names */
TYPE COLUMNS_T IS TABLE OF DBMS_QUOTED_ID;

/* Collection for new columns */
TYPE COLUMNS_NEW_T IS TABLE OF COLUMN_METADATA_T;

 /* Collections for each supported types */
TYPE TAB_VARCHAR2_T IS TABLE OF VARCHAR2(32767) INDEX BY PLS_INTEGER;
TYPE TAB_NUMBER_T IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
TYPE TAB_DATE_T IS TABLE OF DATE INDEX BY PLS_INTEGER;
TYPE TAB_BINARY_FLOAT_T IS TABLE OF BINARY_FLOAT INDEX BY PLS_INTEGER;
TYPE TAB_BINARY_DOUBLE_T IS TABLE OF BINARY_DOUBLE INDEX BY PLS_INTEGER;
TYPE TAB_RAW_T IS TABLE OF RAW(32767) INDEX BY PLS_INTEGER;
TYPE TAB_CHAR_T IS TABLE OF CHAR(32767) INDEX BY PLS_INTEGER;
TYPE TAB_CLOB_T IS TABLE OF CLOB INDEX BY PLS_INTEGER;
TYPE TAB_BLOB_T IS TABLE OF BLOB INDEX BY PLS_INTEGER;
TYPE TAB_TIMESTAMP_T IS TABLE OF TIMESTAMP_UNCONSTRAINED INDEX BY PLS_INTEGER;
TYPE TAB_TIMESTAMP_TZ_T IS TABLE OF TIMESTAMP_TZ_UNCONSTRAINED INDEX BY PLS_INTEGER;
TYPE TAB_INTERVAL_YM_T IS TABLE OF YMINTERVAL_UNCONSTRAINED INDEX BY PLS_INTEGER;
TYPE TAB_INTERVAL_DS_T IS TABLE OF DSINTERVAL_UNCONSTRAINED INDEX BY PLS_INTEGER;
TYPE TAB_TIMESTAMP_LTZ_T IS TABLE OF TIMESTAMP_LTZ_UNCONSTRAINED INDEX BY PLS_INTEGER;

/* Data for a single column (tagged-union/variant-record) */
TYPE COLUMN_DATA_T IS RECORD(
/* ::TAG:: */
metadata          COLUMN_METADATA_T,
/* ::VARIANT FIELDS:: Exactly one is active */
tab_varchar2      TAB_VARCHAR2_T,
tab_number        TAB_NUMBER_T,
tab_date          TAB_DATE_T,
tab_binary_float  TAB_BINARY_FLOAT_T,
tab_binary_double TAB_BINARY_DOUBLE_T,
tab_raw           TAB_RAW_T,
tab_char          TAB_CHAR_T,
tab_clob          TAB_CLOB_T,
tab_blob          TAB_BLOB_T,
tab_timestamp     TAB_TIMESTAMP_T,
tab_timestamp_tz  TAB_TIMESTAMP_TZ_T,
tab_interval_ym   TAB_INTERVAL_YM_T,
tab_interval_ds   TAB_INTERVAL_DS_T,
tab_timestamp_ltz TAB_TIMESTAMP_LTZ_T);

/* Data for a rowset */
TYPE ROW_SET_T IS TABLE OF COLUMN_DATA_T INDEX BY PLS_INTEGER;

TYPE PARALLEL_ENV_T IS RECORD(
instance_id      PLS_INTEGER,  /* QC instance ID */
session_id       PLS_INTEGER,  /* QC session ID */
slave_svr_grp    PLS_INTEGER,  /* Slave server group */
slave_set_no     PLS_INTEGER,  /* Slave server set num */
no_slobal_slaves PLS_INTEGER,  /* Num of sibling slaves (including self) */
global_slave_no  PLS_INTEGER,  /* Global slave number (base 0) */
no_local_slaves  PLS_INTEGER,  /* Num of sibling slaves running on instance */
local_slave_no   PLS_INTEGER); /* Local slave number (base 0) */

TYPE TABLE_METADATA_T IS TABLE OF COLUMN_METADATA_T INDEX BY PLS_INTEGER;

TYPE PROJECTED_COLS_T IS TABLE OF BOOLEAN INDEX BY PLS_INTEGER;

TYPE ENV_T IS RECORD (
get_columns  TABLE_METADATA_T, /* Metadata for the Get_Col() */
put_columns  TABLE_METADATA_T, /* Metadata for the Put_Col() */
proj_put_col PROJECTED_COLS_T, /* TRUE => put-column is not needed */
parallel_env PARALLEL_ENV_T,   /* Parallel Execution information */
query_optim  BOOLEAN)'         /* Is this execution for query optimization? */
Dependencies
DBMS_OUTPUT DBMS_SYSTEM PLITBLM
DBMS_STANDARD    
Documented No
Exceptions
Error Code Reason
ORA-62562 The API Get_Env can only be called during execution of a polymorphic table function.
First Available 12.2.0.1
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/dbmstf.sql
Subprograms
 
GET_COL (new 12.2)
These procedure are used to "get" read column values
Overload 1
dbms_tf.Get_Col(
ColumnId   IN            NUMBER,
Collection IN OUT NOCOPY "<V2_TABLE_1>");
TBD
Overload 2 dbms_tf.Get_Col(
ColumnName IN            VARCHAR2,
Collection IN OUT NOCOPY "<V2_TABLE_1>");
TBD
 
GET_ENV (new 12.2)
Return information about the PTF's dbms_tf.get_env RETURN ENV_T;
set serveroutput on

DECLARE
 retVal dbms_tf.env_t;
BEGIN
  retVal := dbms_tf.get_env;
END;
/
*
ERROR at line 1:
ORA-62562: The API Get_Env can be called only during execution of a polymorphic table function
ORA-06512: at "SYS.DBMS_TF",, line 49
ORA-06512: at line 4
 
GET_EXECUTION_ID (new 12.2)
  dbms_tf.get_execution_Id RETURN VARCHAR2;
SELECT tf.get_execution_id
FROM dual;
 
GET_ROW_SET (new 12.2)
Return miscellaneous information about the runtime environment of the current PTF
Overload 1
dbms_tf.Get_Row_Set(
rowset    OUT NOCOPY ROW_SET_T,
row_count OUT        PLS_INTEGER);
DECLARE
 rsetO dbms_tf.row_set_t;
 rowCO PLS_INTEGER;
BEGIN
  dbms_tf.get_row_set(rsetO, rowCO);
END;
/
*
ERROR at line 1:
ORA-62562: The API Get_Env can be called only during execution of a polymorphic table function
ORA-06512: at "SYS.DBMS_TF", line 49
ORA-06512: at "SYS.DBMS_TF", line 62
ORA-06512: at "SYS.DBMS_TF", line 111
ORA-06512: at line 4
Overload 2 dbms_tf.get_row_set(rowset OUT NOCOPY ROW_SET_T);
DECLARE
 retVal dbms_tf.row_set_t;
BEGIN
  dbms_tf.get_row_set(retVal);
END;
/
*
ERROR at line 1:
ORA-62562: The API Get_Env can be called only during execution of a polymorphic table function
ORA-06512: at "SYS.DBMS_TF", line 49
ORA-06512: at line 4
 
PUT_COL (new 12.2)
These procedure are used to "put" new column values
Overload 1
dbms_tf.Put_Col(ColumnId NUMBER, Collection IN "<V2_TABLE_1>");
 
Overload 2 dbms_tf.Put_Col(
ColumnName IN VARCHAR2,
Collection IN "<V2_TABLE_1>");
TBD
 
PUT_ROW_SET (new 12.2)
Sets miscellaneous information about the runtime environment of the current PTF dbms_tf.put_row_set(rowset IN ROW_SET_T);
TBD
 
SUPPORTED_TYPE (new 12.2)
Returns TRUE if the type_id is supported for obtaining or producing columns inside a PTF dbms_tf.supported_type(type_id PLS_INTEGER) RETURN BOOLEAN;
BEGIN
  IF dbms_tf.supported_type(dbms_tf.type_varchar2) THEN
    dbms_output.put_line('Supported');
  ELSE
    dbms_output.put_line('Not Supported');
  END IF;
END;
/
 
TRACE (new 12.2)
Overload 1 dbms_tf.trace(
msg       IN VARCHAR2,
with_id   IN BOOLEAN  DEFAULT FALSE,
separator IN VARCHAR2 DEFAULT NULL,
prefix    IN VARCHAR2 DEFAULT NULL);
TBD
Overload 2 dbms_tf.trace(rowset IN row_set_t);
TBD
Overload 3 dbms_tf.trace(env IN env_t);
TBD
Overload 4 dbms_tf.trace(columns_new IN columns_new_t);
TBD
Overload 5 dbms_tf.trace(cols IN columns_t);
TBD
Overload 6 dbms_tf.trace(columns_with_type IN columns_with_type_t);
TBD
Overload 7 dbms_tf.trace(tab IN table_t);
TBD

Related Topics
Built-in Functions
Built-in Packages
What's New In 12cR1
What's New In 12cR2

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved