A PTF can accept an input table and selectively include or exclude its columns in the output. This is controlled via a parameter passed to the implementation package's describe() function. Before we learn how to use this control let's first look at its datatype, dbms_tf.table_t. This datatype, and its releated types, are defined as follows.
TYPE TABLE_T IS RECORD(
column TABLE_COLUMNS_T,
schema_name DBMS_id,
package_name DBMS_id,
ptf_name DBMS_id ) ;
TYPE TABLE_COLUMNS_T IS TABLE OF COLUMN_T ;
TYPE column_t IS RECORD (
description COLUMN_METADATA_T,
pass_through BOOLEAN,
for_read BOOLEAN);
TYPE COLUMN_METADATA_T IS RECORD
( type PLS_INTEGER,
max_len PLS_integer DEFAULT -1,
name VARCHAR2(32767),
name_len PLS_INTEGER,
precision PLS_INTEGER,
scale PLS_INTEGER,
charsetid PLS_INTEGER,
charsetform PLS_INTEGER,
collation PLS_INTEGER );
We can visualize the data type like this.
table_t.schema_name
table_t.package_name
table_t.ptf_name
table_t.column
table_t.column(i).pass_through
table_t.column(i).for_read
table_t.column(i).description
table_t.column(i).description.type
table_t.column(i).description.max_len
table_t.column(i).description.name
table_t.column(i).description.name_len
table_t.column(i).description.precision
table_t.column(i).description.scale
table_t.column(i).description.charsetid
table_t.column(i).description.charsetform
table_t.column(i).description.collation
Note that table_t.column is a nested table. The examples in the DBMS_TF documentation use a FOR loop to iterate through such data types. This is o.k. when the data is densly packed but, as we'll soon see, this might not always be the case. To stay on the safe side the solutions in this tutorial will always iterate through nested tables using a WHILE loop, which works for both dense and sparse data.
Now let's see how to write a describe() function that hides certain columns, e.g. all columns appearing after the first two columns in the input table.
create or replace package ptf_demo as
function describe( p_input_table_metadata in out dbms_tf.table_t )
return dbms_tf.describe_t ;
function trim_columns( p_table table )
return table pipelined row polymorphic using ptf_demo ;
end;
/
create or replace package body ptf_demo as
function describe( p_input_table_metadata in out dbms_tf.table_t )
return dbms_tf.describe_t
as
i pls_integer := p_input_table_metadata.column.first ;
begin
while i is not null loop
if i > 2 then
p_input_table_metadata.column(i).pass_through := false ; -- hides the column
end if;
i := p_input_table_metadata.column.next(i) ;
end loop ;
return null ;
end describe ;
end ptf_demo ;
/
Let's test it.
select * from ptf_demo.trim_columns( scott.dept ) ;
DEPTNO DNAME
---------- ----------
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS
select * from ptf_demo.trim_columns( scott.emp ) ;
EMPNO ENAME
---------- ----------
7839 KING
7934 MILLER
7782 CLARK
7369 SMITH
7566 JONES
7876 ADAMS
7902 FORD
7788 SCOTT
7499 ALLEN
7521 WARD
7654 MARTIN
7698 BLAKE
7844 TURNER
7900 JAMES
Now let's see how p_table_metadata might become sparsely populated.
rem
rem we use the same package spec created above,
rem only the body changes in this example
rem
create or replace package body ptf_demo as
function describe( p_input_table_metadata in out dbms_tf.table_t )
return dbms_tf.describe_t
as
i pls_integer ;
begin
-- delete columns numbered 2 and 4
p_input_table_metadata.column.delete(2) ;
p_input_table_metadata.column.delete(4) ;
-- now the nested table is sparsely populated
return null ;
end describe ;
end ptf_demo ;
/
For a baseline, let's take a look at the entire EMP table.
select * from scott.emp ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
7839 KING PRESIDENT 1981-11-17 5000 10
7934 MILLER CLERK 7782 1982-01-23 1300 10
7782 CLARK MANAGER 7839 1981-06-09 2450 10
7369 SMITH CLERK 7902 1980-12-17 800 20
7566 JONES MANAGER 7839 1981-04-02 2975 20
7876 ADAMS CLERK 7788 1987-07-13 1100 20
7902 FORD ANALYST 7566 1981-12-03 3000 20
7788 SCOTT ANALYST 7566 1987-07-13 3000 20
7499 ALLEN SALESMAN 7698 1981-02-22 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 1250 500 30
7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 2850 30
7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30
7900 JAMES CLERK 7698 1981-12-03 950 30
Now let's compare this with the output of the PTF to confirm that columns 2 and 4 are hidden.
select * from ptf_demo.trim_columns( scott.emp ) ;
EMPNO JOB HIREDATE SAL COMM DEPTNO
---------- ---------- ---------- ---------- ---------- ----------
7839 PRESIDENT 1981-11-17 5000 10
7934 CLERK 1982-01-23 1300 10
7782 MANAGER 1981-06-09 2450 10
7369 CLERK 1980-12-17 800 20
7566 MANAGER 1981-04-02 2975 20
7876 CLERK 1987-07-13 1100 20
7902 ANALYST 1981-12-03 3000 20
7788 ANALYST 1987-07-13 3000 20
7499 SALESMAN 1981-02-22 1600 300 30
7521 SALESMAN 1981-02-22 1250 500 30
7654 SALESMAN 1981-09-28 1250 1400 30
7698 MANAGER 1981-05-01 2850 30
7844 SALESMAN 1981-09-08 1500 0 30
7900 CLERK 1981-12-03 950 30
So far the package works as expected. Let's see what happens if we try to iterate through the collection with a FOR loop now.
create or replace package body ptf_demo as
function describe( p_input_table_metadata in out dbms_tf.table_t )
return dbms_tf.describe_t
as
begin
-- delete column numbers 2 and 4
p_input_table_metadata.column.delete(2) ;
p_input_table_metadata.column.delete(4) ;
for i in 1 .. p_input_table_metadata.column.count loop
if p_input_table_metadata.column(i).description.name = '"SAL"' then
p_input_table_metadata.column(i).pass_through := false ;
end if;
end loop ;
return null ;
end describe ;
end ptf_demo ;
/
select * from ptf_demo.trim_columns( scott.emp ) ;
Error starting at line : 1 in command - select * from ptf_demo.trim_columns( scott.emp ) Error at Command Line : 2 Column : 8 Error report - SQL Error: ORA-62565: The Describe method failed with error(s). ORA-01403: no data found ORA-06512: at "JFUDA.PTF_DEMO", line 15 ORA-06512: at line 22
We encountered an ORA-01403: no data found exception. If we re-write the package to use a WHILE loop we avoid the exception.
create or replace package body ptf_demo as
function describe( p_input_table_metadata in out dbms_tf.table_t )
return dbms_tf.describe_t
as
i pls_integer ;
begin
-- delete column numbers 2 and 4
p_input_table_metadata.column.delete(2) ;
p_input_table_metadata.column.delete(4) ;
i := p_input_table_metadata.column.first ;
while i is not null loop
if p_input_table_metadata.column(i).description.name = '"SAL"' then
p_input_table_metadata.column(i).pass_through := false ;
end if;
i := p_input_table_metadata.column.next(i) ;
end loop ;
return null ;
end describe ;
end ptf_demo ;
/
This time the query works without error.
select * from ptf_demo.trim_columns( scott.emp ) ;
EMPNO JOB HIREDATE COMM DEPTNO
---------- ---------- ---------- ---------- ----------
7839 PRESIDENT 1981-11-17 10
7934 CLERK 1982-01-23 10
7782 MANAGER 1981-06-09 10
7369 CLERK 1980-12-17 20
7566 MANAGER 1981-04-02 20
7876 CLERK 1987-07-13 20
7902 ANALYST 1981-12-03 20
7788 ANALYST 1987-07-13 20
7499 SALESMAN 1981-02-22 300 30
7521 SALESMAN 1981-02-22 500 30
7654 SALESMAN 1981-09-28 1400 30
7698 MANAGER 1981-05-01 30
7844 SALESMAN 1981-09-08 0 30
7900 CLERK 1981-12-03 30