Why you need to worry about NLS_DATE_FORMAT !


Posted by Hafed | Posted in Forms, oracle | Posted on 18-08-2008

One of those things that can irritate you for more than you can care of: the mix up in dates. That’s an old story for those dealing with the Oracle Db but in our case, that was something that bothered us for more than a week before we found out that we were missing the environment variable NLS_DATE_FORMAT in our report server setup.

In our old C/S forms 6i setup, we used to have 2 ways of getting reports out.  Either Preview or Printer with a submission form that has all the report parameters.  In most cases, we needed a START DATE and an END DATE . Those were always supposed to be in the format DD/MM/YYYY since we use FRENCH_CANADIAN as our NLS_LANG. In the add_parameters statement, a to_date with the proper format was systematically used. However, in the report module, the receiving parameter was setup as CHARACTER with length 10. That was working fine in the C/S version. Remember that each user could have his own report on his own machine. The registry in this case had a value setup for NLS_DATE_FORMAT and NLS_LANG.

On 10g, this was giving us a TERMINATED_WITH_ERROR message each time the report was called. One way to fix the error was to set the user parameter for the dates as DATE with the proper format (DD/MM/YYYY).

But that was calling for a big change since we had a high number of submission screens.  Looking at the environment variables for Oracle reports  10g release 2, it was obvious that we needed to have both NLS_LANG and NLS_DATE_FORMAT setup properly.

For the time being, we changed NLS_LANG to AMERICAN_AMERICA.WE8MSWIN1252 and NLS_DATE_FORMAT to DD/MM/YYYY on the report server and that fixed our problems. However, it is more than likely that NLS_LANG will be changed to FRENCH_CANADIAN.WE8MSWIN1252 to make it compatible with our other setup.

Note: When checking the server environment (with showenv at the end of the url), NLS_DATE_FORMAT does not show up in the list.

Similar Posts:

Comments (3)

i think so …..maybe this is a part of de problem… but i can not fix my problem with

by the way thanks.

exists %ORACLE_DEv%\REPORTS\CONF\reportservicename.conf and add the path where are the aplication\module\report.rep

and the file file.env and add REPORT_PATH= unit:\aplication\module1;unit:\aplication\module2 etc etc.

maybe then fix the problem.

Write a comment