Stored procedure/package with DateTime parameters need help
MrHelpMe
Is there some who could help me with this. I have a number of
packages/stored procedures created in oracle 9.2 that need to accept
parameters. The parameters I have having trouble with are a StartDate
and EndDate. If I have a package/SP with a start and end date how do I
pass these in my where clause and how do I allow the user to either
select NULL(so that the recordset returns all data regardless of Start
and End Date) or a Start and End Date is they are looking for a range
of values in the recordset. Please note that this SP will be hooiked
upto Crystal Reports and the start and end date will be passed to the
report in ASP.
i.e
[CODE]
WHERE a.DateTime BETWEEN StartDate and EndDate...this is the part I am
unsure of
[CODE]
Thanks everyone.
Mark D Powell
What is the front-end tool written in?
The SQL is fine as written though depending on where the data is coming
from you are likely going to need to use to_date to convert the input
parameters to date values. You can either check the input parameters
for being NULL or have the front-end substitute sysdate for EndDate
when the parameter is NULL. For StartDate you can just substitute a
point in time well in the past.
Assuming a usuable index on the date column exits then for the case
when the entire date range is requested it would probably be better if
the BETWEEN cause was not part of the SQL statement. This could mean
that you want to code two separate SQL statements in the procedure and
execute the one that fits the requested data. The reason being that if
the index is chosen it will be beneficial for short range scans but
will be inefficient for selecting all the data.
A little performance testing may be called for using various
percentages of the total date range to determine if any special
handling is required.
HTH -- Mark D Powell --
DA Morgan
CREATE OR REPLACE PROCEDURE demoproc (sdate DATE, edate DATE) IS
retval NUMBER;
BEGIN
SELECT COUNT(*)
INTO retval
FROM all_objects
WHERE created BETWEEN sdate AND edate;
dbms_output.put_line(TO_CHAR(retval));
END demoproc;
/
set serveroutput on
exec demoproc(SYSDATE-100, SYSDATE)
HTH
--
Daniel A. Morgan
University of Washington
damo...@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
MrHelpMe
Mark,
Thanks for the reply. The Front end will be written in ASP. Oh I
think I see what you are saying. So rather then use BETWEEN I could
say
[code]
WHERE a.DateTime >= StartDate and a.DateTime <=EndDate
[/code]
Is this what you mean. Also, could you see anything wrong with this
beacuse I really don't
[code]
WHERE a.DATETIME BETWEEN NVL(StartDate,'1000-01-01') And
NVL(EndDate,'9999-01-01')
or like you said
[code]
WHERE a.DATETIME >= NVL(StartDate,'1000-01-01') And
a.DATETIME <= NVL(EndDate,'9999-01-01')
[/code]
Thanks again
Mark D Powell
What I want you to consider is if you are going to read the entire
possible range of dates then your are going to read the entire table
and you will not want the CBO to do it by using an index on the date
column. In this case a full table scan would be much more efficient.
But your code is probably going to have variable names in the date
value locations. With a bind variable the optimizer is not going to
know what percentage of the table is going to be read and will devise a
plan based on averages an assumed size of the amount of data being
scanned. With bind variable peeking it may peek at a single day query
but the very next use might scan the entire possible date range. To
prevent using the index for the full data or to force use of the index
for small ranges you might have to resort to some special logic.
That is why I suggested testing your procedure with a series of range
sizes: EndDate - StartDate for 1 day, 15 day, 30 days, 90 days, 180
days, 365 days, 730 days, etc.... depending on what the possible ranges
are.
The test results will let you know how well your procedure is going to
work.
HTH -- Mark D Powell --