Oracle DBMS_TF
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
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.

The code samples on this page were created by Oracle and have been properly formatted, modified slightly to comply with standard PL/SQL coding practices, but otherwise not modified.
AUTHID DEFINER
Constants
Name Data Type Value
Data Types
TYPE_VARCHAR2 PLS_INTEGER 1
TYPE_NUMBER PLS_INTEGER 2
TYPE_DATE PLS_INTEGER 12
TYPE_RAW PLS_INTEGER 23
TYPE_ROWID PLS_INTEGER 69
TYPE_CHAR PLS_INTEGER 96
TYPE_BINARY_FLOAT PLS_INTEGER 100
TYPE_BINARY_DOUBLE PLS_INTEGER 101
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_EDATE PLS_INTEGER 184
TYPE_ETIMESTAMP_TZ PLS_INTEGER 186
TYPE_ETIMESTAMP PLS_INTEGER 187
TYPE_EINTERVAL_YM PLS_INTEGER 189
TYPE_INTERVAL_DS PLS_INTEGER 190
TYPE_TIMESTAMP_LTZ PLS_INTEGER 231
TYPE_ETIMESTAMP_LTZ PLS_INTEGER 232
Pre-Defined INDEX VALUES FOR Method Names
OPEN dbms_quoted_id 'OPEN'
FETCH_ROWS dbms_quoted_id 'FETCH_ROWS'
CLOSE dbms_quoted_id 'CLOSE'
Data Format Constants
CSTORE_TYPE_VARCHAR2 PLS_INTEGER TYPE_VARCHAR2
CSTORE_TYPE_NUMBER PLS_INTEGER TYPE_NUMBER
CSTORE_TYPE_DATE PLS_INTEGER TYPE_DATE
CSTORE_TYPE_BOOLEAN PLS_INTEGER 252
FORMAT_JSON PLS_INTEGER 1
FORMAT_XML PLS_INTEGER 2
Data Types /* Column Metatdata */
TYPE column_metadata_t IS RECORD(
type            PLS_INTEGER,
max_len         PLS_INTEGER,
name            VARCHAR2(32767),
name_len        PLS_INTEGER, /* following two attributes are used for numerical data */
precision       PLS_INTEGER,
scale           PLS_INTEGER, /* following three attributes are used for character data */
charsetid       PLS_INTEGER,
charsetform     PLS_INTEGER,
collation       PLS_INTEGER, /* following attributes may be used in future */
schema_name     DBMS_ID,
schema_name_len PLS_INTEGER,
type_name       DBMS_ID,
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? */


/* A collection of columns(column_t) */
TYPE table_columns_t IS TABLE OF column_t;

/* Table Descriptor */
 TYPE table_t IS RECORD(
 column       table_columns_t,  /* Column information */
 schema_name  dbms_quoted_id,   /* the schema name OF ptf */
 package_name dbms_quoted_id,   /* the package name OF ptf */
 ptf_name     dbms_quoted_id);  /* the ptf name invoked */

/* 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;

/* CStore: Collections TYPE FOR compilation storage */
TYPE CSTORE_CHR_T IS TABLE OF VARCHAR2(32767) INDEX BY VARCHAR2(32767);
TYPE CSTORE_NUM_T IS TABLE OF NUMBER INDEX BY VARCHAR2(32767);
TYPE CSTORE_BOL_T IS TABLE OF BOOLEAN INDEX BY VARCHAR2(32767);
TYPE CSTORE_DAT_T IS TABLE OF DATE INDEX BY VARCHAR2(32767);

/* Collection OF user defined method names */
TYPE METHODS_T IS TABLE OF DBMS_QUOTED_ID INDEX BY DBMS_ID;

/* Return type from the Describe method of PTF */
TYPE describe_t IS RECORD(
NEW_COLUMNS COLUMNS_NEW_T default COLUMNS_NEW_T(), /* new columns */
/* CStore */
CSTORE_CHR CSTORE_CHR_T default CSTORE_CHR_T(), /* CStore: key/char */
CSTORE_NUM CSTORE_NUM_T default CSTORE_NUM_T(), /* CStore: key/numb */
CSTORE_BOL CSTORE_BOL_T default CSTORE_BOL_T(), /* CStore: key/bool */
CSTORE_DAT CSTORE_DAT_T default CSTORE_DAT_T(), /* CStore: key/date */
-- Open/Fetch/Close method overrides
METHOD_NAMES METHODS_T default METHODS_T(),
/* following attributes may be used in future */
-- Row Replication Enabled?
ROW_REPLICATION BOOLEAN default FALSE,
-- Row Insertion Enabled?
ROW_INSERTION BOOLEAN default FALSE);

 /* 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;
TYPE TAB_BOOLEAN_T IS TABLE OF BOOLEAN INDEX BY PLS_INTEGER;
TYPE TAB_ROWID_T IS TABLE OF ROWID INDEX BY PLS_INTEGER;
TYPE TAB_NATURALN_T IS TABLE OF NATURALN 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,
tab_rowid         TAB_ROWID_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? */

SUBTYPE XID_T IS VARCHAR2(1024);
Dependencies
DBMS_OUTPUT DBMS_SYSTEM PLITBLM
DBMS_STANDARD DUAL  
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
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/dbmstf.sql
Subprograms
COLUMN_TYPE_NAME GET_XID TRACE
COL_TO_CHAR PUT_COL XSTORE_CLEAR
CSTORE_EXISTS PUT_ROW_SET XSTORE_EXISTS
CSTORE_GET ROW_REPLICATION XSTORE_GET
GET_COL ROW_TO_CHAR XSTORE_REMOVE
GET_ENV SUPPORTED_TYPE XSTORE_SET
GET_ROW_SET    
 
COLUMN_TYPE_NAME
Coverts the column type information into a text string dbms_tf.column_type_name(col IN COLUMN_METADATA_T)
RETURN VARCHAR2;
CREATE OR REPLACE FUNCTION describe(tab IN OUT dbms_tf.table_t, cols IN dbms_tf.columns_t)
AUTHID CURRENT_USER
RETURN dbms_tf.describe_t IS
 new_cols dbms_tf.columns_new_t;
 col_id   PLS_INTEGER := 1;
BEGIN
  FOR i IN 1 .. tab.COUNT LOOP
    FOR j IN 1 .. cols.COUNT LOOP
      IF (tab(i).description.name = cols(j)) THEN
        IF (dbms_tf.column_type_name(tab(i).description.type) != 'VARCHAR2') then
          raise_application_error(-20102, 'Unspported column type ['||tab(i).description.type||']');
        END IF;
        tab(i).for_read := TRUE;
        new_cols(col_id) := tab(i).description;
        new_cols(col_id).name := 'ECHO_'|| tab(i).description.name;
        col_id := col_id + 1;
        EXIT;
      END IF;
    END LOOP;
  END LOOP;

  -- verify all columns were found
  IF (col_id - 1 != cols.Count) THEN
    raise_application_error(-20101, 'Column mismatch ['||col_id-1||'], ['||cols.Count||']');
  END IF;

  RETURN dbms_tf.describe_t(new_columns => new_cols);
END describe;
/
 
COL_TO_CHAR
converts a column data value to a string representation dbms_tf.col_to_char(
col    IN column_data_t,
rid    IN PLS_INTEGER,
quote  IN VARCHAR2 DEFAULT '"'),
RETURN IN VARCHAR2;
CREATE OR REPLACE PROCEDURE fetch_rows AUTHID CURRENT_USER IS
 rowset dbms_tf.row_set_t;
 str varchar2(32767);
BEGIN
  dbms_tf.get_row_set(rowset);
  str := dbms_tf.col_to_char(rowset(1), 1);
  dbms_output.put_line(str);
END fetch_rows;
/
*
ERROR at line 1:
ORA-62562: The API Get_Env can be called only during execution time of a polymorphic table function.
ORA-06512: at "sys.dbms_tf", line 131
ORA-06512: at "sys.dbms_tf", line 156
ORA-06512: at "sys.dbms_tf", line 198
ORA-06512: at line 5
 
CSTORE_EXISTS
Returns TRUE if the key has an associated value. If key_type is null (or unknown) then check for any value, otherwise it only looks for the specified type of key dbms_tf.cstore_exits(
key      IN VARCHAR2,
key_type IN PLS_INTEGER DEFAULT NULL)
RETURN BOOLEAN;
IF (dbms_tf.cstore_exists('min'||j)) THEN
  dbms_tf.cstore_get('min'||j, min_col);
END IF;
 
CSTORE_GET
A Key/Value Store for PTF Compilation State

Overload 1		 dbms_tf.cstore_get(
key   IN     VARCHAR2,
value IN OUT VARCHAR2);
CREATE OR REPLACE PACKAGE ptf_min_max_p AUTHID CURRENT_USER IS
 FUNCTION describe(tab IN OUT dbms_tf.table_t,
   -- list of columns to compute maximum values
   colmax IN dbms_tf.columns_t,
   -- list od columns to compute minimum values
   colmin IN dbms_tf.columns_t)
   RETURN dbms_tf.describe_t;
 PROCEDURE fetch_rows;
END ptf_min_max_p;
/

CREATE OR REPLACE PACKAGE BODY ptf_min_max_p IS
  FUNCTION describe(tab    IN OUT dbms_tf.table_t,
                    colmax IN     dbms_tf.columns_t,
                    colmin IN     dbms_tf.columns_t)
  RETURN dbms_tf.describe_t IS
   descr dbms_tf.describe_t;
   new   dbms_tf.columns_new_t;
   cid   PLS_INTEGER := 1;
  BEGIN
    FOR i IN 1 .. tab.column.count LOOP
      FOR j IN 1 .. colmax.count LOOP
        IF (tab.column(i).description.name = colmax(j)) THEN
          -- mark this column to be read
          tab.column(i).for_read := TRUE;
          descr.cstore_num('getc'||i) := i;
          -- Create a corresponding new column
          new(cid).name := 'MAX_' || tab.column(i).description.name;
          new(cid).type := dbms_tf.type_number;
          descr.cstore_num('max'||cid) := cid;

          cid := cid + 1;
        END IF;
      END LOOP;

      FOR j IN 1 .. colmin.count LOOP
        IF (tab.column(i).description.name = colmin(j)) THEN
          -- mark this column to be read
          tab.column(i).for_read := TRUE;
          descr.cstore_num('getc'||i) := i;
          -- create a corresponding new column
          new(cid).name := 'MIN_' || tab.column(i).description.name;
          new(cid).type := dbms_tf.type_number;
          descr.cstore_num('min'||cid) := cid;
          cid := cid + 1;
        END IF;
      END LOOP;
    END LOOP;
    -- dbms_tf.trace(NEW);
    descr.new_columns := new;
    RETURN descr;
  END describe;

  PROCEDURE fetch_rows IS
   row_cnt CONSTANT PLS_INTEGER     := dbms_tf.get_env().row_count;
   xid     CONSTANT dbms_tf.xid_t   := dbms_tf.get_xid();

   putcols dbms_tf.table_metadata_t := dbms_tf.get_env().put_columns;
   c_in    dbms_tf.tab_number_t;
   c_ou    dbms_tf.tab_number_t;
   env     dbms_tf.env_t := dbms_tf.get_env();
   fetched BOOLEAN;
   getcols dbms_tf.table_metadata_t := dbms_tf.get_env().get_columns;
   max_col NUMBER := -1;
   min_col NUMBER := -1;
   miv     PLS_INTEGER;
   mxv     PLS_INTEGER;
  BEGIN
    FOR j IN 1..putcols.count LOOP
      fetched := FALSE;
      IF (dbms_tf.cstore_exists('max'||j)) THEN
        dbms_tf.cstore_get('max'||j, max_col);
      END IF;

      IF (j = max_col) AND (env.ref_put_col.exists(j) AND env.ref_put_col(j)) THEN
        dbms_tf.xstore_get('mxv', mxv);
        IF (NOT fetched AND j <= getcols.count) then
          dbms_tf.get_col(j, c_in);
          fetched := TRUE;
        END IF;

        FOR i IN 1 .. row_cnt LOOP
          IF (c_in(i) > NVL(mxv, c_in(i)-1)) THEN
            mxv := c_in(i);
          END IF;
          c_ou(i) := mxv;
        END LOOP;
        dbms_tf.put_col(j, c_ou);
        dbms_tf.xstore_xet('mxv', mxv);
      END IF;

      IF (dbms_tf.cstore_exists('min'||j)) THEN
        dbms_tf.cstore_get('min'||j, min_col);
      END IF;

      IF (j = min_col) AND
        (env.ref_put_col.exists(j)AND
        env.ref_put_col(j)) THEN
        dbms_tf.xstore_get('miv', miv);

        IF (NOT fetched AND j <= getcols.count) THEN
          dbms_tf.get_col(j, c_in);
        END IF;

        FOR i IN 1 .. ROW_CNT LOOP
         - IF (c_in(i) < NVL(miv, c_in(i)+1)) THEN
            miv := c_in(i);
          END IF;
          c_ou(i) := miv;
        END LOOP;
        dbms_tf.put_col(j, c_ou);
      END IF;
    END LOOP;
  END;
END ptf_min_max_p;
/

CREATE OR REPLACE FUNCTION ptf_min_max(t IN table, maxcols IN columns, mincols IN columns)
RETURN TABLE PIPELINED  TABLE POLYMORPHIC USING ptf_min_max_p;

SELECT min_salary, max_salary, max_commision
FROM ptf_min_max(employees PARTITION BY (salary), columns(salary, commission_pct), columns(salary));
Overload 2 dbms_tf.cstore_get(
key   IN     VARCHAR2,
value IN OUT NUMBER);
TBD
Overload 3 dbms_tf.cstore_get(
key   IN     VARCHAR2,
value IN OUT DATE);
TBD
Overload 4 dbms_tf.cstore_get(
key   IN     VARCHAR2,
value IN OUT BOOLEAN);
TBD
Overload 5 dbms_tf.cstore_get(key_value OUT CSTORE_CHR_T);
TBD
Overload 6 dbms_tf.cstore_get(key_value OUT CSTORE_NUM_T);
TBD
Overload 7 dbms_tf.cstore_get(key_value OUT CSTORE_BOL_T);
TBD
Overload 8 dbms_tf.cstore_get(key_value OUT CSTORE_DAT_T);
TBD
 
GET_COL
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>");
pragma interface(c, Get_Col);
CREATE OR REPLACE PROCEDURE fetch_rows AUTHID CURRENT_USER IS
 col1 dbms_tf.tab_clob_t;
 col2 dbms_tf.tab_colb_t;
 out1 dbms_tf.tab_clob_t;
 out2 dbms_tf.tab_clob_t;
BEGIN
  dbms_tf.get_col(1, col1);
  dbms_tf.get_col(2, col2);

  FOR i IN 1 .. col1.COUNT LOOP
    out1(i) := 'ECHO-' || col1(i);
  END LOOP;

  FOR i IN 1 .. col2.COUNT LOOP
    out2(i) := 'ECHO-' || col2(i);
  END LOOP;

  dbms_tf.put_col(1, out1);
  dbms_tf.put_col(2, out2);
END fetch_rows;
/
Overload 2 dbms_tf.get_col(
ColumnName IN            VARCHAR2,
Collection IN OUT NOCOPY "<V2_TABLE_1>");
PRAGMA interface(c, Get_Col);
TBD
 
GET_ENV
Returns a record containing information that may be needed during
execution of a Polymorphic Table Function		 dbms_tf.get_env RETURN dbms_tf.env_t;
DECLARE
 row_cnt CONSTANT PLS_INTEGER := dbms_tf.get_env().Row_Count;
 xid     CONSTANT dbms_id     := dbms_tf.get_xid();
 rid              PLS_INTEGER := xst(XID);
 col              dbms_tf.tab_number_t;
BEGIN
  FOR i IN 1 .. row_cnt LOOP
    col(i) := rid + i;
  END LOOP;
  dbms_tf.put_col(1, col);
  xst(XID) := rid + row_cnt;
END;
/
 
GET_XID
Returns a unique execution id that = can be used by the PTF to index any cursor-execution specific runtime state dbms_tf.get_xid RETURN VARCHAR2;
See GET_ENV demo above
 
GET_ROW_SET
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);
CREATE OR REPLACE PROCEDURE fetch_rows(new_name IN VARCHAR2 DEFAULT 'PTF_CONCATENATE')
AUTHID CURRENT_USER IS
 rowset      dbms_tf.row_set_t;
 accumulator dbms_tf.tab_varchar2_t;
 row_count   PLS_INTEGER;

 FUNCTION get_value(col PLS_INTEGER, row PLS_INTEGER) RETURN VARCHAR2 IS
  col_type PLS_INTEGER := rowset(col).description.type;
 BEGIN
   CASE col_type
   WHEN dbms_tf.type_varchar2 THEN
     RETURN NVL(rowset(col).tab_varchar2 (row), 'empty');
   ELSE
    raise_application_error(-20201,'Non-Varchar Type='||col_type);
   END case;
 END get_value;

 BEGIN
   dbms_tf.get_row_set(rowset, row_count);
   IF (rowset.count = 0) THEN
     RETURN;
   END IF;

   FOR row_num IN 1 .. row_count LOOP
     accumulator(row_num) := 'empty';
   END LOOP;

   FOR col_num IN 1 .. rowset.count LOOP
     FOR row_num IN 1 .. row_count LOOP
       accumulator(row_num) := accumulator(row_num) || get_value(col_num, row_num);
     END LOOP;
   END LOOP;

   -- pushout the accumulator
  dbms_tf.put_col(1, accumulator);
