ORA-01861 in procedure after upgrade from ora9i to ora10g 1

[delayne]

I have a procedure that compiles and runs in Oracle 9i, but encounters an error when run in Oracle 10gR2. It is failing on an INSERT statement, but I cannot figure out what is causing the problem. All datatypes are correct.

Has anyone else experienced this type of issue during your upgrade from 9i to 10g? Any ideas why it's happy in 9i but not 10g?

TIA

 

[SantaMufasa]

Delayne,

Your particular error message ("ORA-01861") is not an error that necessarily relates to your 9i-to-10g upgrade. That error typically occurs when you have a character string that you are attempting to reformat into another expression, and the character string you are attempting to convert does not match the "format mask" you specified for the conversion. For example:

SELECT to_date('20081308','yyyy/mm/dd') from dual
               *
ERROR at line 1:
ORA-01861: literal does not match format string

The above error results from a combination of bad data ("13th month") and Oracle's inability to match your string format with the mask format.

If you cannot locate the error in your code, we'd be happy to help if you are willing to post the offending code here.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[LKBrwnDBA]

The most common mistake that produces this error is trying to compare a date column with a string like this:

SELECT * FROM MyTable
 WHERE SomeDate = '01/04/2008';

and the NLS_DATE_FORMAT parameter does not coincide with the formated date presented.

Therefore it is BEST practice to convert any string values with TO_DATE() before comparing to a date column.





----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

LK,

So that I can duplicate Delayne's error scenario, given your scenario, could you help me understand what I am doing incorrectly?

Here is code that I just tried:

SQL> select * from NLS_INSTANCE_PARAMETERS
  2  where parameter like '%DATE%';

PARAMETER                      VALUE
------------------------------ -----------
NLS_DATE_LANGUAGE
NLS_DATE_FORMAT

select last_name, start_date from s_emp where start_date = '03/03/1990'
                                                           *
ERROR at line 1:
ORA-01843: not a valid month

So, I'm receiving error ORA-01843 instead of ORA-01861.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[delayne]

Turns out there was a difference in a column format between the 2 instances (truly "DUH"). It was a date field that was the culprit (which we strongly suspected).

Thanks for your willingness to help - you guys are the best!!