Re: refcursor for pipeline table function?

From: Jusung Yang <JusungYang_at_yahoo.com>
Date: 10 Jun 2003 08:35:56 -0700
Message-ID: <130ba93a.0306100735.66145908@posting.google.com>

Hmm... You sure? Can you show me the bugs? From this simple test, I did not see the problem you described. Here, using parallel enabled pipelined function, I want to transform data from 1 column based on the condition on another column. Namely

test1.c2 -> test1.c2+10 where test1.c1<>'a'...

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.

• Jusung Yang

Richard Kuhler <noone_at_nowhere.com> wrote in message news:<A1eFa.70034$S9.1996671_at_twister.socal.rr.com>...
> Jusung Yang wrote:
> >
> > I think what you are looking for is the PL/SQL record type.
> >
> > SQL> create or replace package pkg1_ as
> > 2 type rec_typ is record (num number, dt date);
> > 3 type ref_cur_typ is ref cursor return rec_typ;
> > 4 end;
> > 5 /
>
> Thanks, but there are a couple bugs related to using record types with
> pipeline functions (like you can't even recompile them!) as well as
> several limitations. Besides all the features that you get if you use
> objects (encapsulation, inheritence, polymorphism, etc...). See the
> other posters reply and my follow up on how to make it work with object
> types.
>
> Thanks,
> Richard
Received on Tue Jun 10 2003 - 10:35:56 CDT