Path: news.easynews.com!easynews!news.he.net!news-hog.berkeley.edu!ucberkeley!newsfeed.stanford.edu!postnews1.google.com!not-for-mail
From: zheli2001@yahoo.com (Jeff)
Newsgroups: comp.databases.oracle.server
Subject: PL/SQL question
Date: 6 Jun 2002 08:43:06 -0700
Organization: http://groups.google.com/
Lines: 40
Message-ID: <41266446.0206060743.21750974@posting.google.com>
NNTP-Posting-Host: 67.96.124.194
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1023378186 16767 127.0.0.1 (6 Jun 2002 15:43:06 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: 6 Jun 2002 15:43:06 GMT
Xref: easynews comp.databases.oracle.server:149761
X-Received-Date: Thu, 06 Jun 2002 08:40:31 MST (news.easynews.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