Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: PL/SQL question, Please help
In article <41266446.0206060746.37476677_at_posting.google.com>,
zheli2001_at_yahoo.com says...
>
>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:
>
while others have said "dynamic sql" -- I'll offer a different approach -- one that is bind variable friendly (no one should be surprised by that by now ;)
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:110612348061
Also, if you do the dynamic sql approach, please add:
execute immediate 'alter session set cursor_sharing=force';
open ref_cursor for query_string;
execute immediate 'alter session set cursor_sharing=exact';
to the code -- to permit bind variables to be used!
> 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
-- Thomas Kyte (tkyte@oracle.com) http://asktom.oracle.com/ Expert one on one Oracle, programming techniques and solutions for Oracle. http://www.amazon.com/exec/obidos/ASIN/1861004826/ Opinions are mine and do not necessarily reflect those of Oracle CorpReceived on Thu Jun 06 2002 - 17:27:37 CDT