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