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