 |
|
ORA-01840: input value not long enough for date format tips
Oracle Error Tips by Burleson Consulting (S. Karam)
|
The Oracle docs note this on the
ora-01840 error:
- ORA-01840
input value not long enough for date format
- Cause:
The data to be converted to date format was incomplete; the date format
picture was longer than the input data.
-
- Action:
Either add more input or shorten the date picture format, then retry the
operation.
On
Oracle MOSC, ORA-01810 was being exemplified as being thrown, "When
Creating a Window or Schedule with DBMS_SCHEDULER" while using Oracle Enterprise
Edition - Version: 10.1.0.2 and can happen on any platform.
Symptoms
You are attempting to create a window or a schedule using the package
DBMS_SCHEDULER, such as in the following example:
begin
dbms_scheduler.create_schedule (
schedule_name => 'nightly_schedule',
start_date => '01-JAN-2004 01:00:00',
end_date => '01-JAN-2005 01:00:00',
repeat_interval => 'FREQ=DAILY; INTERVAL=1',
comments => 'Important Comments Here');
end;
/
However, it fails with the following stack:
ORA-01840: input value not long enough for date format
ORA-06512: at line 2
You check the NLS_DATE_FORMAT, and it is, indeed, set to DD-MON-YYYY
HH24:MI:SS.
Cause
DBMS_SCHEDULER requires the hours in a 12 hour, AM/PM model.
Fix
Change the code to use a 12 hour format instead of 24 hour. This does not
require a change in the NLS_DATE_FORMAT:
begin
dbms_scheduler.create_schedule (
schedule_name => 'nightly_schedule',
start_date => '01-JAN-2004 01:00:00AM', <--- Note the 'AM' Here
end_date => '01-JAN-2005 01:00:00AM', <-- Note the 'AM' Here
repeat_interval => 'FREQ=DAILY; INTERVAL=1',
comments => 'Important Comments Here');
end;
/
It is important to note that MOSC finishes this information with this
statement: "This is true for all date strings passed to dbms_scheduler
for create schedule, window, or job. Even if your date format is, say,
DD-MON-RR, you still need to use the format above."
