Oracle: DBMS_TF.TABLE

Oracle: DBMS_TF.TABLE_T

dbms_tf.table_t is used in a PTF's implementation package's describe function to pass information about the polymorphed table.

In describe, the corresponding parameter is in out so that columns can be marked as pass through or for read.

TYPE TABLE_T IS RECORD ( column TABLE_COLUMNS_T,-- Column information, TABLE_COLUMNS_T is a TABLE OF COLUMN_T 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 table_schema_name dbms_quoted_id, -- schema name table table_name dbms_quoted_id -- table name );

Example code

create table tab (
   col_txt  varchar2(10),
   col_num  number (5,2),
   col_dat  date
);


create or replace package pkg as

    function describe(tab in out dbms_tf.table_t) return dbms_tf.describe_t;

 --
 -- Note, no implementation required for 'func' in package body:
 --
    function func(p_t table) return table pipelined row polymorphic using pkg;

end pkg;
/

create or replace package body pkg as

    function describe(tab in out dbms_tf.table_t) return dbms_tf.describe_t is
    begin

        dbms_output.put_line('schema_name:       ' || tab.schema_name      );
        dbms_output.put_line('package_name:      ' || tab.package_name     );
        dbms_output.put_line('ptf_name:          ' || tab.ptf_name         );
        dbms_output.put_line('table_schema_name: ' || tab.table_schema_name);
        dbms_output.put_line('table_name:        ' || tab.table_name       );

        for i in 1 .. tab.column.count loop
            dbms_output.put_line('  ' || tab.column(i).description.name);
        end loop;

        return null;
    end describe;

end pkg;
/

select * from pkg.func(tab);
-- schema_name:       RENE
-- package_name:      PKG
-- ptf_name:          FUNC
-- table_schema_name:
-- table_name:
-- "COL_TXT"
-- "COL_NUM"
-- "COL_DAT

with cte as (select rownum, col_txt from tab where rownum = 1)
select * from pkg.func(cte);
-- schema_name:       RENE
-- package_name:      PKG
-- ptf_name:          FUNC
-- table_schema_name:
-- table_name:
-- "ROWNUM"
-- "COL_TXT"

Cleaning up:

drop package pkg;
drop table tab purge;

Oracle: DBMS_TF.TABLE_T

Example code

See also