Forum OpenACS Development: oracle date issue help

Collapse
Posted by Ivan Lazarte on
This actually is less of an ACS question, but more AOLserver.

Our setup includes Oracle 9i, and Aolserver 3.4.2. At some point our dates did the infamous switch from YYYY-MM-DD to DD-MON-YY which of course breaks more than a few places. After some searching the Oracle user appears to have the following set in DB host box (It is unclear if these lines are new; the responsible team isn't sure):

NLS_LANG=american_america.utf8
NLS_DATE_FORMAT=YYYY-MM-DD

I know you are supposed to comment out the lang line like so:
# NLS_LANG=american
# export NLS_LANG

but if you add the format line should it be ok?

part 2: after a change on the db host, what is required to reflect the change. just restart the server app? box level?

any help is appreciated. thanks!

Collapse
2: Re: oracle date issue help (response to 1)
Posted by Janine Ohmer on
I'm not sure why you would want to comment out NLS_LANG; I don't think having it set will hurt anything, as long as it is set accurately.

To fix the date, you'll need to do one of two things.

You can set it from within Oracle, which is the best way to do it. Previous to 9i this was easy to do; just add it to the end of the init.ora file. However if your 9i instance is using an spfile instead of an init.ora file, which is the default, you can't do that anymore. I know values like this can be set via OEM, which we don't use, and I was too rushed at the time I did our Oracle upgrade to take the time to figure out how to do that without OEM.

So I used option two, which is to have them set in the environment for the user that runs AOLserver. In my case I set both NLS_LANG and NLS_DATE_FORMAT in the Daemontools run script for each site that needs it. You probably also have a wrapper script in $AOLSERVER/bin to set other Oracle-related environment variables and you could set it there as well.

After putting these in the right file, just restarting AOLserver should do the trick.