Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: PL/SQL question
zheli2001_at_yahoo.com (Jeff) wrote in message news:<41266446.0206060743.21750974_at_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:
>
v_addtype is defined as a charater type. no need to add extra quotes.
> SQL> declare
> 2 v_addtype varchar2(100) := '''m'',''mb''';
try,
v_addtype1 varchar1(10) := 'm'; v_addtype2 varchar1(10) := 'mb';
> 3 i number;
> 4 Begin
> 5 SELECT count(*) into i
> 6 FROM UserAddr
> 7 WHERE addrtype in (v_addtype);
and where addrtype in (v_addtype1,v_addtype2);
This should work. In other words, you need a separate variable for each parameter. If you want to be able to pass any number of parameters to the "in" clause, the only way I know is to use dynamic SQL. You can find the syntax "execute immediate" in PL/SQL reference guide.
> 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 - 22:19:55 CDT