SEO

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

On 14 Mai, 20:37, steph <stepha...@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!