The following package demonstrates the bare minimum components required to implement a PTF. All required components can be defined inside an "implementation package". Below we create an implementation package called "ptf_demo".
create or replace package ptf_demo as
--
-- the implementation package must have a function called "describe"
--
function describe( p_input_table_metadata in out dbms_tf.table_t )
return dbms_tf.describe_t ;
--
-- this next function can either be defined here or in a standalone function
-- it can have any valid name, but it must have at least one argument with a
-- data type of TABLE (a new data type introduced in 18.1)
--
function pass_through( p_table table )
return table pipelined row polymorphic using ptf_demo ;
--
-- alternately the function can specify "PIPELINED TABLE" (for a "Table Semantics" PTF)
-- instead of "PIPELINED ROW" (for a "Row Semantics" PTF)
--
-- the differences will be described later in this tutorial
--
/*
function pass_through( p_table table )
return table PIPELINED TABLE polymorphic using ptf_demo ;
*/
end ptf_demo ;
/
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
return null;
end describe ;
end ptf_demo;
/
The PTF can now be invoked like this ...
select * from ptf_demo.pass_through( scott.dept ) ;
DEPTNO DNAME LOC
---------- ---------- ----------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
... or this
select deptno, ename from ptf_demo.pass_through( scott.emp ) where deptno = 10 ;
DEPTNO ENAME
---------- ----------
10 KING
10 MILLER
10 CLARK
... or this
with t1 as ( select deptno, dname, ename from scott.dept join scott.emp using( deptno ) where deptno = 10 ) select t2.* from ptf_demo.pass_through( t1 ) t2 ;
DEPTNO DNAME ENAME
---------- ---------- ----------
10 ACCOUNTING KING
10 ACCOUNTING MILLER
10 ACCOUNTING CLARK
... but not like this.
declare v_table table ; v_count pls_integer ; begin v_table := SCOTT.DEPT ; select count(*) into v_count from ptf_demo.pass_through( v_table ) ; end ; /
ORA-06550: line 3, column 11: PLS-00765: COLUMNS or TABLE is not allowed in this context ORA-06550: line 3, column 11: PL/SQL: Item ignored ORA-06550: line 8, column 3: PLS-00320: the declaration of the type of this expression is incomplete or malformed ORA-06550: line 8, column 3: PL/SQL: Statement ignored ORA-06550: line 10, column 60: PLS-00320: the declaration of the type of this expression is incomplete or malformed ORA-06550: line 10, column 60: PL/SQL: ORA-00904: "V_TABLE": invalid identifier ORA-06550: line 10, column 3: PL/SQL: SQL Statement ignored 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:
Aliasing
Multiple functions can use the same implementation package. This effectively creates function aliases.
create or replace package ptf_demo as
function describe( p_table_metadata in out dbms_tf.table_t )
return dbms_tf.describe_t ;
function pass_through( p_table table )
return table pipelined row polymorphic using ptf_demo ;
function identity_transformation( p_table table )
return table pipelined row polymorphic using ptf_demo ;
end ptf_demo ;
/
create or replace package body ptf_demo as
function describe( p_table_metadata in out dbms_tf.table_t )
return dbms_tf.describe_t as
begin
return null;
end describe ;
end ptf_demo;
/
Let's test the first PTF in the package.
select * from ptf_demo.pass_through( scott.dept ) ;
DEPTNO DNAME LOC
---------- ---------- ----------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Now let's test the second PTF in the package.
select * from ptf_demo.identity_transformation( scott.dept ) ;
DEPTNO DNAME LOC
---------- ---------- ----------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
They both executed the same logic and returned the same results.