First, I just wanted to say how GREAT you site is and how much it helps developers like me.

I currently work w/ .NET and query against several Oracle 9.2.x databases.

One of the problems I have is how to dynamically sort a SQL query using a stored procedure and an INPUT parameter which passes in the column to sort by.

I've read a previous question that was posted on your site about this and the solution was to use a dynamically build your SQL statement concatenating all of your statements together using a string then open your REF CURSOR for that string.

(Note: procedure is usually in a package that uses ref cursor as a type declared in the package header. Can you use a ref cursor in a generic stored procedure or must you always use a package?)
e.g.
CREATE OR REPLACE PACKAGE myPackage AS
TYPE refcur IS REF CURSOR;
END;
CREATE OR REPLACE PACKAGE BODY myPackage IS
PROCEDURE pSortTest(myRefCursor OUT myPackage.refcur, myInputParm VARCHAR2) AS
v_sql VARCHAR2(50);
BEGIN
v_sql := 'SELECT * FROM employees ORDER BY ' || myInputParm;
OPEN myRefCursor FOR v_sql;
END pSortTest;
END;

I know with SQL Server you can do a CASE statement in the ORDER BY clause and this approach is recommended by Microsoft rather than building the query dynamically as a string b/c it allows for the plan to be built.

e.g.
CREATE PROCEDURE pSortTest @SortParm VARCHAR AS
SELECT * FROM employees
ORDER BY
CASE
WHEN @SortParm = 'emp_name' THEN emp_name
.....
END
GO

Since I'm using ORACLE 9.2.x what would be the best approach to use and why?

Is there anything new in 10g that will make this any easier?

Thank you for your time and help,
John