END;
/
Overload 2 dbms_tf.get_row_set(
rowset    OUT NOCOPY row_set_t,
row_count OUT        PLS_INTEGER,
col_count OUT        PLS_INTEGER);
TBD
Overload 3 dbms_tf.get_row_set(rowset OUT NOCOPY ROW_SET_T);
DECLARE
 rowset dbms_tf.row_set_t;
BEGIN
  dbms_tf.get_row_set(rowset);
  dbms_tf.put_row_set(rowset);
END;
/
 
PUT_COL
These procedure are used to "put" new column values
Overload 1		 dbms_tf.put_col(
ColumnId   IN NUMBER,
Collection IN "<V2_TABLE_1>");
PRAGMA interface(c, Put_Col);
See GET_COL Demo Above
Overload 2 dbms_tf.put_col(
ColumnName IN VARCHAR2,
Collection IN "<V2_TABLE_1>");
TBD
 
PUT_ROW_SET
Sets runtime environment information for the current PTF
Overload 1		 dbms_tf.put_row_set(rowset IN dbms_tf.row_set_t);
TBD
Overload 2 dbms_tf.put_row_set(
rowset             IN dbms_tf.row_set_t,
replication_factor IN NATURALN);
TBD
Overload 3 dbms_tf.put_row_set(
rowset             IN dbms_tf.row_set_t,
replication_factor IN dbms_tf.tab_naturaln_t);
TBD
 
