* Transform c2 from test1 into c2+10 when c1<>'a'
* Strong typed refcursor is needed for parallel enable pipe function
* Record type declared, since we send in only two columns and we can not use test1%rowtype

*PARALLEL_ENABLE is an optimization hint indicating that the function can be executed from a 
parallel execution server of a parallel query operation. The function should not use session state, 
such as package variables, as those variables may not be shared among the parallel execution servers.

* The optional PARTITION argument BY clause is used only with functions that have a REF CURSOR argument type. 
It lets you define the partitioning of the inputs to the function from the REF CURSOR argument.
Partitioning the inputs to the function affects the way the query is parallelized when the function is used 
as a table function (that is, in the FROM clause of the query). 
ANY indicates that the data can be partitioned randomly among the parallel execution servers. 
Alternatively, you can specify RANGE or HASH partitioning on a specified column list.


SQL> create type obj1_ as object (c1 varchar2(3), c2 number(2));
  2  /

Type created.

SQL> create type tab_obj1_ as table of obj1_;
  2  /

Type created.

SQL> create or replace package pkg1_ as
  2  type rec1 is record (c1 varchar2(2),c2 number(1));
  3  type ref1 is ref cursor return rec1;
  4  end;
  5  /

Package created.

SQL> 
SQL> create or replace function pipe_fun1(ref_in pkg1_.ref1)
  2  return tab_obj1_
  3  pipelined
  4  parallel_enable(partition ref_in by any)
  5  as
  6  c1p varchar2(3);
  7  c2p number(2);
  8  begin
  9    loop
 10      fetch ref_in into c1p, c2p;
 11      exit when ref_in%notfound;
 12      if c1p<>'a' then
 13         pipe row (obj1_(c1p,c2p+10));
 14      end if;
 15    end loop;
 16  return;
 17  end;
 18  /

Function created.

SQL> select * from test1;

C1         C2      SUMC2
-- ---------- ----------
a           1        100
b           2        400
a           1        135
a           2        430
c           1         20
d           1         28
e           1         20
f           1         28
g           1        300
h           1        376
a           1        200

C1         C2      SUMC2
-- ---------- ----------
b           2        130
a           1        360
a           2        100
c           1        200
d           1         84
e           1        260
f           1       2008
g           1         26
h           1         90
O           9          9
Q           0          0

C1         C2      SUMC2
-- ---------- ----------
G           7          7

23 rows selected.

SQL> select * from table(pipe_fun1(cursor(select c1,c2 from test1)));

C1          C2
--- ----------
b           12
c           11
d           11
e           11
f           11
g           11
h           11
b           12
c           11
d           11
e           11

C1          C2
--- ----------
f           11
g           11
h           11
O           19
Q           10
G           17

17 rows selected.