1

I am trying to pass an expression into the WHERE clause of my query using dynamic SQL. The expression can contain multiple filters/columns.

Similar to other posts on SO, the following (example 1) works:


DECLARE
    where_expression VARCHAR2(40) := q'[filter_column = 'some_value')]';
    plsql_block VARCHAR2(500);

BEGIN
    plsql_block := 'SELECT column FROM mytable';

    EXECUTE IMMEDIATE plsql_block || ' WHERE ' || where_expression;

END;
/

And this approach (example 2) using placeholders does not work:


DECLARE
    where_expression VARCHAR2(40) := q'[filter_column = 'some_value')]';
    plsql_block VARCHAR2(500);

BEGIN
    plsql_block := 'SELECT column FROM mytable WHERE :a';

    EXECUTE IMMEDIATE plsql_block USING where_expression;

END;
/

Oracle returns an error: ORA-00920: invalid relational operator at line 8 (EXEC statement).

What am I doing wrong in example 2 and what's the correct way with placeholders?

MT0
  • 143,790
  • 11
  • 59
  • 117
DSH
  • 1,038
  • 16
  • 27
  • 1
    You can't do that. :a is just getting passed a string. it does not get evaluated. If you need complete control, then you need to use the DBMS_SQL package. – OldProgrammer Aug 28 '21 at 02:11
  • I see. How would it be done with DBMS_SQL package? – DSH Aug 28 '21 at 12:59
  • You would need to split up the input into expressions with placeholders and the values for those placeholders. If you have a variable number of placeholders then it gets complicated as you will have to use multiple dynamic SQL statements with different numbers of parameters and logic to execute just one. Or you could go with the lazy option of `cursor_sharing=force`. Whatever you do, this is going to be highly susceptible to SQL injection, you need to validate the input - you’re probably best off using static SQL with allowed filters. – Andrew Sayer Aug 30 '21 at 07:41

2 Answers2

1

What am I doing wrong in example 2 and what's the correct way with placeholders?

The placeholder syntax is for passing values to be checked when the statement is executed. The expected usage is something like this:

DECLARE
    v_out_1     varchar2(32);
    v_out_2     varchar2(32);
    plsql_block VARCHAR2(500);

BEGIN
    plsql_block := 'SELECT column FROM mytable WHERE filter_column = :a';

    EXECUTE IMMEDIATE plsql_block INTO v_out_1 USING 'some value';

    EXECUTE IMMEDIATE plsql_block INTO v_out_2 USING 'another value';
END;
/
APC
  • 144,005
  • 19
  • 170
  • 281
0

Whitelist the possible filter columns in a CASE expression:

DECLARE
  v_out        VARCHAR2(32);
  column_name  VARCHAR2(30) := 'COLUMN1';
  column_value VARCHAR2(30) := 'value1';
  sql          VARCHAR2(500);
BEGIN
  sql := 'SELECT column
          FROM   mytable
          WHERE  CASE :name
                 WHEN ''COLUMN1'' THEN column1
                 WHEN ''COLUMN2'' THEN column2
                 WHEN ''COLUMN3'' THEN column3
                 END = :value';

  EXECUTE IMMEDIATE sql INTO v_out USING column_name, column_value;
END;
/
MT0
  • 143,790
  • 11
  • 59
  • 117