Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Stored procedure/package with DateTime parameters need help

Re: Stored procedure/package with DateTime parameters need help

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 16 Jan 2007 07:52:46 -0800
Message-ID: <1168962763.574480@bubbleator.drizzle.com>


MrHelpMe wrote:
> Hello experts,
>
> 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.

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
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Tue Jan 16 2007 - 09:52:46 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US