We need to upgrade our Oracle database from 9i to 10g. We've performed the upgrade on the test box and are in the process of checking it for issues.

One thing that's popped up so far is the error: "Oracle ORA-01855: AM/A.M. or PM/P.M." for queries with the to_timestamp function.

I've checked the NLS settings and they appear to be the same between our 9i and 10g configurations except for a small difference in the parm = NLS_NUMERIC_CHARACTERS. I'm hoping to find a settings change to fix this rather than updating a large number of SQL statements. Attached is a sample query and the NLS settings for both.NLS-parameters-comparison.xlsSample-SQL.txt

Your problem is the format string and the date. They do not match. There has been tightening of the rules around these things for many versions now.
If you look, your string has a : between the seconds

It means that the data does not have the full information to decipher whether it is AM or PM. You might need to convert the data to make it to timestamp format or truncate the data so that extra (mili seconds) are rounded off to seconds.

Looking at the sample query file I posted (which has been working in 9i for several years), am I correct that you're saying the SQL must be modified in some way for 10g to figure out what AM/PM is? Why would 9i know but not 10g?

Featured Post

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…

This post first appeared at Oracleinaction (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself).
I will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…

Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…