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:  Ana C. Dent (anacede…@hotmail.com)
Date: Sun, 18 Nov 2007

jeremy <jeremy05…@gmail.com> wrote in
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?

Dynamic SQL

Replies

In response to

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