Hi,

I am facing a wierd situation here.

I have a stored procedure with a cursor defined as

cursor AllGlobalCursor is select region_name,region_id from region where region_id <> p_selectedRegionId and rank is null and region_type = 'Global'
MINUS
select a.region_name,a.region_id from region a, regionhierarchy b
where b.region_child_id=a.region_id and b.region_parent_id = p_selectedRegionId and a.rank is null and region_type = 'Global'
order by region_name;

The procedure is compiling perfectly and is working.

but when i change the cursor to

cursor AllGlobalCursor is select region_name,region_id from region where region_id <> p_selectedRegionId and rank is null and region_type = 'Global'
MINUS
select a.region_name,a.region_id from region a, regionhierarchy b
where b.region_child_id=a.region_id and b.region_parent_id = p_selectedRegionId and a.rank is null and region_type = 'Global'
order by upper(region_name);

and try to compile the procedure it fails with the error

SQL> @C:\Companydb\codb_linkregions

Package created.

No errors.

Warning: Package Body created with compilation errors.

Errors for PACKAGE BODY CODB_LINKREGIONS:

LINE/COL ERROR
-------- -----------------------------------------------------------------
725/27 PL/SQL: SQL Statement ignored
731/17 PLS-00201: identifier 'REGION_NAME' must be declared

For the sake of simplicity of this post I have mentioned the UPPER function here. What I am trying is decode so that I can display certain records first in the order by clause.

Why is it failing with upper.

Thanks
Ron