Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Roberto.

Asked: October 18, 2004 - 2:39 pm UTC

Last updated: March 15, 2007 - 1:07 pm UTC

Version: 9.2.0

Viewed 1000+ times

You Asked

Hello,
An external company is trying to sell us a product whose database relies on an Oracle database. They try to force us to create a new instance instead of using an existing one, because they want to have an specific default value for NLS_DATE_FORMAT (the company is from UK, we're in Spain, so the default NLS_DATE_FORMAT are different). I've told them that there's ALWAYS a way to override the database default, whether it's with environment variables, registry entries and/or -Dxxxx definitions for java thin clients. I've also argued that date values have an internal representation, completely independent of the "aspect" that a determined client sees. As far as I know, you can only connect to oracle by means of a sqlnet client or with a java client. In both cases, there's a way to override the NLS_DATE_FORMAT from outside the application (before starting it). I don't know whether they don't want to investigate and just be sure that the app will work with the default values without worrying. In short, the question is:
Is there ALWAYS a way to override the database NLS default values, from OUTSIDE a 3rd party application? Thank you very much in advance for your invaluable help.

and Tom said...

absolutely -- the client ALWAYS wins.

In fact -- if the client has ANY NLS_ settings (eg: if the client is a windows client), then the NLS_DATE_FORMAT in the server is -- well -- *ignored*, overridden, meaningless.

see
</code> http://asktom.oracle.com/Misc/NLSDateFormat.html <code>

for example -- people tried this in the year 2000, the server setting is virtually meaningless.

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Helena Marková, October 19, 2004 - 4:03 am UTC


Windows & NLS_ Registry settings.

Cloink, March 08, 2007 - 12:19 pm UTC

Hi Tom,

I'm curious to know why XE (but presumably any standard Ora install) puts an NLS_LANG setting into the Windows registry. As far as I'm aware, I installed only the server, certainly I have no Windows-sqlplus (just the DOS-sqlplus).

My PC is acting as both server and client, connecting via Perl or DOS-sqlplus. Perl picks up the registry setting, as does sqlplus. That's maddening! If this PC was a server proper, the registry would be overriding the database SYSTEM settings.

I want queries to return dates in standard ANSI SQL format (surely a common thing) to any queries exec'd by the Perl scripts; I don't want to have to issue
ALTER SESSION SET NLS_DATE_FORMAT...

every time there's a connection to the db, I'm writing software to go on the web and that would add unnecessary overhead on the server.

I've removed (renamed) the NLS_LANG registry setting. Is this going to cause problems elsewhere? Should I add an NLS_DATE_FORMAT registry entry intead?

Thanks,
Clark.
Tom Kyte
March 08, 2007 - 12:47 pm UTC

the XE install installed the client and the server software. the client software sets that registry setting.


either of what you propose would work.

Setting NLS parameters in WEB applications

Shimon B, March 15, 2007 - 1:01 pm UTC

Hi tom,

If you find my review that it is "actually new question..."(as you mentioned in the fair warning), please accept my appology, and do not bother to read further - only notify me via my email address i've atached, and I will try to submit a new question.

As to the subject matter, in the company where I am assigned now, we have upgraded our application from a C/S model into a Oracle Ias-10g model (Old : Oracle Forms version 5.0 . New : IDS 10g). In some of the application modules there is an explicit use of "alter session" to set the NLS_DATE_FORMAT to a different format('DD-MON-YYYY') than the default('DD-MON-RR'). This worke dfine in the C/S environment, but in the new model, the client is actually the IAS.
my question : How can one override the default nls_date_format, so that it will effect only the current session, since the alter session affects only the direct client - which in this case is not the Forms module.

Thanks in advance.
Shimon B.
Tom Kyte
March 15, 2007 - 1:07 pm UTC

not sure I'm following you - the alter session set nls_date_format should work just the same in web forms as it did in client server.

Shimon Batashvili, March 21, 2007 - 1:20 pm UTC

Hi Tom,

Thanks for the short-time reply.

It apparantly does not behave the same as before, because the application itself has no effect on these settings anymore. I've printed the value of sysdate before setting the format via SRW.DO_SQL('alter session....'), and after the setting, but the value presented has been tha same as it was without changing the format - i.e., "20-MAR-07" (the same as the default format,"dd-mon-rr"), instead of "20-MAR-2007".
The error I get when the report fails is as follows :
"
REP-1401: Invalid date mask. ORA-01843: not a valid month REP-1401: 'cf_charged_duration_41': Fatal PL/SQL error occurred.
"
Thank again,

Shimon B.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.