Re: PL/SQL question, Please help

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
Received on Thu Jun 06 2002 - 11:56:51 CDT