Re: Altering the ORDER BY clause based on a parameter

Giganews Newsgroups
Subject: Re: Altering the ORDER BY clause based on a parameter
Posted by:  Michel Cadot (micadot{at}altern{dot}org)
Date: Sun, 18 Nov 2007

"jeremy" <jeremy05…@gmail.com> a écrit dans le message de news: MPG.21aa98819cb5e56298a4…@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
| ============================================================

order by decode(p_order_by,1,date_col,2,name_col,3,desc_col...)

Regards
Michel Cadot

Replies

In response to

Altering the ORDER BY clause based on a parameter posted by Jeremy on Sun, 18 Nov 2007