Dynamic order by without dynamic sql ?

I have a huge select statement cursor that's a union. Sorting of the cursor resultset should be dynamic (ie order by column comes into the proc as input parameter). Usually I do this as decode. eg

select
customer_no, customer_name, address, phone etc from
where
order by
decode(i_var, 'customer_no', 1,

                    'customer_name', 2)

This works fine as long as statment is NOT UNION. When it's Union, plsql complains and expects order by to be ONLY COLUMN NUMBER (and doesn't allow decode, function etc).

Anybody has any suggestion on how this can be done ? I want to avoid dynamic sql only because I think it makes code clumsy (putting the whole giant sql statement in a variable etc) rather than simply opening up the cursor.

Any ideas ? Is there a way of sorting pl*sql table type variable (if this works then I can get cursor data without order by and then order it by sorting before returning the resultset to caller VB program.

Rdbms version is 7.3.4.

Sameer Utrankar
sameeru_at_exchange.ml.com Received on Tue Feb 01 2000 - 13:30:49 CST