Getting ORA-22902 after upgrade from 10gR2 to 11gR2
775725May 27 2010 — edited Jun 1 2010Get this error "CURSOR expression not allowed" after upgrade. Worked fine under 10gR2:
function OpenSubjectTree(project number, categoryview number, role number)
return sys_refcursor is
vc sys_refcursor;
begin
open vc for
select c.proj_ref_category_id,c.parent_id,
trim(c.display_order||' '||c.name) refcat_name,
CURSOR
(select e.proj_eval_criteria_id,p.pec_ref_category_id,p.proj_ref_category_id,
ltrim(e.display_order||' '||e.name) subject_name,e.proj_eval_criteria_type from
proj_eval_criteria e, pec_ref_categories p
where p.proj_ref_category_id=c.proj_ref_category_id and
e.proj_eval_criteria_id=p.proj_eval_critera_id
order by upper(e.sort_order)) subjects
from proj_ref_categories c
where c.project_id=project
and c.proj_ref_category_type=categoryview
and exists (select null from resp_ref_cats where
responsibility_id=role and resp_ref_cats.proj_ref_category_id =
c.proj_ref_category_id )
start with c.parent_id is null
connect by prior c.proj_ref_category_id=c.parent_id
order siblings by upper(c.sort_order);
return vc;
exception when others then
raise_application_error(-20201,'ti$sql.opensubjecttree: '||sqlerrm);
end;