8

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?

General Grievance
  • 4,555
  • 31
  • 31
  • 45
Peter Gubik
  • 347
  • 1
  • 3
  • 10

2 Answers2

10

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||''%'' ';
APC
  • 144,005
  • 19
  • 170
  • 281
  • Thanks, it works now. I did it exactly how you said - going from static statement to dynamic, I just had no idea i still need to use `||` operators in dynamic query. – Peter Gubik May 22 '17 at 13:13
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
MT0
  • 143,790
  • 11
  • 59
  • 117
  • This would very probably also work, but the solution above is more useful for my needs. Thanks anyway :) – Peter Gubik May 22 '17 at 13:19
  • Thanks, used this and for some reason my *very limited* testing had this solution performing slightly better than the accepted answer. – DPenner1 Jun 06 '19 at 21:08