CODEWITHSUNDEEP
oracle-databasedynamic-sqldatabase-cursorbind-variables
Peter Gubik
Peter Gubik

Reputation: 369

Oracle - using bind variable in LIKE clause of dynamic cursor

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?

Upvotes: 9

Views: 11345

Answers (3)

cartbeforehorse
cartbeforehorse

Reputation: 3487

Just for anyone else who happens to be passing, you can also put the % wildcard in the bind-string itself:

DECLARE
   cursor_ SYS_REFCURSOR;
   qry_ VARCHAR2(2000) := q'[
with cat_names as (
select 'Bobby' names from dual union
select 'Tracy' names from dual union
select 'Jack' names from dual union
select 'Barnet' names from dual union
select 'Sally' names from dual union
select 'Bruce' names from dual
)
select c.names
from   cat_names c
where  c.names LIKE :var]';

   var_  VARCHAR2(20) := 'B%';
   name_ VARCHAR2(50);

BEGIN
   OPEN cursor_ FOR qry_ USING var_;
   LOOP
      FETCH cursor_ INTO name_;
      EXIT WHEN cursor_%NOTFOUND;
      Dbms_Output.Put_Line (name_);
   END LOOP;
   CLOSE cursor_;
END;

And the output is:

Barnet
Bobby
Bruce

This solution is more dynamic than the accepted answer, because it gives your user/client-application the choice of whether to use a % match, and not every search has to be done on the whole string. Depending on your data-set and table structure, this could drastically hit your performance because a preceding % on a search string (such as the enforced %:bv1% in the accepted answer) normally means the optimizer can't use an index.

Disclaimer: Tested in Oracle 19 only!

Upvotes: -1

APC
APC

Reputation: 146349

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

Upvotes: 11

MT0
MT0

Reputation: 168681

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

Upvotes: 1

Related Questions