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.