Re: Functions with date inputs

Comments embedded.
lisa.sloane_at_gmail.com wrote:
> I am having a problem creating a function that accepts date input
> parameters. This is for my pl/sql class.
> I need to create a function called OLTP_NUMBER_SOLD to return number of
> car sales from OLTP Sales Table, given a starting and ending date as
> input parameters.
>
> this is what I have for my function:
> CREATE OR REPLACE FUNCTION OLTP_NUMBER_SOLD (p_start
> sales.sale_day%TYPE,
> p_end sales.sale_date%TYPE)
> RETURN NUMBER
> AS
> v_count NUMBER;
> BEGIN
> SELECT COUNT(*)
> INTO v_count
> FROM SALES
> RETURN v_count;
> END;
> /

And this function won't use the parameters passed to it, as none are listed in a WHERE clause for your query. As such this will always return the total count from the table, regardless of the date 'window'.  A better function would be:

CREATE OR REPLACE FUNCTION OLTP_NUMBER_SOLD (p_start DATE, p_end DATE) RETURN NUMBER
AS

     v_count NUMBER;
BEGIN

     SELECT COUNT(*)
     INTO v_count
     FROM SALES
     WHERE sale_date between p_start and p_end;
     RETURN v_count;

> --FUNCTION CREATES SUCCESSFULLY
> --THEN CALL FUNCTION
> BEGIN
> DBMS_OUTPUT.PUT_LINE('NO. SOLD = '||OLTP_NUMBERR_SOLD(07-JUL-2005,
> 27-JUL-2005);
>

Others have explained your error in calling the function. The most reliable way to call this would be:

BEGIN
    DBMS_OUTPUT.PUT_LINE('NO. SOLD =
'||OLTP_NUMBERR_SOLD(to_date('07-JUL-2005', 'DD-MON-YYYY'), to_date('27-JUL-2005','DD-MON-YYYY');

> --THEN I recieve error ORA-06650

The error is actually ORA-06550, and it is an error in formatting. You're using no formatting and you're also not using a string to delimit date values. Use the provided example, and the modified function, to complete your assigment.

> am I using the wrong date format to call the date? any assistance
> greatly appreciated..
> thanks
> Lisa

David Fitzjarrell Received on Tue Aug 23 2005 - 08:01:25 CDT