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

Home -> Community -> Usenet -> c.d.o.server -> Re: Functions with date inputs

Re: Functions with date inputs

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Sat, 13 Aug 2005 18:10:37 +0200
Message-ID: <rj6sf1dvs47rn1ga35opsvgfsakovajr97@4ax.com>


On 13 Aug 2005 08:23:48 -0700, 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;
>/
>--FUNCTION CREATES SUCCESSFULLY
>--THEN CALL FUNCTION
>BEGIN
> DBMS_OUTPUT.PUT_LINE('NO. SOLD = '||OLTP_NUMBERR_SOLD(07-JUL-2005,
>27-JUL-2005);
>
>--THEN I recieve error ORA-06650
>am I using the wrong date format to call the date? any assistance
>greatly appreciated..
>thanks
>Lisa

Ora-6650 probably should be ora-6550. You should always include the error message (not the error number alone) and the error stack. No one knows all the error numbers by heart on *you* should do the lookup on http://tahiti.oracle.com as this is a volunteer forum (and homework is also usually not supported)

When you have a date as parameter, it needs to be embedded in single quotes
'07-jul-2005' instead of 07-jul-2005
You should also not rely on a default date format but always use to_date('07-jul-2005','dd-mon-yyyy')

I won't comment on the quality of the class.

--
Sybrand Bakker, Senior Oracle DBA
Received on Sat Aug 13 2005 - 11:10:37 CDT

Original text of this message

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