Re: Altering the ORDER BY clause based on a parameter

jeremy <jeremy0505_at_gmail.com> wrote in
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?
>
>

Dynamic SQL Received on Sun Nov 18 2007 - 13:28:32 CST