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;
/
Improve this question
3
  • 4
    This is a good start. Commented Aug 28, 2021 at 13:58
  • If you are able to run the query without dbms_sql then do it. It is less typing work. Commented Aug 29, 2021 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. Commented Aug 30, 2021 at 7:34
Related questions
0
PL/SQL Dynamic SQL USING clause
0
how to pass a string variable to the where clause in dynamic sql
0
Dynamic where clause using "execute immediate USING" clause
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.