|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
order by 1 desc;
Now supposing this code is in a PL/SQL procedure with input parameters
p_order_by and p_order_desc.
create or replace procedure
myproc (p_order_by in number default 1,
p_order_desc in varchar2 default 'ASC')
for res in ( select date_col, name_col, desc_col, num_col
order by p_order_by)
-- do things
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?
Oracle 9iR2 / Oracle HTTP Server / mod_plsql / Solaris 8