From: "Stephen B" <stephen.bell@cgi.ca>
Newsgroups: comp.databases.oracle.server
References: <41266446.0206060743.21750974@posting.google.com> <ado9i2$249q$1@news.dg.net.ua>
Subject: Re: PL/SQL question
Lines: 72
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <tEOL8.31133$Av5.2099903@news20.bellglobal.com>
Date: Thu, 6 Jun 2002 15:19:53 -0400
NNTP-Posting-Host: 207.164.204.5
X-Complaints-To: abuse@sympatico.ca
X-Trace: news20.bellglobal.com 1023391385 207.164.204.5 (Thu, 06 Jun 2002 15:23:05 EDT)
NNTP-Posting-Date: Thu, 06 Jun 2002 15:23:05 EDT
Organization: Bell Sympatico
Path: news.easynews.com!easynews!nntp.flash.net!newsswitch.lcs.mit.edu!sunqbc.risq.qc.ca!torn!webster!nf1.bellglobal.com!nf2.bellglobal.com!news20.bellglobal.com.POSTED!not-for-mail
Xref: easynews comp.databases.oracle.server:149780
X-Received-Date: Thu, 06 Jun 2002 12:21:50 MST (news.easynews.com)

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@i.com.ua> wrote in message
news:ado9i2$249q$1@news.dg.net.ua...
>
> "Jeff" <zheli2001@yahoo.com> ???????/???????? ? ???????? ?????????:
news:41266446.0206060743.21750974@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
>
>
>
>
>
>