Pass Dynamic Column name with dynamic asc or desc in Oracle Sql

Question

How can we pass dynamic column names with dynamic ASC or DESC in a select statement as in the following case:

SELECT emp_name, emp_id 
FROM employee 
ORDER BY (CASE WHEN :p_asc='asc' THEN :p_column_name END) ASC,
         (CASE WHEN :p_asc='asc' THEN :p_column_name END) DESC;

This query is not working - column names are not accepted - please help to achieve this.

Answer 1

Well, it depends on tool you use. SQL*Plus lets you do that. Here's example:

SQL> set ver off
SQL>
SQL> accept p_col prompt "Enter column name for sorting purposes: "
Enter column name for sorting purposes: ename
SQL> accept p_asc_desc prompt "Asc or Desc? "
Asc or Desc? desc
SQL>
SQL> select ename from emp order by &p_col &p_asc_desc;

ENAME
----------
WARD
TURNER
SMITH
SCOTT
MILLER
MARTIN
KING
JONES
JAMES
FORD
CLARK
BLAKE
ALLEN
ADAMS

14 rows selected.

SQL>

Right; rows are sorted by ENAME column in DESCending order.

Answer 2

1. Whitelist the column names in the WHEN clause of the CASE expression and put the column value into the THEN clause (otherwise you are sorting on the value of the bind variable and not the value of the column); and then
2. Use an ELSE clause and a static value so that when the condition is not matched then that part of the ORDER BY clause will give all the rows the same priority and you can specify the ORDER in a later part of the ORDER BY clause.

Like this:

SELECT emp_name, emp_id 
FROM   employee 
ORDER BY
       CASE 
       WHEN :p_column_name = 'EMP_NAME' AND :p_asc='asc'
       THEN emp_name
       WHEN :p_column_name = 'EMP_ID' AND :p_asc='asc'
       THEN TO_CHAR(emp_id, '000000') -- Assuming it is a number
       ELSE 'All rows ordered identically'
       END ASC,
       CASE 
       WHEN :p_column_name = 'EMP_NAME' AND :p_asc='desc'
       THEN emp_name
       WHEN :p_column_name = 'EMP_ID' AND :p_asc='desc'
       THEN TO_CHAR(emp_id, '000000') -- Assuming it is a number
       ELSE 'All rows ordered identically'
       END DESC;

Alternatively, build your query using dynamic SQL (ensuring that you sanitise your inputs to prevent SQL injection).