The following example demonstrates passing parameters into a PTF.
(Note: The example is similar to the first example in Controlling Pass Through Columns. The only difference is that in Controlling Pass Through Columns the number of columns to keep was hardcoded to "2". In this lesson the value is passed as an argument via the P_COLUMN_COUNT parameter.)
create or replace package ptf_demo as
function describe(
p_input_table_metadata in out dbms_tf.table_t,
P_COLUMN_COUNT IN PLS_INTEGER )
return
dbms_tf.describe_t ;
function show_columns( p_table table, P_COLUMN_COUNT IN PLS_INTEGER )
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,
P_COLUMN_COUNT IN PLS_INTEGER )
return
dbms_tf.describe_t
as
i pls_integer := p_input_table_metadata.column.first ;
begin
while i is not null loop
if i > P_COLUMN_COUNT 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 ;
/
select *
from ptf_demo.show_columns( p_table => scott.emp, P_COLUMN_COUNT => 2 ) ;
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
select * from ptf_demo.show_columns( p_table => scott.emp, P_COLUMN_COUNT => 5 ) ;
EMPNO ENAME JOB MGR HIREDATE
---------- ---------- ---------- ---------- ----------
7839 KING PRESIDENT 1981-11-17
7934 MILLER CLERK 7782 1982-01-23
7782 CLARK MANAGER 7839 1981-06-09
7369 SMITH CLERK 7902 1980-12-17
7566 JONES MANAGER 7839 1981-04-02
7876 ADAMS CLERK 7788 1987-07-13
7902 FORD ANALYST 7566 1981-12-03
7788 SCOTT ANALYST 7566 1987-07-13
7499 ALLEN SALESMAN 7698 1981-02-22
7521 WARD SALESMAN 7698 1981-02-22
7654 MARTIN SALESMAN 7698 1981-09-28
7698 BLAKE MANAGER 7839 1981-05-01
7844 TURNER SALESMAN 7698 1981-09-08
7900 JAMES CLERK 7698 1981-12-03