0

I learned from here that a complete where-clause expression cannot be evaluated using placeholders with native dynamic SQL; placeholders are for passing values.

However, it was also mentioned in the comments that it may be possible to do this with DBMS_SQL.

What is the analogous version of the following code in DBMS_SQL:

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;
/
Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
DSH
  • 1,038
  • 16
  • 27
  • 4
    [This](https://docs.oracle.com/en/database/oracle/oracle-database/18/arpls/DBMS_SQL.html#GUID-5A464D35-DBDE-4A18-8AFE-F0F1A0FC4EB6) is a good start. – Jeff Holt Aug 28 '21 at 13:58
  • If you are able to run the query without dbms_sql then do it. It is less typing work. – Wernfried Domscheit Aug 29 '21 at 11:02
  • 2
    I don’t think that comment was particularly helpful. Dbms_sql does not allow you to pass in entire filters as a bind variable. At parse time, the statement needs to know the tables and columns being used so a valid execution plan can be created, the only things that can be later assigned on execution are values. – Andrew Sayer Aug 30 '21 at 07:34

0 Answers0