Re: PL/SQL question

Hi..

I think the problem is in the assignment line:

   "v_addtype varchar2(100) := '''m'',''mb''';

You can't use that syntax...you correct it in the second example and it works

"Sergey Adamenko" <adamenko_at_i.com.ua> wrote in message news:ado9i2$249q$1_at_news.dg.net.ua...
>
> "Jeff" <zheli2001_at_yahoo.com> ???????/???????? ? ???????? ?????????:
news:41266446.0206060743.21750974_at_posting.google.com...
> > Hello All:
> >
> > In a PL/SQL procedure, I was tring to use a "in" statment in a select
> > query. But for some reason it did work. Here is my code:
> >
> > SQL> declare
> > 2 v_addtype varchar2(100) := '''m'',''mb''';
> > 3 i number;
> > 4 Begin
> > 5 SELECT count(*) into i
> > 6 FROM UserAddr
> > 7 WHERE addrtype in (v_addtype);
> > 8 DBMS_OUTPUT.put_line('##### count = ' || i||' #####' );
> > 9 End;
> > 10 /
> > ##### count = 0 #####
> >
> > PL/SQL procedure successfully completed.
> >
> > But if I put directly use the value instead of using the variable
> > "v_addtype". It works fine:
> >
> > SQL> declare
> > 2 i number;
> > 3 Begin
> > 4 SELECT count(*) into i
> > 5 FROM UserAddr
> > 6 WHERE addrtype in ('m','mb');
> > 7 DBMS_OUTPUT.put_line('##### count = ' || i||' #####' );
> > 8 End;
> > 9 /
> > ##### count = 265 #####
> >
> > PL/SQL procedure successfully completed.
> >
> > What was wrong in my first statement?
> >
> > Great thanks for any help.
> >
> > Jeff
>
>
>
> What's the type of UserAddr.addrtype? Is it CHAR?
> If so try use TRIM or RTRIM functions:
>
> WHERE trim(addrtype) in (v_addtype);
>
> Best regards,
> Sergey Adamenko
>
>
>
>
>
>
Received on Thu Jun 06 2002 - 14:19:53 CDT