Dynamic Sorts in Pl/Sql

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 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 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
ORDER BY 'in sort field' ASC; <-- notice quotes around field name

It seems to ignore everything following.....Now, I did try to create a procedure that had the sort field hardcoded, with quotes around the name 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

--