Oracle - using bind variable in LIKE clause of dynamic cursor

Question

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 1

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||''%'' ';

Answer 2

Take the bind variable out of the string:

VARIABLE mycursor REFCURSOR;
VARIABLE bv1 VARCHAR2;

BEGIN
  :bv1 := 'X'; -- set the bind variable
END;
/

DECLARE
  query VARCHAR2(200) := 'SELECT * FROM DUAL WHERE DUMMY LIKE :value';
BEGIN
  OPEN :mycursor FOR query USING '%' || :bv1 || '%';
END;
/

PRINT mycursor;

Output

MYCURSOR
--------
DUMMY
-----
X