Home » SQL & PL/SQL » SQL & PL/SQL » Error message ORA-01487
Error message ORA-01487 [message #230276] Wed, 11 April 2007 07:01 Go to next message
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 Go to previous messageGo to next message
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 #230286 is a reply to message #230276] Wed, 11 April 2007 07:55 Go to previous messageGo to next message
akinia
Messages: 4
Registered: April 2007
Junior Member
Hi,

Thanks for your quick answer. Wink
Could it be the problem ?

Re: Error message ORA-01487 [message #230290 is a reply to message #230276] Wed, 11 April 2007 08:04 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: srw.do_sql ora-01830 date picture ends ...
Next Topic: query format .
Goto Forum:
  


Current Time: Sun Dec 04 14:48:37 CST 2016

Total time taken to generate the page: 0.10143 seconds