Re: Like Statement in PL/SQL

<juzer_at_my-deja.com> wrote in message news:8nk5ir$mn4$1_at_nnrp1.deja.com...
> Hi
> I am starting with PL/SQL and have written a procedure which takes one
> argument of type varchar2.
>
> The argument I pass to the procedure has to be used in a Like Clause of
> the select statement.
>
> Example
> Create Or Replace Procedure Get_Site_Details( strNum VarChar2 )
> AS
> Cursor cTest Is
> SELECT Site_Num, City From Site
> WHERE Site_Num Like ''' || % || strNum || ''';
> ......
> ...
> End Get_Site_Details;
>
> What is the correct syntax to be used since it select statement works
> if I do something like this.
>
> Create Or Replace Procedure Get_Site_Details( strNum VarChar2 )
> AS
> Cursor cTest Is
> SELECT Site_Num, City From Site
> WHERE Site_Num Like '%9803';
> ......
> ...
> End Get_Site_Details;
>
> Any Help will be greatly appreciated, as I am working on a deadline.
>
> Thanks
> Juzer Doriwala

In this statement:

WHERE Site_Num Like ''' || % || strNum || ''';

You have hard-coded the constant strNum. You need to end your quoted string before the variable, concatenate the variable, then finish with another quoted string (and you might need parentheses too because LIKE is a function that takes an argument):

WHERE Site_Num Like('''%''' || strNum || ''')''');

-Matt Received on Sat Aug 19 2000 - 02:05:54 CEST