Oracle dynamic sort order in stored procedure

Hi,

I am so very close to having dynamic sorting working in a PL/SQL stored procedure, I just can't get the descending to work. When I add in the code for descending sort, the sp fails to build

My guess is that the problem is that the CASE within the ORDER BY is a case expression, which can only return a single value. I have tried a number of different arrangements but to no avail.

If I put the DESC after the CASE...END, then it will compile, however, this means I don't have dynamic sort order.

I have a nasty workaround where I have surrounded the entire query in an IF statement. But that's nasty.

Does anyone know how to get this working. I am trying to avoid dynamic SQL. Thanks,
Chris Needham

My table is:
CREATE TABLE MYTABLE
(

  COLUMN1 VARCHAR2(20 BYTE),
  COLUMN2 VARCHAR2(20 BYTE)
)

I also have:
CREATE OR REPLACE PACKAGE MyApplicationTypes IS

    TYPE refcursor IS REF CURSOR;
END MyApplicationTypes;
/

The stored procedure code is:
CREATE OR REPLACE PROCEDURE MyStoredProcedure
(

  SortColumn IN varchar2,
  SortOrder IN varchar2,
  ResultSet OUT MyApplicationTypes.RefCursor )
IS
BEGIN
  OPEN ResultSet FOR
    SELECT

      Column1,
      Column2
    FROM
      MyTable
    ORDER BY
      CASE
        WHEN SortColumn = 'Column1' and SortOrder = 'Ascending' THEN
Column1
        -- if I uncomment the following line I get PL/SQL: ORA-00905:
missing keyword
        --WHEN SortColumn = 'Column1' and SortOrder = 'Descending' THEN
Column1 DESC
        WHEN SortColumn = 'Column2' and SortOrder = 'Ascending' THEN
Column2
        -- if I uncomment the following line I get PL/SQL: ORA-00905:
missing keyword
        --WHEN SortColumn = 'Column2' and SortOrder = 'Descending' THEN
Column1 DESC
      END; 
            

END MyStoredProcedure;
/ Received on Fri May 26 2006 - 06:54:35 CDT