Thanks for yur suggestions
"Mark C. Stock" <mcstockX_at_Xenquery .com> wrote in message
news:aNydnZVv1-qbP8bdRVn-gg_at_comcast.com...
>
> "Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message
> news:1079715860.379852_at_yasure...
> | Jawahar Rajan wrote:
> | > All,
> | > I have a stored procedure that return a Ref Cursor
> | > The SQL in the procedure is
> | >
> | > Procedure getPatName(in_name IN Varchar2, retCursor OUT patCursor)
> | > Is
> | > Begin
> | > Open retCursor For
> | > SELECT NAME, weight
> | > FROM patient
> | > WHERE patient_name like = in_name;
> | >
> | > End getPatName;
> | >
> | > I have defined patCursor in the spec of the package of which
getPatName
> is a
> | > part of
> | >
> | > This procedure does return any rows despite the data being present I
am
> | > assuming the SQL using like is not correct. How can I build a SQL in a
> | > procedure using LIKE - is the above correct?
> | >
> | > Jawahar
> |
> | SELECT ...
> | FROM ...
> | WHERE patient_name LIKE '%' || in_name || '%';
> |
> | Take a look at the Soundex function too:
> | http://www.psoug.org/reference/string_func.html
> |
> | --
> | Daniel Morgan
> | http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
> | http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
> | damorgan_at_x.washington.edu
> | (replace 'x' with a 'u' to reply)
> |
>
> ouch! avoid the leading '%'!
>
> code the procedure with
>
> WHERE patient_name like in_name || '%'
>
> and let the user (or interface) supply the leading '%' in the (hopefully)
> rare case in which it's needed
>
> ;-{ mcs
>
>
Received on Sat Mar 20 2004 - 06:00:56 CST
