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 <FONT
color=#0000f0 size=2>( p_loginid varchar2
<FONT color=#0000f0
size=2>,
<FONT
color=#0000f0>
p_startdate date<FONT
color=#0000f0>,
p_firstname varchar2 ,
p_enddate date <FONT
color=#0000f0>,
p_status <FONT
color=#0000f0>out <FONT
color=#ff0000>number,<FONT
color=#000000>
p_msg <FONT
color=#0000f0>out <FONT
color=#ff0000>varchar2,<FONT
color=#000000>
p_refcursor <FONT
color=#0000f0>out c_refcursor )
Begin
open p_refcursor for <FONT
color=#000000>
select logpin <FONT
color=#0000f0>, logtype <FONT
color=#0000f0>, logaction <FONT
color=#0000f0>, logdate <FONT
color=#0000f0>, memberpin <FONT
color=#0000f0>
FROM mem
WHERE loginid
=nvl<FONT
color=#0000f0>(p_loginid ,
loginid <FONT
color=#0000f0>)
AND logdate
between nvl<FONT
color=#0000f0>(p_startdate<FONT
color=#0000f0>,to_date<FONT
color=#0000f0>('1-jan-1900'<FONT
color=#0000f0>,'dd-mon-rrrr'<FONT
color=#0000f0>) <FONT
color=#0000f0>)
And firstname = nvl( p_firstname , firstname
)
and nvl<FONT
color=#0000f0>(p_enddate <FONT
color=#0000f0>, <FONT
color=#0000f0>sysdate <FONT
color=#0000f0>) <FONT
color=#0000f0>;
<FONT color=#000000
size=1>
End ;
Received on Mon Sep 10 2001 - 16:15:12 CDT
