Path: news.easynews.com!easynews!crtntx1-snh1.gtei.net!nycmny1-snh1.gtei.net!news.gtei.net!newsfeed.mathworks.com!btnet-peer0!btnet-feed5!btnet!news.btopenworld.com!not-for-mail
From: DazzaL <duaij1REMOVEME@hotmail.com>
Newsgroups: comp.databases.oracle.server
Subject: Re: PL/SQL question, Please help
Date: Thu, 6 Jun 2002 21:25:50 +0000 (UTC)
Organization: BT Openworld
Lines: 68
Message-ID: <9jkvfuobkb5r3q73nvcerapc2bv5hp4q0e@4ax.com>
References: <41266446.0206060746.37476677@posting.google.com>
NNTP-Posting-Host: host213-120-104-74.in-addr.btopenworld.com
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: quoted-printable
X-Trace: paris.btinternet.com 1023398750 19264 213.120.104.74 (6 Jun 2002 21:25:50 GMT)
X-Complaints-To: news-complaints@lists.btinternet.com
NNTP-Posting-Date: Thu, 6 Jun 2002 21:25:50 +0000 (UTC)
X-No-Archive: yes
X-Newsreader: Forte Agent 1.91/32.564
Xref: easynews comp.databases.oracle.server:149793
X-Received-Date: Thu, 06 Jun 2002 14:23:31 MST (news.easynews.com)

On 6 Jun 2002 08:46:26 -0700, zheli2001@yahoo.com (Jeff) wrote:

>Hello All:
>
>     In a PL/SQL procedure, I was tring to use a "in" statment in a =
select
>     query. But for some reason it didn't work right. Here is my code:
>
>     SQL> declare
>       2       v_addtype  varchar2(100) :=3D '''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 =3D ' || i||' #####' );
>       9  End;
>      10  /
>     ##### count =3D 0 #####
>
>     PL/SQL procedure successfully completed.
>
>     But if I 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 =3D ' || i||' #####' );
>       8  End;
>       9  /
>     ##### count =3D 265 #####
>
>     PL/SQL procedure successfully completed.
>
>     What was wrong in my first statement?=20
>
>     Great thanks for any help.
>
>     Jeff

Jeff,=20

your first statement is looking for the rows that contains the string    =
 'm','mb'      not rows which contain m or mb.

You need to use dynamic sql if you want to do that.=20

eg.
declare
     v_addtype  varchar2(100) :=3D ''m'', ''mb''';
    i number;
   =20
    v_nds varchar2(2000);
Begin
     v_nds :=3D 'SELECT count(*)
                 FROM  UserAddr
                WHERE addrtype in ('||v_addtype||')';
     execute immediate nds into i;
     DBMS_OUTPUT.put_line('##### count =3D ' || i||' #####' );
End;


--=20
Darren