ORA-01830: date format picture problem with a Procedure

From: Steven Markey <s.markey_at_worldnet.att.net>
Date: Sat, 26 Jul 2003 00:11:22 GMT
Message-ID: <KGjUa.71444$3o3.4803760@bgtnsc05-news.ops.worldnet.att.net>

Hello:

My table is described as:

RENTAL# NUMBER
CUSTNAME VARCHAR2(15)
ITEM_NUMBER NUMBER(4)
OUT_DATE DATE
DUE_DATE DATE
IN_DATE DATE
FEE NUMBER(6,2) So, with that said, I've 1st set the following session level variable:

ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY hh24:mi';

After this I wrote and sucessfully compiled this procedure:

create or replace procedure AddRental (

   p_custname IN rental.custname%TYPE,
   p_item_number IN rental.item_number%TYPE,
   p_out_date IN rental.out_date%TYPE,
   p_fee IN rental.fee%TYPE) AS

  /*Defined all params above with type from table rental. Note the exclusion of in_date as this param

     is not necessary...going on the premise that the business won't know when the customer will

     actually return the rental.*/

BEGIN    INSERT INTO RENTAL (rental#, custname, item_number, out_date, due_date, in_date, fee)

   VALUES (RentalSeq.NEXTVAL, p_custname, p_item_number, to_date(p_out_date, 'MM/DD/YYYY hh24:mi'),

      next_business_date(to_date(p_out_date, 'MM/DD/YYYY')), NULL, p_fee);

   DBMS_OUTPUT.PUT_LINE('Transaction occured on: ' || to_char(p_out_date, 'DAY') || ' ' || to_date(p_out_date, 'MM/DD/YYYY'));

END AddRental;

Where next_business_date is as such:

create or replace function next_business_date (D IN DATE)  RETURN DATE IS --Function takes a date and returns the next business day.

 --Declare variable to store result
v_bizdate DATE;

--Declare a variable (in CHAR format) to store passed value for string comparison
v_storedate CHAR(3);

 --Begin body of function
 BEGIN

       --Convert and store  passed value in CHAR format into v_storedate
       SELECT to_char(D, 'DY')
       INTO v_storedate
       FROM dual;



        --Take the date and check to see if it is a business day or day on

the weekend.

--Test for Friday and add 3 days if this is so.

        IF  v_storedate = 'FRI' THEN
                v_bizdate := D + 3;



        --Test for Saturday and 2 days if this is so.

        ELSIF v_storedate = 'SAT' THEN
                v_bizdate := D + 2;



        --If it not Friday or Saturday add 1 day.

        ELSE
                      v_bizdate := D + 1;

        END IF;



        --Return the next business day.

        RETURN v_bizdate;

END next_business_date;

And this is what I get when I execute the procedure:

[CRC] SQL> exec AddRental('Clinton', 25, '04/03/2003 14:00', 3.50); BEGIN AddRental('Clinton', 25, '04/03/2003 14:00', 3.50); END;

*
ERROR at line 1:

ORA-01830: date format picture ends before converting entire input string
ORA-06512: at "SCM24.ADDRENTAL", line 13
ORA-06512: at line 1

So, seeking a different way of performing this action I tried removing the to_date() functions with no luck. Here is that code:

create or replace procedure AddRental (

   p_custname IN rental.custname%TYPE,
   p_item_number IN rental.item_number%TYPE,
   p_out_date IN rental.out_date%TYPE,
   p_fee IN rental.fee%TYPE) AS

  /*Defined all params above with type from table rental. Note the exclusion of in_date as this param

     is not necessary...going on the premise that the business won't know when the customer will

     actually return the rental.*/

BEGIN    INSERT INTO RENTAL (rental#, custname, item_number, out_date, due_date, in_date, fee)

   VALUES (RentalSeq.NEXTVAL, p_custname, p_item_number, p_out_date,

      next_business_date(p_out_date), NULL, p_fee);

   DBMS_OUTPUT.PUT_LINE('Transaction occured on: ' || to_char(p_out_date, 'DAY') || ' ' || to_date(p_out_date, 'MM/DD/YYYY'));

END AddRental;

After recompiling the above proc I tried passing the date (in numerous formats, such as the defualt Oralce format, 'DD-MMM-YYYY', as well as attempting a to_date call within the exec statement) for the out_date param with no luck and the same error.

Also, please note that I DO NOT want to store the due_date value as a date & time attribute (I want just the date).

So, any ideas? Received on Fri Jul 25 2003 - 19:11:22 CDT