Re: pipelining data between pipeline functions

On 14 Mai, 20:37, steph <stepha..._at_yahoo.de> wrote:
> 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

Solved it by moving everything into a package. So I'm now having record types instead of object types in the package definition which can be reused in the pipeline functions - thus avoiding any redundancy in type definitions. And everything is in one place now. beautiful! Received on Thu May 15 2008 - 03:04:30 CDT