| Error message ORA-01487 [message #230276] |
Wed, 11 April 2007 07:01  |
akinia
Messages: 4
Registered: April 2007
|
Junior Member |
|
|
Hi,
Can someone help me with this error ORA-01847
"Day of month must be between 1 and the last day of the month"
Here is the trigger's code:
DECLARE
-- Fields variables --
v_PP_DATE DATE;
v_JOUR INTEGER;
BEGIN
LOOP
FETCH emp_cursor into
v_NR,
v_PP_DATE,
EXIT WHEN emp_cursor%NOTFOUND;
v_PP_DATE := TO_DATE(v_PP_DATE, 'DD.MM.YYYY');
v_DAY_NR := TRUNC(TO_DATE (v_PP_DATE, 'DD.MM.YYYY') - TO_DATE('19900101000000', 'YYYYMMDDHH24MISS'));
v_MOIS := TO_CHAR(v_PP_DATE, 'Month', 'NLS_DATE_LANGUAGE = FRENCH');
-- v_JOUR := TO_CHAR(v_PP_DATE, 'DD');
-- v_JOUR := TO_CHAR(TO_DATE(v_PP_DATE, 'DD.MM.YYYY'),'DD');
v_JOUR := TO_CHAR(TRUNC(TO_DATE(v_PP_DATE, 'DD.MM.YYYY'),'DD'),’DD’);
END
Here is the input '08.03.2007' VARCHAR2(10)
The error message is pointing to the line with v_JOUR.
I'm not sure that the problem comes from this line because when I asked the customer to execute the following sql
SELECT TO_CHAR(TO_DATE('08.03.2007', 'DD.MM.YYYY'),'DD');
we get the same result
Below are the NLS parameters.
PARAMETER / CUSTOMER VALUE / MY PC VALUE
--------------------------- ------------------------------ -------------------------
NLS_LANGUAGE / ENGLISH / FRENCH
NLS_TERRITORY / UNITED KINGDOM / BELGIUM
NLS_CURRENCY / £ / Euro (symbol)
NLS_ISO_CURRENCY / UNITED KINGDOM / BELGIUM
NLS_NUMERIC_CHARACTERS / ., / ,.
NLS_CALENDAR / GREGORIAN / GREGORIAN
NLS_DATE_FORMAT / DD-Mon-RRRR / DD/MM/RRRR
NLS_DATE_LANGUAGE / ENGLISH / FRENCH
NLS_CHARACTERSET / WE8ISO8859P1 / WE8MSWIN1252
NLS_SORT / BINARY / FRENCH (symbol)
NLS_TIME_FORMAT / HH24.MI.SSXFF / HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT / DD-MON-RR HH24.MI.SSXFF / DD/MM/RR HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT / HH24.MI.SSXFF TZR / HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT / DD-MON-RR HH24.MI.SSXFF TZR / DD/MM/RR HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY / ¿ / Euro
NLS_NCHAR_CHARACTERSET / AL16UTF16 / AL16UTF16
NLS_COMP / BINARY / BINARY
NLS_LENGTH_SEMANTICS / BYTE / BYTE
NLS_NCHAR_CONV_EXCP / FALSE /FALSE
Any help will be appreciated.
Thanks in advance.
|
|
|
|
| Re: Error message ORA-01487 [message #230283 is a reply to message #230276] |
Wed, 11 April 2007 07:31  |
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
|
Senior Member |
|
|
Why do you have:
v_PP_DATE := TO_DATE(v_PP_DATE, 'DD.MM.YYYY');
when v_pp_date is already declared as a DATE type variable ? You should only apply to_date to character strings.
|
|
|
|
|
|
| Re: Error message ORA-01487 [message #230290 is a reply to message #230276] |
Wed, 11 April 2007 08:04 |
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
|
Senior Member |
|
|
|
It is certainly a problem. You should check that the datatype of the column which feeds v_pp_date from your cursor is of type date. If it isn't, you should change the declaration of v_pp_date to match it. If it is, then you should remove the to_dates, as they are not required. If it is really a character string field in the table, you will have to check that it always contains a date string in the correct format.
|
|
|
|
| Re: Error message ORA-01487 [message #230294 is a reply to message #230276] |
Wed, 11 April 2007 08:19 |
akinia
Messages: 4
Registered: April 2007
|
Junior Member |
|
|
The column is a caracter string field; it always contains this king of format: '08.03.2007'.
If I run the trigger on my machine the row is inserted;
The same one doesn't work at the customer.
Is the difference between the NLS_DATE FORMAT really a problem ?
I thought I should give the format of the given data in order to inform about this difference.
|
|
|
|
| Re: Error message ORA-01487 [message #230299 is a reply to message #230276] |
Wed, 11 April 2007 08:24 |
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
|
Senior Member |
|
|
|
You are retrieving it into a DATE field so, unless the characater string happens to match the NLS_DATE_FORMAT, Oracle will not be able to convert it and you will get an error. The code is very shoddy, as you should not be declaring a variable as a type DATE and then retrieving a character string column into it. You have only yourselves to blame for the problem.
|
|
|
|
| Re: Error message ORA-01487 [message #230309 is a reply to message #230276] |
Wed, 11 April 2007 08:54 |
akinia
Messages: 4
Registered: April 2007
|
Junior Member |
|
|
Thanks for your advice.
I'm really a newbie in PL_SQL; I've begon last month to learn by myself and have to maintain a trigger, created by someone who left the company, which didn't exactly do what the customer wanted.
Thus I've changed it and as it worked fine on my machine didn't pay attention to that.
I let you know when the problem is solved.
Thanks again
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
