Mix recursive queries and cursor expressions

I have a table that contains data that represents hierarchical structures. The easiest way to get data out of this table for a single "object" is a recursive query. The same table also stores "member variables" that are associated with the "object". I thought it would be nice to see the object structure as well as associated member variables in a single query, so I tried something like:

cursor object_explorer is
           select (level*2) lvl, ob.object_id, lpad(ot1.object_type_name, 2*level + length(ot1.object_type_name), '.') ob_typ_nam
              from obj_tab ob, obj_type ot1
                  , cursor (select lpad(mv.member_var_name, level + length(mv.member_var_name), ' ') var_nam, /*other stuff*/
                             from obj_type ot2, object_memberVar_value omv, member_variable mv
                              where mv.member_variable_id = omv.member_variable_id
                              and ot2.object_type_id = omv.object_type_id
                              and omv.object_id = ob.object_id)
              where ot1.object_type_id = ob.object_type_id
              and /*other filtering conditions unrelated to problem at hand*/
              start with ob.objecT_id = '1234567980ABC' 
              connect by nocycle ob.parent_object = prior ob.object_id;

...and Oracle tells me "Cursor expression not allowed".

If I do this as two separate cursors (looping through the results of one and then using the other cursor b开发者_开发问答ased on those results), everything works fine, so I don't need a single-cursor solution.

I just wanted to know why I can't combine these two queries using cursor expressions - or can I combine them and I just missed it somehow?

(Oracle version is 10g)

I don't think you need to use the CURSOR keyword there. As the explanation for ora-22902 states, CURSOR() is only applicable in the projection of a SELECT statement.

We can use inline views in our FROM clause. In your case that would look like:

....
from 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*/
         from obj_type ot2, object_memberVar_value omv, member_variable mv
         where mv.member_variable_id = omv.member_variable_id
         and ot2.object_type_id = omv.object_type_id
         ) iv
where iv.object_id = ob.object_id
and /*filtering conditions unrelated to problem at hand*/
....

Your WHERE clause is not good enough, because you need something which joins the inline view to OBJ_TYPE and/or OBJ_TAB. That's why I moved omv.object_id into the sub-query's projection: to give a hook for the outer-query's WHERE clause.