Re: Altering the ORDER BY clause based on a parameter

"jeremy" <jeremy0505_at_gmail.com> a écrit dans le message de news: MPG.21aa98819cb5e56298a4be_at_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 Received on Sun Nov 18 2007 - 14:46:16 CST