oracle dynamic sql evaluate expression in where clause

Question

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?

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
– OldProgrammer, Commented Aug 28, 2021 at 2:11
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
– Andrew Sayer, Commented Aug 30, 2021 at 7:41

APC · Accepted Answer · 2021-08-28 05:15:26Z

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;
/

answered Aug 28, 2021 at 5:15

APC

147k20 gold badges180 silver badges289 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

MT0 · Accepted Answer · 2021-08-28 09:24:43Z

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;
/

answered Aug 28, 2021 at 9:24

MT0

173k12 gold badges70 silver badges136 bronze badges

1 Comment

Andrew Sayer Over a year ago

If you rewrite like this then the dynamic SQL isn’t giving you anything. This could just be static SQL.

Collectives™ on Stack Overflow

oracle dynamic sql evaluate expression in where clause

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related