Re: PL/SQL question, Please help

On 6 Jun 2002 08:46:26 -0700, zheli2001_at_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) := '''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

Jeff,

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.

eg.
declare

     v_addtype varchar2(100) := ''m'', ''mb''';     i number;     

    v_nds varchar2(2000);
Begin

     v_nds := 'SELECT count(*)
                 FROM  UserAddr
                WHERE addrtype in ('||v_addtype||')';
     execute immediate nds into i;
     DBMS_OUTPUT.put_line('##### count = ' || i||' #####' );

-- 
Darren