Re: Passing a parameter to a Where Clause in PL/SQL !

[Quoted] In article <eFsQ7.31281$KT.7682567_at_news4.rdc1.on.home.com>, "Kavous says...
>
>You will have to excuse me if this sounds a bit elementary, but I'm new to
>this stuff and have to find out ASAP. I have a VB application that passes
>several parameters to a PL/SQL package. These are used in a WHERE clauses of
>a SQL query. For example :
>
>Select a_column
>from a_table
>where b_column = ai_str1
>and c.column = ai_str2
>and d.column = ai_str3 -- the line I'm having problem with
>
>Now my problem is that a user can either send a string for ai_str3 or a null
>value. How can I structure the query such that if the user sends a null
>value, the third condition would be simply ignored.. I have been thinking
>along the lines of having :
>
>d_column = nvl(ai_str3, d_column)
>
>Would that work or what do you think I should do?
>
>

well, it might not work. consider:

scott_at_ORA817DEV.US.ORACLE.COM> variable ai_str3 number scott_at_ORA817DEV.US.ORACLE.COM> exec :ai_str3 := null

PL/SQL procedure successfully completed.

scott_at_ORA817DEV.US.ORACLE.COM>
scott_at_ORA817DEV.US.ORACLE.COM> select count(*)   2 from emp
  3 where comm = nvl( :ai_str3, comm )   4 /

  COUNT(*)

         4

scott_at_ORA817DEV.US.ORACLE.COM>
scott_at_ORA817DEV.US.ORACLE.COM> select count(*)   2 from emp
  3 /

  COUNT(*)

        14

scott_at_ORA817DEV.US.ORACLE.COM>

I think you were expecting the two queries to be the same, they are not. NULLS [Quoted] will get you everytime.

You can either:

scott_at_ORA817DEV.US.ORACLE.COM> create or replace procedure p ( ai_str3 in varchar2 )
  2 as
  3 type rc is ref cursor;
  4

  5      l_cursor rc;
  6      l_rec    dept%rowtype;
  7  begin
  8      if ( ai_str3 is null )
  9      then
 10          open l_cursor for select * from dept;
 11      else
 12          open l_cursor for select * from dept where dname like ai_str3;
[Quoted]  13      end if;
 14  
 15      loop
 16          fetch l_cursor into l_rec;
 17          exit when l_cursor%notfound;
 18          dbms_output.put_line( l_rec.dname );
 19      end loop;
 20      close l_cursor;
 21      dbms_output.put_line( '----------------' );

Procedure created.

scott_at_ORA817DEV.US.ORACLE.COM>
scott_at_ORA817DEV.US.ORACLE.COM> exec p( null ); ACCOUNTING
RESEARCH
SALES
OPERATIONS
x

PL/SQL procedure successfully completed.

scott_at_ORA817DEV.US.ORACLE.COM> exec p( '%O%' ); ACCOUNTING
OPERATIONS

PL/SQL procedure successfully completed.

or, if you have LOTS of combinations to deal with, see: http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1288401763279

[Quoted] for a method to do this with dynamic sql.

--
Thomas Kyte (tkyte_at_us.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