Can I use "order by" with user input in oracle?-CodePudding

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 :

CodePudding user response：

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>