Oracle - using bind variable in LIKE clause of dynamic cursor

oracle cursor dynamic-sql bind-variables

Peter Gubik · May 22, 2017 · Viewed 8.2k times · Source

I am using dynamic cursor for fetching data. Query that is being executed looks similar to:

query := 'SELECT column1, column2 
          FROM my_table 
          WHERE column1 LIKE ''%:bv1%''';

And the cursor itself is executed like this:

OPEN my_cursor FOR query USING my_var1;

I also tried to check the query and print it:

... WHERE column1 LIKE '%:bv1%' ...

so apostrophes are escaped, but the cursor fetches no data. Is it even possible to use bind variables in LIKE clause and if yes, what did I do wrong?

Answer

This is a subtle one. It's often useful to start with a static statement, get that right, then convert it to dynamic SQL.

In non-dynamic SQL we might do it like this:

 SELECT column1, column2 
 FROM my_table 
 WHERE column1 LIKE '%' || local_var || '%';

The dynamic equivalent is

query := 'SELECT column1, column2 
          FROM my_table 
          WHERE column1 LIKE ''%''||:bv1||''%'' ';