Dynamic Sort

Let us say we have a requirement to sort the result set based on the given input parameter . In most of the system , the developers concacatenate the SELECT statement with ORDER BY clause .

In our example ( scott's emp table)  , this will be something like
....
lssql = "SELECT EMPNO , ENAME ,JOB ,HIREDATE , SAL ,DEPTNO FROM EMP ORDER BY " + p_input
....
...
The above sql is potential candiate for SQL injection . The above SQL can be rewritten as below to efficetly use BIND varaibles.
We can also employ dynamic SQL to implement the variants of dynamic SORT.

SQL> show user
USER is "ZAHIR"
SQL> create table emp as select * from scott.emp;
Table created.
SQL> desc emp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                              NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)
SQL> set pagesize 90
SQL> SELECT EMPNO ,
  2    ENAME ,
  3    JOB ,
  4    HIREDATE ,
  5    SAL ,
  6    DEPTNO
  7  FROM emp ;
     EMPNO ENAME      JOB       HIREDATE         SAL     DEPTNO
---------- ---------- --------- --------- ---------- ----------
      7369 SMITH      CLERK     17-DEC-80        800         20
      7499 ALLEN      SALESMAN  20-FEB-81       1600         30
      7521 WARD       SALESMAN  22-FEB-81       1250         30
      7566 JONES      MANAGER   02-APR-81       2975         20
      7654 MARTIN     SALESMAN  28-SEP-81       1250         30
      7698 BLAKE      MANAGER   01-MAY-81       2850         30
      7782 CLARK      MANAGER   09-JUN-81       2450         10
      7788 SCOTT      ANALYST   19-APR-87       3000         20
      7839 KING       PRESIDENT 17-NOV-81       5000         10
      7844 TURNER     SALESMAN  08-SEP-81       1500         30
      7876 ADAMS      CLERK     23-MAY-87       1100         20
      7900 JAMES      CLERK     03-DEC-81        950         30
      7902 FORD       ANALYST   03-DEC-81       3000         20
      7934 MILLER     CLERK     23-JAN-82       1300         10
14 rows selected.

SQL> variable p_input varchar2(20);
SQL> exec :p_input := 'EMPNO';

PL/SQL procedure successfully completed.
SQL> SELECT EMPNO ,
  2    ENAME ,
  3    JOB ,
  4    HIREDATE ,
  5    SAL ,
  6    DEPTNO
  7  FROM emp
  8  ORDER BY
  9    CASE
 10      WHEN :P_INPUT = 'EMPNO'
 11      THEN to_char(EMPNO)
 12      WHEN :p_input = 'ENAME'
 13      THEN ENAME
 14      WHEN :p_input = 'JOB'
 15      THEN JOB
 16      WHEN :P_INPUT = 'HIREDATE'
 17      THEN TO_CHAR(HIREDATE, 'YYYY-MM-DD')
 18      ELSE  to_char(DEPTNO)
 19    END
 20  ;
     EMPNO ENAME      JOB       HIREDATE         SAL     DEPTNO
---------- ---------- --------- --------- ---------- ----------
      7369 SMITH      CLERK     17-DEC-80        800         20
      7499 ALLEN      SALESMAN  20-FEB-81       1600         30
      7521 WARD       SALESMAN  22-FEB-81       1250         30
      7566 JONES      MANAGER   02-APR-81       2975         20
      7654 MARTIN     SALESMAN  28-SEP-81       1250         30
      7698 BLAKE      MANAGER   01-MAY-81       2850         30
      7782 CLARK      MANAGER   09-JUN-81       2450         10
      7788 SCOTT      ANALYST   19-APR-87       3000         20
      7839 KING       PRESIDENT 17-NOV-81       5000         10
      7844 TURNER     SALESMAN  08-SEP-81       1500         30
      7876 ADAMS      CLERK     23-MAY-87       1100         20
      7900 JAMES      CLERK     03-DEC-81        950         30
      7902 FORD       ANALYST   03-DEC-81       3000         20
      7934 MILLER     CLERK     23-JAN-82       1300         10
14 rows selected.

SQL> exec :p_input := 'JOB';
PL/SQL procedure successfully completed.
SQL> SELECT EMPNO ,
  2    ENAME ,
  3    JOB ,
  4    HIREDATE ,
  5    SAL ,
  6    DEPTNO
  7  FROM emp
  8  ORDER BY
  9    CASE
 10      WHEN :P_INPUT = 'EMPNO'
 11      THEN to_char(EMPNO)
 12      WHEN :p_input = 'ENAME'
 13      THEN ENAME
 14      WHEN :p_input = 'JOB'
 15      THEN JOB
 16      WHEN :P_INPUT = 'HIREDATE'
 17      THEN TO_CHAR(HIREDATE, 'YYYY-MM-DD')
 18      ELSE  to_char(DEPTNO)
 19    END
 20  ;
     EMPNO ENAME      JOB       HIREDATE         SAL     DEPTNO
---------- ---------- --------- --------- ---------- ----------
      7788 SCOTT      ANALYST   19-APR-87       3000         20
      7902 FORD       ANALYST   03-DEC-81       3000         20
      7934 MILLER     CLERK     23-JAN-82       1300         10
      7900 JAMES      CLERK     03-DEC-81        950         30
      7369 SMITH      CLERK     17-DEC-80        800         20
      7876 ADAMS      CLERK     23-MAY-87       1100         20
      7698 BLAKE      MANAGER   01-MAY-81       2850         30
      7566 JONES      MANAGER   02-APR-81       2975         20
      7782 CLARK      MANAGER   09-JUN-81       2450         10
      7839 KING       PRESIDENT 17-NOV-81       5000         10
      7844 TURNER     SALESMAN  08-SEP-81       1500         30
      7654 MARTIN     SALESMAN  28-SEP-81       1250         30
      7521 WARD       SALESMAN  22-FEB-81       1250         30
      7499 ALLEN      SALESMAN  20-FEB-81       1600         30
14 rows selected.
Comments welcome.