ROW_REPLICATION
Sets the replication factor per row
Overload 1		 dbms_tf.row_replication(replication_factor IN NATURALN);
DECLARE
 rowset dbms_tf.row_set_t;
BEGIN
  dbms_tf.row_replication(replication_factor => 2);
END;
/
Overload 2 dbms_tf.row_replication(replication_factor IN TAB_NATURALN_T);
TBD
 
ROW_TO_CHAR
Converts a row data value to a string representation dbms_tf.row_to_char(
rowset IN Row_Set_t,
rid    IN PLS_INTEGER,
format IN PLS_INTEGER DEFAULT FORMAT_JSON)
RETURN VARCHAR2;
DECLARE
 rowset dbms_tf.row_set_t;
 str varchar2(32767);
BEGIN
  dbms_tf.get_row_set(rowset);
  str := dbms_tf.row_to_char(rowset, 1);
  dbms_output.put_line(str);
END;
/
DECLARE
*
ERROR at line 1:
ORA-62562: The API Get_Env can be called only during execution time of a polymorphic table function.
ORA-06512: at "SYS.DBMS_TF", line 131
ORA-06512: at "SYS.DBMS_TF", line 156
ORA-06512: at "SYS.DBMS_TF", line 198
ORA-06512: at line 5
 
SUPPORTED_TYPE
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
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
 
