Re: Dynamic Order By Clause

From: Mladen Gogala <mgogala_at_earthlink.net>
Date: Wed, 21 Nov 2001 22:05:20 GMT
Message-ID: <pan.2001.11.21.17.05.15.144.896@earthlink.net>

In article <9tgrad02deh_at_drn.newsguy.com>, "Thomas Kyte" <tkyte_at_us.oracle.com> wrote:

> 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

Actually, this is a trick that could have been more easily accomplished by using EXECUTE IMMEDIATE. I understod his question as "Is it possible to use bind variablwes in the ORDER BY clause". The answer to that was "NO". because oracle needs access path (and that includes the use of sort/merge subsystem) at the time of parse operation, which means that it can not be dynamic.

-- 
Mladen Gogala
I love the smell of napalm in the morning. It smells like victory.
Apocalypse Now

Received on Wed Nov 21 2001 - 16:05:20 CST