Path: news.easynews.com!easynews!cyclone.swbell.net!pln-e!spln!dex!extra.newsguy.com!newsp.newsguy.com!drn From: Thomas Kyte Newsgroups: comp.databases.oracle.server Subject: Re: PL/SQL question, Please help Date: 6 Jun 2002 15:27:37 -0700 Organization: Oracle Lines: 66 Message-ID: References: <41266446.0206060746.37476677@posting.google.com> NNTP-Posting-Host: p-492.newsdawg.com X-Newsreader: Direct Read News 2.96 Xref: easynews comp.databases.oracle.server:149804 X-Received-Date: Thu, 06 Jun 2002 15:48:37 MST (news.easynews.com) In article <41266446.0206060746.37476677@posting.google.com>, zheli2001@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 Corp