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?

APC · Accepted Answer · 2017-05-22 13:22:10Z

11

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

edited May 22, 2017 at 13:22

answered May 22, 2017 at 13:05

APC

147k20 gold badges180 silver badges289 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Peter Gubik Over a year ago

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.

MT0 · Accepted Answer · 2017-05-22 13:14:59Z

1

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

edited May 22, 2017 at 13:14

answered May 22, 2017 at 12:53

MT0

173k12 gold badges70 silver badges136 bronze badges

2 Comments

Peter Gubik Over a year ago

This would very probably also work, but the solution above is more useful for my needs. Thanks anyway :)

DPenner1 Over a year ago

Thanks, used this and for some reason my very limited testing had this solution performing slightly better than the accepted answer.

cartbeforehorse · Accepted Answer · 2023-10-25 19:14:41Z

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!

Collectives™ on Stack Overflow

Oracle - using bind variable in LIKE clause of dynamic cursor

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related