|
BigP,
You
could have an If Then ElsIf...Else statement that tested the input arguments of
interest (looks like p_loginid and p_firstname), each part having an appropriate
Select that did not use NVL if that input argument was Not Null. In
essence, you'd be tailoring your Ref Cursor to the input argument
that caused the most useful index to be used.
Right
now it looks like just three Selects might do it. One if p_loginid
was not null, one if p_firstname was not null, and one if both were
null.
Of
course, dynamic SQL would work, too, but that can get pretty messy to write and
maintain.
Jack
--------------------------------
Jack C.
Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit,
Inc.
Austin,
Texas
www.iNetProfit.com
[EMAIL PROTECTED]
(512)327-9068
Hi All ,
I am writing this proc does a search in
database based on these in parameters and returns a ref cursor .
Now one more of these in parameters can be null and my query should return
data neglecting null parameters . So I use nvl in the query as shown below
.
Now my problem is , the query doesnt use index
available on table since i m using a function .
Is there any way I can rewrite this query so
that it meets my requirements and use the index . I have other option is to
create a dynamic sql based on in parameters .
TIA for any help .
-BigP
PROCEDURE get_alertlog (
p_loginid varchar2 ,
p_startdate date,
p_firstname varchar2 ,
p_enddate date ,
p_status out number,
p_msg out varchar2,
p_refcursor out c_refcursor )
Begin
open p_refcursor for
select logpin , logtype , logaction , logdate , memberpin
FROM mem
WHERE loginid
=nvl(p_loginid , loginid )
AND logdate
between nvl(p_startdate,to_date('1-jan-1900','dd-mon-rrrr') )
And firstname = nvl( p_firstname , firstname
)
and nvl(p_enddate , sysdate ) ;
End
;
|
