connect by does not connect fileds of object

From: Oleg Paraschenko <usenet_at_sein.sportwetten.spb.ru>
Date: 12 Mar 2003 04:19:12 -0800
Message-ID: <70f723dc.0303120419.19bef592@posting.google.com>

Hello all,

  I'm using simultaneously object features and hierarchical query, and it seems that I do something wrong. Would you please suggest any ideas?

<code>
declare
vIntTbl t_integer_tbl;
vObjTbl t_i_c128_i_i_tbl := t_i_c128_i_i_tbl(t_i_c128_i_i(1,'a',1,1)); begin
select i1 bulk collect into vIntTbl from (   select i1 from table(cast(vObjTbl as t_i_c128_i_i_tbl)))   connect by i1>0 -- (*)
;

dbms_output.put_line('obj count: '||vObjTbl.count);
dbms_output.put_line('int count: '||vIntTbl.count);
if  vIntTbl.count > 0  then
dbms_output.put_line('int: '||vIntTbl(1));

end if;
end;
</code>

Code defines table with one object, field of which is used in hierarchical query. Result of execution is printed.

When I execute the code (on 9.0.0.1 and 9.2.0), I get next result: <result>
obj count: 1
int count: 0
</result>
I expected either non-zero "int count" either "connect by loop in user data".

Is here something wrong?

---

Additional information.

When I delete line marked (*) ("connect by i1>0"), I get "int count" = 1.

When I change "select i1 from table(cast(vObjTbl as t_i_c128_i_i_tbl)))" to "select 1 i1 from dual)", I get error "CONNECT BY loop in user data". It is ok for me. If error is produced, it means that query at least work.

Definition of types:

create type t_integer_tbl as table of integer;
/

create or replace type t_i_c128_i_i as object ( i1 integer,
c1 varchar2(128),
i2 integer,
i3 integer,
map member function compare return integer )
/

create or replace type body t_i_c128_i_i as map member function compare return integer is begin
return self.i1;
end;
end;
/

create or replace type t_i_c128_i_i_tbl as table of t_i_c128_i_i;
/

Also, when type was defined without "map member function", I got compilation error "cannot ORDER objects without MAP or ORDER method".

---

  Thank you in advance.

  Regards, Oleg Received on Wed Mar 12 2003 - 06:19:12 CST