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

Thank you all for your insightful advice. Looks like I 've got my work cut out for me - I need to learn a few things about PL/SQL in general and DNS in particular, as my queries contain a few of these parameters in a single SQL and also the queries themselves are pretty complex - one in particular is 4 pages (8.5" X 11'') long. Thank you agai.n

Regards, Kavous

Thomas Kyte <tkyte_at_us.oracle.com> wrote in message news:9utr3s01uke_at_drn.newsguy.com...
> 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
> 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;
> 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( '----------------' );
> 22 end;
> 23 /
>
> 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:128840176 3279
>
> 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
>
Received on Sun Dec 09 2001 - 22:01:37 CET