Re: Y2k Server Testing, What's in a date?

Changing the NLS data format is not enough:

(1) what if all your forms have date display widths of 8, and now you want to display 10 or 11?
(2) what if some stupid developer did not use the date format but lets say number, or varchar(2) for storing dates (3) what if parts of a date are stored in a number or varchar(2) format field: year number(2)?
(3) what if calculations are made on dates using to_char(..) with 2-digit year formatting?

     example: period_between := to_char(date1,'DD-MM-YY') - to_char(date2,'DD-MM-YY')

Where do you look for this potential problems? Is exploring the designer/2000 repository with scripts using the repository api enough? Do we generate 100%?
We scanned for potential problems in
a. the designer/2000 repositories
b. the pl/sql database objects (packages, procedures, functions, views, triggers)
c. forms (.fmb), reports (.rdf), libraries (.pll), menus (.mmb)

We scanned (using a in house developed scanning tool) about 3 or 4 million objects (=records or lines of code), and found almost no problems. Some display widths will have to be increased to 10 or 11 and two reports will have to be corrected.

The major problem we encountered was the following: lots of our oracle applications receive or send data to legacy systems. This legacy systems were not all y2k compliant (will be soon). So the oracle appication adjusts for this discompliance.
Example: one application receives orders with orderdates from a legacy system. The legacy system puts the data in an ascii file with the order date having a format of yymmdd. This ascii file is imported in a oracle table with a varchar2(6) column for the order date. The table contents are then processed by the oracle application. A part of the table processing is adjusting the order date: when the first two digits form a number < 50, '20' is put before the date and then converted to the oracle date format. When the number is >= 50 '19 is put before it. You can imagine what will happen when the legacy system at some point will start sending a order date having a format of yyyymmdd! The oracle application will no longer work correctly.
So we are currently analysing all of our legacy systems / oracle systems interfaces.

Hope this helps you!
Mike Oswald heeft geschreven in bericht <642T1.6$c47.43873_at_newsread.com>...
>With all of the Y2K testing planned here at work and the fact that someone
>has determined all the DBA's need to do is change the NLS date format...
>
>What is everyone else doing? How involved have you, as a DBA, or has your
>DBA's been in the Y2K process?
>
>Is it simply a NLS data format change? It seems to me... there is more to
>it.
>
>Please forward any suggestions... websites... comments on what I should be
>thinking about. We do have Developer & Designer 2000 in house... but they
>are not the core business tools. Oracle7 is primarily the backend database
>but we do have snapshots and some replication occurring.
>
>Thanks!
>
>-Mike
>
>
>
Received on Fri Oct 09 1998 - 07:10:16 CDT