PL/SQL question

From: Jeff <zheli2001_at_yahoo.com>
Date: 6 Jun 2002 08:43:06 -0700
Message-ID: <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 Received on Thu Jun 06 2002 - 10:43:06 CDT