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 based 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.