Altering the ORDER BY clause based on a parameter

Giganews Newsgroups
Subject: Altering the ORDER BY clause based on a parameter
Posted by:  Jeremy (jeremy05…@gmail.com)
Date: Sun, 18 Nov 2007

Hi folks, is there a technique for achieving the following (9iR2 at
present for which a solution is sought - but 10g coming soon).

select date_col, name_col, desc_col, num_col
from  mytab
order by 1 desc;

Now supposing this code is in a PL/SQL procedure with input parameters
p_order_by and p_order_desc.

e.g.

create or replace procedure
myproc (p_order_by  in number default 1,
        p_order_desc in varchar2 default 'ASC')
is
begin

  for res in ( select date_col, name_col, desc_col, num_col
              from  mytab
              order by p_order_by)
  loop
    -- do things
  end loop;
end;
/

I know the above doesn't work.

Does anyone have a suggestion (or a pointer to where a solution lies)
which enables this to work, specifying also the direction of the order
by and which takes into consideration the data type as well?

--
jeremy

============================================================
  ENVIRONMENT:
  Oracle 9iR2 / Oracle HTTP Server / mod_plsql / Solaris 8
============================================================

Replies