Can I use "order by" with user input in oracle?
Problem Description:
I use the same procedure for several tasks. Each function also needs to use ORDER BY every time.
I want to use ORDER BY with user inputs.
I tried this but it didn’t work.
PROCEDURE GET_DEPARTMENT_LIST(ORDER_BY_PARAM IN VARCHAR2, DEPT_DATA OUT T_CURSOR) IS
V_CURSOR T_CURSOR;
BEGIN
OPEN V_CURSOR FOR
SELECT GET_LIST(ORDER_BY_PARAM) FROM DUAL;
DEPT_DATA := V_CURSOR;
END GET_DEPARTMENT_LIST;
FUNCTION GET_LIST (PAR_ORDER_BY IN VARCHAR2)
RETURN SYS_REFCURSOR
IS
L_CR SYS_REFCURSOR;
BEGIN
OPEN L_CR FOR
SELECT DEPARTMENT_ID, DEPARTMENT_CODE, DEPARTMENT_NAME
FROM DEPARTMENT ORDER BY PAR_ORDER_BY ASC;
RETURN L_CR;
END;
Procedure Executed Query :
VARIABLE RC REFCURSOR;
EXECUTE DEPARTMENT_PKG.GET_DEPARTMENT_LIST('DEPARTMENT_NAME', :RC);
PRINT RC;
Result :
Solution – 1
Sure you can. For example:
SQL> create or replace function get_list (par_order_by in varchar2)
2 return sys_refcursor
3 is
4 l_rc sys_refcursor;
5 begin
6 open l_rc for
7 'select empno, ename, job, sal from emp where deptno = 10 order by ' || par_order_by;
8 return l_rc;
9 end;
10 /
Function created.
Testing:
SQL> select get_list('ename') result from dual;
RESULT
--------------------
CURSOR STATEMENT : 1
CURSOR STATEMENT : 1
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7782 CLARK MANAGER 2450
7839 KING PRESIDENT 5000
7934 MILLER CLERK 1300
SQL> select get_list('job, sal') result from dual;
RESULT
--------------------
CURSOR STATEMENT : 1
CURSOR STATEMENT : 1
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7934 MILLER CLERK 1300
7782 CLARK MANAGER 2450
7839 KING PRESIDENT 5000
SQL>
As you noticed, I created a function – for simplicity – instead of a procedure with an OUT parameter. If you have to use a procedure, no problem; it is the ORDER BY you had problem with, not the way you’ll return the result to the caller.
[EDIT: procedure that calls a function]
SQL> CREATE OR REPLACE PROCEDURE get_dept_list (
2 order_by_param IN VARCHAR2,
3 dept_data OUT SYS_REFCURSOR)
4 IS
5 BEGIN
6 OPEN dept_data FOR SELECT get_list (order_by_param) FROM DUAL;
7 END get_dept_list;
8 /
Procedure created.
SQL> CREATE OR REPLACE FUNCTION get_list (par_order_by IN VARCHAR2)
2 RETURN SYS_REFCURSOR
3 IS
4 l_rc SYS_REFCURSOR;
5 BEGIN
6 OPEN l_rc FOR
7 'select empno, ename, job, sal from emp where deptno = 10 order by '
8 || par_order_by;
9
10
11 RETURN l_rc;
12 END;
13 /
Function created.
Sorted by ENAME:
SQL> var rc refcursor
SQL> exec get_dept_list('ename', :rc)
PL/SQL procedure successfully completed.
SQL> print rc
GET_LIST(:B1)
--------------------
CURSOR STATEMENT : 1
CURSOR STATEMENT : 1
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7782 CLARK MANAGER 2450
7839 KING PRESIDENT 5000
7934 MILLER CLERK 1300
Sorted by JOB:
SQL> exec get_dept_list('job', :rc)
PL/SQL procedure successfully completed.
SQL> print rc
GET_LIST(:B1)
--------------------
CURSOR STATEMENT : 1
CURSOR STATEMENT : 1
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7934 MILLER CLERK 1300
7782 CLARK MANAGER 2450
7839 KING PRESIDENT 5000
Sorted by SAL in descending order:
SQL> exec get_dept_list('sal desc', :rc)
PL/SQL procedure successfully completed.
SQL> print rc
GET_LIST(:B1)
--------------------
CURSOR STATEMENT : 1
CURSOR STATEMENT : 1
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7839 KING PRESIDENT 5000
7782 CLARK MANAGER 2450
7934 MILLER CLERK 1300
SQL>