Wild Card in stored proc 1

[sweetleaf]

How do you use wild card in a stored procedure?
In a regular select statment the following works:

select a.project_id, b.dev_id, b.dev_name, c.project_name, c.project_start_date
from di_proj_dev a, di_developer b, di_project c
where a.dev_id = b.dev_id and
a.project_id = c.project_id and
b.dev_name like '%John%';

- all John's are retrieved

HOWEVER, in a stored procedure the following doesn't work:

select a.project_id, b.dev_id, b.dev_name, c.project_name, c.project_start_date
from di_proj_dev a, di_developer b, di_project c
where a.dev_id = b.dev_id and
a.project_id = c.project_id and
b.dev_name like '%p_ID%' ;

- nothing is retrieved

(p_ID is passed in from a com object. When "like '%p_ID%'" is changed to "= p_ID" and a full name is typed it works - but why won't the wild card work?

thanks,

 

[MikeJones]

Oracle isn't using the value of the vairable here, it thinks you are looking for all records like the string literal '%p_id%' so anything containg the text p_id

try...

select a.project_id, b.dev_id, b.dev_name, c.project_name, c.project_start_date
from di_proj_dev a, di_developer b, di_project c
where a.dev_id = b.dev_id and
a.project_id = c.project_id and
b.dev_name like '%' || p_ID ||'%' ;

now the variable name is no longer enclosed in single speach marks so Oracle will no longer treat it as a string literal and instead recolve it to the variable value. the double bars (||) mean concatonate.

 

[sweetleaf]

Thank you Mike,

I tried your solution and it works,

cheers