XSTORE_CLEAR
Removes all key/value pairs dbms_tf.xstore_clear;
exec dbms_tf.xstore_clear;
 
XSTORE_EXISTS
Returns TRUE iff the key has an associated value. If key_type is null
(or unknown) then checks for any value, otherwise it only looks for the specified type of key		 dbms_tf.xstore_exists(
key      IN VARCHAR2,
key_type IN PLS_INTEGER DEFAULT NULL)
RETURN BOOLEAN;
BEGIN
  IF dbms_tf.xstore_exists(key('vc2') ) THEN
    dbms_output.put_line('======= exists with no type = ' || key('vc2') || ' TRUE');
  ELSE
    dbms_output.put_line('======= exists with no type ' || key('vc2') ||' FALSE');
  END IF;
END;
/
 
XSTORE_GET
Retrieve a Key/Value Store for PTF Execution State Management

Overload 1		 dbms_tf.xstore_get(
key   IN     VARCHAR2,
value IN OUT VARCHAR2);
CREATE OR REPLACE PROCEDURE fetch_rows AUTHID CURRENT_USER IS
  row_cnt CONSTANT PLS_INTEGER   := dbms_tf.get_env().row_count;
  xid     CONSTANT dbms_tf.xid_t := dbms_tf.get_xid();
  mxv     PLS_INTEGER;
  c_in    dbms_tf.tab_number_t;
  c_ou    dbms_tf.tab_number_t;
