Re: Dynamic Order By Clause

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 21 Nov 2001 10:21:01 -0800
Message-ID: <9tgrad02deh@drn.newsguy.com>

In article <7e1c2f8c.0111210812.416e524f_at_posting.google.com>, prakashcn_at_hotmail.com says...
>
>Hi,
>
> Can i have a dynamic order by clause, following is my example which
> is in a package
>
> select * from tab1
> order by columnname_in
>
> columnname_in will have the actual column name
> giving the flexibility of ordering by any column which is in tab1
> with out hardcoding the column name
>
> Thanks in advance
> -Regards
> Prakash C N

sigh, no versions.... bummer. Ok assuming Oracle8i and up is reasonable so...

declare

   type rc is ref cursor;
   l_cursor rc;
   l_rec tab1%rowtype;
begin

   open l_cursor for 'select * from tab1 order by ' || columnname_in;    loop

       fetch l_cursor into l_rec;
       exit when l_cursor%notfound;
      ....

   end loop;
   close l_curslr;
end;
/

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Wed Nov 21 2001 - 12:21:01 CST