Oracle - "bad bind variable" with bind variable inside function

Question

Why does this code:

with
function qry(v in varchar2) return varchar2 is
   begin
      return owa_util.ite(v like ('%' || lower(:param) || '%'),'Y','N');
   end;
select * from my_table where qry(my_col) = 'Y'

Gives me the following error:

ORA-06553: PLS-49: bad bind variable 'PARAM'
06553. 00000 -  "PLS-%s: %s"
*Cause:    
*Action:
Error at Line: 6 Column: 46

you can't stick a bind variable like that inside a function. pass it is as a parameter like the way you do for "v" — OldProgrammer, Apr 12 '21 at 00:00

score 2 · Accepted Answer · answered Apr 12 '21 at 00:37

I am assuming that you have declared the param bind variable; if you haven't then that is your first problem.

You do not need a function:

select *
from   my_table
where  my_col LIKE '%' || LOWER( :param ) || '%'

If you really want a function (don't as it would prevent you using an index on the column) then pass the bind value as an argument:

WITH FUNCTION qry(
    value  IN VARCHAR2,
    filter IN VARCHAR2
  ) RETURN VARCHAR2
  IS
  BEGIN
    RETURN CASE WHEN v LIKE '%' || lower(filter) || '%' THEN 'Y' ELSE 'N' END;
  END;
SELECT *
FROM   my_table
WHERE  qry(my_col, :param) = 'Y'

Oracle - "bad bind variable" with bind variable inside function

1 Answers1