Path: news.easynews.com!easynews!news.he.net!news-hog.berkeley.edu!ucberkeley!newsfeed.stanford.edu!postnews1.google.com!not-for-mail
From: dyou98@aol.com (D.Y.)
Newsgroups: comp.databases.oracle.server
Subject: Re: PL/SQL question
Date: 6 Jun 2002 20:19:55 -0700
Organization: http://groups.google.com/
Lines: 55
Message-ID: <f369a0eb.0206061919.93a8472@posting.google.com>
References: <41266446.0206060743.21750974@posting.google.com>
NNTP-Posting-Host: 12.237.95.20
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1023419995 18969 127.0.0.1 (7 Jun 2002 03:19:55 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: 7 Jun 2002 03:19:55 GMT
Xref: easynews comp.databases.oracle.server:149817
X-Received-Date: Thu, 06 Jun 2002 20:17:20 MST (news.easynews.com)

zheli2001@yahoo.com (Jeff) wrote in message 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:
> 
v_addtype is defined as a charater type. no need to add extra quotes.

> SQL> declare
>   2       v_addtype  varchar2(100) := '''m'',''mb''';
try,
            v_addtype1 varchar1(10) := 'm';
            v_addtype2 varchar1(10) := 'mb';

>   3      i number;
>   4  Begin
>   5       SELECT count(*) into i
>   6       FROM  UserAddr
>   7       WHERE addrtype in (v_addtype);
and
            where addrtype in (v_addtype1,v_addtype2);

This should work. In other words, you need a separate variable for each 
parameter. If you want to be able to pass any number of parameters to
the "in" clause, the only way I know is to use dynamic SQL. You can find
the syntax "execute immediate" in PL/SQL reference guide. 

>   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