oracle - SQL Dynamic ASC and DESC -

i have following sql statement order clause passed dynamically . how can pass 'order asc , desc' dynamically sql?

select table1.prod_id,table2.prod_name table1 left outer join table2               on table1.prod1 = table2.prod_id                order case when :odb = 1 prod_id end 

i pass order asc or desc above sql dynamically,

how can this?

you can solutions @tonyandrews manipulating numeric or data values. varchar2 alternative dynamic sql have 2 expressions:

order    case when :sorting='asc' col1 end asc,    case when :sorting='desc' col1 end desc 

when :sorting has value 'asc' result of order by becomes if had been:

order    col1 asc,    null desc 

when :sorting has value 'desc' result of order by becomes if had been:

order    null asc,    col1 desc 

one downside method cases optimizer can skip sort operation because there index involved makes data sorted desired, not happen when using case method this. mandate sorting operation no matter what.