Dynamic Sorts in Pl/Sql

[Quoted] [Quoted] We are trying to create a procedure that will be able to sort fields dynamically. We want the same fields for each query, but at some times [Quoted] we want it sorted by one field, and sometimes by another.

We created the following procedure:
PROCEDURE testSort
(in sort field IN VARCHAR2,
in sort order IN VARCHAR2 DEFAULT 'asc', out cursor OUT pjcCursor)
IS
BEGIN
if in sort order = 'asc' then
OPEN out cursor FOR
SELECT cat itm id, cat itm nm FROM ITP CATALOG ITEM ORDER BY in sort field ASC;
else
open out cursor FOR
select cat itm id, cat itm nm FROM itp catalog item order by in sort field DESC;
end if;
END; It allows us to pass in a field name to sort by. Now, it works fine on [Quoted] ASC, but it never works on DESC. We think this is because Oracle really sees this query as

OPEN out cursor FOR
SELECT cat itm id FROM ITP CATALOG ITEM
[Quoted] ORDER BY 'in sort field' ASC; <-- notice quotes around field name

It seems to ignore everything following.....Now, I did try to create a [Quoted] procedure that had the sort field hardcoded, with quotes around the name [Quoted] and it would not sort either. So, that seems to be the problem, the quotes.

Does anyone have any guesses as to how we can make this work????

-- 

Thanks.

Patrick Casey
Applications Analyst Programmer II
UNC - Chapel Hill
patrick casey_at_unc.edu
919.962.0549

--