Path: news.easynews.com!easynews!uunet!dfw.uu.net!sac.uu.net!sea.uu.net!ash.uu.net!news.boulder.noaa.gov!not-for-mail
From: Thomas Gaines <Thomas.Gaines@noaa.gov>
Newsgroups: comp.databases.oracle.server
Subject: Re: PL/SQL question, Please help
Date: Thu, 06 Jun 2002 10:56:51 -0600
Organization: NOAA Boulder
Lines: 63
Message-ID: <3CFF9453.35B8AE39@noaa.gov>
References: <41266446.0206060746.37476677@posting.google.com>
NNTP-Posting-Host: 192.149.148.31
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Trace: mwrns.noaa.gov 1023382459 6781 192.149.148.31 (6 Jun 2002 16:54:19 GMT)
X-Complaints-To: usenet@news.boulder.noaa.gov
NNTP-Posting-Date: Thu, 6 Jun 2002 16:54:19 +0000 (UTC)
X-Mailer: Mozilla 4.79 [en] (Windows NT 5.0; U)
X-Accept-Language: en
Xref: easynews comp.databases.oracle.server:149764
X-Received-Date: Thu, 06 Jun 2002 09:57:34 MST (news.easynews.com)

Jeff -

I commend you for your inventiveness, but what you're trying to
do just won't work.  Your v_addtype variable is simply a collection
of characters, and the addition of commas and quotes won't
change that.  Your query will return a non-zero count only
if addrtype is equal to the literal string "''m'',''mb''" (double quotes
are mine, single quotes are yours).

Instead, you're going to have to do some dynamic PL/SQL.  But it's
really quite straight-forward.  Check out the official Oracle docs, this
newsgroup's archives, and various Oracle Press and third-party
books for details and examples of dynamic query execution.  Think
"execute immediate".

Bye,
TG

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) := '''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 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