pipelining data between pipeline functions

From: steph <stephan0h_at_yahoo.de>
Date: Wed, 14 May 2008 11:37:52 -0700 (PDT)
Message-ID: <6802424f-c95d-486f-9a17-361e35ecdf2d@a23g2000hsc.googlegroups.com>

hello,

database version is 10.2.0.3

i'm trying to use pipeline function for transferring and transforming data. some questions arise ...

Type definitions might look like this:

create type lwps_type as object
(LWPS_ID NUMBER
, ...)
/

create type lwps_type_set as table of lwps_type;
/

pipeline function might look like this:

CREATE OR REPLACE function pfms_vgr_2nd(p_invals in sys_refcursor) return lwps_type_set pipelined
is
  out_rec lwps_type:=lwps_type(null,null,null,null,null,null,null,null

                              ,null,null,null,null,null,null,null,null);

  --in_rec pfms_vgr_type:=pfms_vgr_type(null,null);   --in_rec pfms_vgr_type_set%rowtype;

  vgr_kurz varchar2(5);
  vgr_bez varchar2(40);
  pgr_bezeichnung varchar2(40);

begin
  loop
    fetch p_invals into vgr_kurz,vgr_bez,pgr_bezeichnung;     exit when p_invals%notfound;
    out_rec.LWPS_ID:=null;
    out_rec.LWPS_INS_ID:=null;
...

    out_rec.LWPS_REFERENT:=null;
    out_rec.LWAPS_GRUPPENBEZEICHUNG:=pgr_bezeichnung;     pipe row(out_rec);
  end loop;
  return;
end;
/

And my query might look like this:

select * from table(pfms_vgr_2nd(cursor(select * from table(pfms_vgr_stage))))
/

Question #1:
I've decided to use a weak ref cursor (sys_refcursor) as an explicitly defined ref cursor would require a record type to be defined - and I wanted to spare me the redundancy of having an object type and a record type holding exactly the same information. Or is it possible to use an object type to define a ref cursor?

Question #2:
Originally I have defined my function like this (again to avoid redundancy in declaring data-types):

CREATE OR REPLACE function pfms_vgr_2nd(p_invals in sys_refcursor) return lwps_type_set pipelined
is
  ...
  in_rec pfms_vgr_type:=pfms_vgr_type(null,null);   --in_rec pfms_vgr_type_set%rowtype; ... i also tried this one - but this is leading to an ora-600!
begin
  loop
    fetch p_invals into in_rec;
    ...

But then my query

      select * from table(pfms_vgr_2nd(cursor(select * from table(pfms_vgr_stage))))
raises an "ORA-00932 inconsistent datatypes"-error.

Or is it possible to transform the query so that this error does not appear? I've tried with cast() - but to no success.

ok, I hope i was sufficiently clear in describing my questions - allthough I'm not quite sure about this. anyway, thanks for any input,

Stephan Received on Wed May 14 2008 - 13:37:52 CDT