Re: Miracle Y2K problem among Oracle, NT, and Win95

A copy of this was sent to "Wilfred Lau" <wilfred_at_flash.net> (if that email address didn't require changing) On Thu, 24 Dec 1998 21:52:10 GMT, you wrote:

the init.ora NLS_DATE_FORMAT parameter is not being used by clients that have overridden some NLS defaults (your NT box). The sun and win/95 client are using the DD-MON-RR format since the have NOT specified an NLS lang but the win/nt machine undoubtably has (using regedit and searching for NLS_LANG will confirm this).

The issue is that if a CLIENT has set ANY NLS_* parameter, they all default based on the client (eg: the init.ora defaults are defaults only for clients who specify no NLS_* parameters on their side).

On UNIX, the default character set for a database is US7ASCII. On Windows, the default client characterset is WE8ISO8859P1. the client install on Windows inserts the value NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1 into your registry. Since the client (windows nt) has specified some NLS_* settings, the defaults for the client for ALL NLS_* parameters are based on the character they specified -- meaning in short -- since the client specified some NLS_* parameters, the defaults for that client are based on the NLS character set they are using, not the defaults from the database.

How to fix:

1. update the registry to have the NLS_DATE format as well as the NLS_LANG it already has.
2. upon application startup, issue "alter session set nls_date_format='....'". This is as opposed to 3.) below. also, consider putting triggers on tables that have dates in them that simply verify that the nls_date_format is set correctly and raise_applicatoin_error if not -- this will prevent an application that does not have this startup code from destroying your data. If you use pl/sql to do lots of stuff, you can typically avoid modifying the actual application on the client by modifying frequently used package bodies. You can put elaboration code in the pl/sql package body that will be executed once per session when the package is first referenced. For example, you could take some package that looks like:

create or replace package body my_package as

   ... global variables ...
   ... procedures and functions ...

begin

   dbms_session.set_nls_date_format( .... ); end;
/

The code between the begin/end block at the end of the package will run once upon the package being used.

3. explicity use to_date() on all character strings, to_char() on all date strings. don't rely on nls_date_formats

>Hi:
>
>PLEASE HELP!!! PLEASE HELP!!!
>
>First of all, neither I am not an Oracle nor a database person, but I am
>working on a Y2K compliant test for the department with an Oracle server for
>NT. An ambiguous issue came up from the test. The value of DATE data shows
>difference among the inputs from different workstations.
>
>1) Update the record as '01-JAN-00' locally via SQL*Plus 3.3.2.0.2 from the
>Testing server running with NTS 4.0 w/SP4 and Oracle server 7.3.2 The date
>shows 01-JAN-1900 after I use (alter session set
>NLS_DATE_FORMAT='DD-MON-YYYY') command.
>
>2) Update the same table via the same version SQL*PLUS on Windows 95 machine
>through LAN, the date shows 01-JAN-2000.
>
>3) Update the same table via the SQL*PLUS 7.3.3 on a SUN workstation through
>LAN, the date also shows 01-JAN-2000.
>
>4) create a table with only one DATE date type field on the production
>server which the testing server mirrors from. Insert the value '01-JAN-00'
>into the field. The date shows '01-JAN-1900' after I change the
>NLS_DATE_FORMAT.
>
>Once I found the returned date shows 1900, our DBA changed the parameter to
>NLS_DATE_FORMAT = 'DD-MON-RR' in an initxxx.ora file (I believe it is a
>initializaton file). The instance shows the parameter is changed to 'RR',
>but the database parameter still shows 'YY'. Since I don't know how Oracle
>works, but by using 'RR' parameter, the date should shows 2000 if I input
>'00' in year 1998.
>
>I made some assumptions and conditions about this test. (Please correct me
>if I am wrong)
>1) The testing machine is a Compaq Proliant 1600R with P2-450 processor, the
>latest BIOS dated 8/17/98. This is a Y2K compliant system on hardware
>level.
>2) By applying NT4 SP4, the NT machine should be Y2K complaint up to OS
>level. (I assume)
>3) Oracle 7.3 server and SQL*Plus 3.3 are also Y2K compliant.
>4) I know that our Windows 95 machine does not have Y2K patch installed yet.
>Therefore, it should not be Y2K compliant. (I assume)
>
>For the above results with the set of assumptions and conditions, both the
>DBA and myself have not idea about it, However, we both guess that the
>Windows NT may not be Y2K compliant.
>
>Today is Christmas Eve, I am still trying to figure out this problem. If
>anyone have some suggestions, please email to me.
>My email address is wlau_at_enron.com
>
>Thanks and Merry Christmas
>
>Wilfred
>
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation
 
Anti-Anti Spam Msg: if you want an answer emailed to you, 
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.