BEGIN
  dbms_tf.xstore_get('mxv', mxv);
  dbms_tf.get_col(1, c_in);

  FOR i IN 1 .. row_cnt LOOP
    IF (c_in(i) > NVL(mxv, c_in(i)-1)) THEN
      mxv := c_in(i);
    END IF;
    c_ou(i) := mxv;
  END LOOP;
  dbms_tf.put_col(1, c_ou);
  dbms_tf.xstore_set('mxv', mxv);
END;
Overload 2 dbms_tf.xstore_get(
key   IN     VARCHAR2,
value IN OUT NUMBER);
TBD
Overload 3 dbms_tf.xstore_get(
key   IN     VARCHAR2,
value IN OUT DATE);
TBD
Overload 4 dbms_tf.xstore_get(
key   IN     VARCHAR2,
value IN OUT BOOLEAN);
TBD
 
XSTORE_REMOVE
Removes any value associated with the given value. If key_type is null (or unknown) then delete all corresponding keys, otherwise delete just the specified key dbms_tf.xstore_remove(
key      IN VARCHAR2,
key_type IN PLS_INTEGER DEFAULT NULL);
TBD
 
XSTORE_SET
Sets a Key/Value Store for PTF Execution State Management

Overload 1		 dbms_tf.xstore_set(
key   IN VARCHAR2,
value IN VARCHAR2);
CREATE OR REPLACED PROCEDURE fetch_rows AUTHID CURRENT_USER IS
 row_cnt CONSTANT PLS_INTEGER   := dbms_tf.get_env().row_count;
 xid     CONSTANT dbms_tf.xid_t := dbms_tf.get_xid();
 mxv     PLS_INTEGER;
 c_in    dbms_tf.tab_number_t;
 c_ou    dbms_tf.tab_number_t;
BEGIN
  dbms_tf.xstore_get('mxv', mxv);
  dbms_tf.get_col(1, c_in);

  FOR i IN 1 .. row_cnt LOOP
    IF (c_in(i) > NVL(mxv, c_in(i)-1)) THEN
      mxv := c_in(i);
  END IF;
    c_ou(i) := mxv;
  END LOOP
  dbms_tf.put_col(1, c_ou);
  dbms_tf.xstore_set('mxv', mxv);
END;
/
Overload 2 dbms_tf.xstore_set(
key   IN VARCHAR2,
value IN NUMBER);
TBD
Overload 3 dbms_tf.xstore_set(
key   IN VARCHAR2,
value IN DATE);
TBD
Overload 4 dbms_tf.xstore_set(
key   IN VARCHAR2,
value IN BOOLEAN);
TBD

Related Topics
Built-in Functions
Built-in Packages
Pipelined Table Functions
What's New In 21c
What's New In 23c

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-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx