Altering the ORDER BY clause based on a parameter

From: jeremy <jeremy0505_at_gmail.com>
Date: Sun, 18 Nov 2007 18:45:04 -0000
Message-ID: <MPG.21aa98819cb5e56298a4be@news.individual.net>

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 


 ============================================================

Received on Sun Nov 18 2007 - 12:45:04 CST