Friday, January 4, 2008

Oracle PL/SQL Functions and the LIKE operator

Had a bit of a struggle with PL/SQL functions today.

The idea was to see if a record existed in a table, based on a LIKE
condition. Really, rock simple stuff.

SQL Example:

"afiedt.buf" 4 lines, 55 characters

1 select count(1)
2 from test
3* where ssn like '%1817'
SQL> /

COUNT(1)
----------
1

SQL>

Should be simple to use this in a function, right? Turns out, not so much.

create or replace function zetest( inssn in char )
return number
is dacount number(10);
begin
select count(1)
into dacount
from test
where ssn like '%inssn';
return(dacount);
end;
/

SQL> select zetest(1817) from dual;

ZETEST(1817)
------------
0

SQL>

So, what the frak?

Apparently, to use a wildcard operator with PL/SQL you have to wiggle it a bit:

create or replace function zetest( inssn in char )
return number
is dacount number(10);
begin
select count(1)
into dacount
from test
where ssn like '%'||inssn;
return(dacount);
end;
/

SQL> select zetest(1817) from dual;

ZETEST(1817)
------------
1

SQL>

So, you pass the wildcard inside single quotes, then concatenate it to
the variable.

No comments: