We had to import an sql script delivered by a US based company in an Oracle 11g database. Everything went fine except for to_timestamp function that had letter code month values. All to_timestamps with MAR for March failed. As not being a dba I searched around bit and found out that the database we’re importing in should be set to American language and American Territory. Quite logic actually.
But it didn’t help.
In both sqlPlus and SQLDeveloper the ‘invalid month’ ORA-01843 kept popping up. You would expect the clients to take the settings from the database, but it does not.
Solution add a NLS_LANG and NLS_TERRITORY to your environment settings. Both SQLDeveloper and SQLPlus will take that parameter and apply it to any script you execute. In our case since the script came from US we had to put NLS_LANG=”American” and NLS_TERRITORY=”American Territory”.
Just a note, even when not setting the NLS parameters, we tried the to_timestamp with the Dutch version of ‘mar’ but that didn’t work too. Maybe the locale for Dutch isn’t installed. As it is quite easy with Oracle 11g to create your own locale perhaps Oracle expect us to do that?