ORA-01830: date format picture problem with a Procedure

[Steveo99]

Hello:

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

Am I wrong in the proc call, or the procedure body?

 

[HereistheAnswer]

pleae send the table structure of table 'RENTAL'

 

[sem]

Check whether rental.out_date%TYPE is date and if so, remove conversion to_date(p_out_date, 'MM/DD/YYYY hh24:mi') and all other similar statements, because to_date(DATE) implicitly converts parameter to string according your nls settings and then converts result string to date. The result string is obviously of NLS_DATE_FORMAT format, thus no other format mask may be applied.

Regards, Dima

 

[Steveo99]

HereIsTheAnswer:

RENTAL# NUMBER
CUSTNAME VARCHAR2(15)
ITEM_NUMBER NUMBER(4)
OUT_DATE DATE
DUE_DATE DATE
IN_DATE DATE
FEE NUMBER(6,2)

sem: I tried the proc below with and without the session altered, without luck:

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?

 

[SantaMufasa]

Steve, I include, below, a cleaned-up, working copy of your code. Here were the problems/issues:

1) Given your call to AddRental,
"exec AddRental('Clinton', 25, '04/03/2003 14:00', 3.50)",
the third argument, '04/03/2003 14:00', is a character string. Your procedure definition declares it to be the same type as "rental.out_date", which is a DATE column. Therefore, to cause the least impact on the rest of your code, change the argument definition to an explicit "varchar2".

2) Your procedure invocation, "exec AddRental('Clinton', 25, '04/03/2003 14:00', 3.50)", includes the time in the date string; yet in the NEXT_BUSINESS_DATE invocation in the INSERT statement, your DATE format mask does not include the time. This becomes a reason for the error:
"ORA-01830: date format picture ends before converting entire input string".

3) In your DBMS_OUTPUT...invocation the date-related stuff is just generally goobered up: a) When you say,
"...to_char(p_out_date, 'DAY')...", p_out_date is already a character string and cannot appear in a date-conversion usage of the to_char function. b) When you say,
"to_date(p_out_date, 'MM/DD/YYYY')", again, your format mask must include "hh24:mi" to account for the time that appears in p_out_date; this, too, accounts for the error, "ORA-01830: date format picture ends before converting entire input string". If actually consolidate both of the date-related invocations in the corrected code below.

Corrected, working code (If you copy, paste, and execute in SQL*Plus, you should be able to just run your "exec" with success) :
create or replace procedure AddRental (
p_custname IN rental.custname%TYPE,
p_item_number IN rental.item_number%TYPE,
/*
p_out_date IN varchar2,
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); */
next_business_date(to_date (p_out_date, 'MM/DD/YYYY hh24:mi')), NULL, p_fee); -- corrected line
/* DBMS_OUTPUT.PUT_LINE('Transaction occured on: ' || to_char(p_out_date, 'DAY') || ' ' || to_date(p_out_date, 'MM/DD/YYYY')); */
DBMS_OUTPUT.PUT_LINE('Transaction occured on: ' || to_char(to_date(p_out_date,'MM/DD/YYYY hh24:mi'),'DAY MM/DD/YYYY')); -- corrected line
END AddRental;
/

Let me know.

Dave Hunt

 

[Steveo99]

Dave:

It worked, thanks!

Steve