sql - Mix recursive queries and cursor expressions -

i have table contains data represents hierarchical structures. easiest way data out of table single "object" recursive query. same table stores "member variables" associated "object". thought nice see object structure associated member variables in single query, tried like:

cursor object_explorer            select (level*2) lvl, ob.object_id, lpad(ot1.object_type_name, 2*level + length(ot1.object_type_name), '.') ob_typ_nam               obj_tab ob, obj_type ot1                   , cursor (select lpad(mv.member_var_name, level + length(mv.member_var_name), ' ') var_nam, /*other stuff*/                              obj_type ot2, object_membervar_value omv, member_variable mv                               mv.member_variable_id = omv.member_variable_id                               , ot2.object_type_id = omv.object_type_id                               , omv.object_id = ob.object_id)               ot1.object_type_id = ob.object_type_id               , /*other filtering conditions unrelated problem @ hand*/               start ob.object_id = '1234567980abc'                connect nocycle ob.parent_object = prior ob.object_id; 

...and oracle tells me "cursor expression not allowed".

if 2 separate cursors (looping through results of 1 , using other cursor based on results), works fine, don't need single-cursor solution.

i wanted know why can't combine these 2 queries using cursor expressions - or can combine them , missed somehow?

(oracle version 10g)

i don't think need use cursor keyword there. explanation ora-22902 states, cursor() applicable in projection of select statement.

we can use inline views in our clause. in case like:

.... obj_tab ob, obj_type ot1      , (select omv.object_id                , lpad(mv.member_var_name, level + length(mv.member_var_name), ' ') var_nam                , /*other stuff*/          obj_type ot2, object_membervar_value omv, member_variable mv          mv.member_variable_id = omv.member_variable_id          , ot2.object_type_id = omv.object_type_id          ) iv iv.object_id = ob.object_id , /*filtering conditions unrelated problem @ hand*/ .... 

your clause not enough, because need joins inline view obj_type and/or obj_tab. that's why moved omv.object_id sub-query's projection: give hook outer-query's clause.