Re: Dynamic Order By Clause

In article <pan.2001.11.21.17.05.15.144.896_at_earthlink.net>, "Mladen says...
>
>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.

don't keep me in suspense, how would you do this with execute immediate? that can execute single statement or a select query that returns A row.

> I understod his question as "Is it possible
>to use bind variablwes in the ORDER BY clause".

not seeing the word bind varible anywhere -- I took a different tack -- which was to answer how to order by a column whose name you have in a variable..

> 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.

I think you mean "generic", cause using native DYNAMIC sql as I did, you can easily do this (and even use bind variables where bind variables can be used)

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

--
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