2

Oracle sql developer, Substitute dynamic operators in where clause instead manual operators

I am writing procedure that takes ARGUMENT which has set of operators.

that dynamic operator i just want substitute in where clause. kindly guide me how i can achieve this.

Select * from table where field (ARGUMENT) 100.

Note : in above query ARGUMENT has '+,<,>,<=,>='.

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
Jambu
  • 21
  • 1

1 Answers1

0

You cannot have dynamic operators in pure SQL, it must be static. You need to (ab)use EXECUTE IMMEDIATE in PL/SQL to execute the dynamic SQL.

For example, using standard EMP table in SCOTT schema:

SQL> set serveroutput on
SQL> declare
  2  v_empno NUMBER;
  3  v_operator VARCHAR2(2);
  4  v_dyn_sql VARCHAR2(200);
  5  BEGIN
  6  v_operator := '=';
  7  v_dyn_sql := 'SELECT empno FROM emp WHERE ename '||v_operator||' ''SCOTT''';
  8  EXECUTE IMMEDIATE v_dyn_sql INTO v_empno;
  9  DBMS_OUTPUT.PUT_LINE('employee number is '||V_EMPNO);
 10  END;
 11  /
employee number is 7788

PL/SQL procedure successfully completed.

Or, in SQL*Plus using bind variable:

SQL> variable v_operator varchar2(2);
SQL> exec :v_operator := '=';

PL/SQL procedure successfully completed.

SQL> print v_operator;

V_OPERATOR
----------------------
=

SQL> set serveroutput on
SQL> declare
  2  v_empno NUMBER;
  3  v_dyn_sql VARCHAR2(200);
  4  v_operator VARCHAR2(2);
  5  BEGIN
  6  v_operator := '=';
  7  v_dyn_sql := 'SELECT empno FROM emp WHERE ename '||:v_operator||' ''SCOTT''';
  8  EXECUTE IMMEDIATE v_dyn_sql INTO v_empno;
  9  DBMS_OUTPUT.PUT_LINE('employee number is '||V_EMPNO);
 10  END;
 11  /
employee number is 7788

PL/SQL procedure successfully completed.
Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
  • @Jambu Please mark it as answered, would help others! See [Stack Overflow tour](http://stackoverflow.com/tour) to understand how this site works. – Lalit Kumar B Nov 21 '15 at 08:27
  • 1
    just select the tick mark to the left of this answer – Pரதீப் Nov 21 '15 at 09:25
  • Proper use of bind variable would be `v_dyn_sql := 'SELECT empno FROM emp WHERE ename '||v_operator||' :p1'; EXECUTE IMMEDIATE v_dyn_sql USING 'SCOTT' INTO v_empno;` – Wernfried Domscheit Nov 21 '15 at 10:32
  • Why do you consider dynamic SQL as "abuse"? – Wernfried Domscheit Nov 21 '15 at 10:33
  • @WernfriedDomscheit That is not what OP wants, he wants the operator to be dynamic. And nobody is worried about sql injection to know the operator, but the value. And, yes this is an abuse of dynamic sql because this is not the purpose for which dynamic sql is supported in PL/SQL. – Lalit Kumar B Nov 21 '15 at 14:02