Compliance with the Year 2000, requires you to look at the following
issues:
Input mask
Systematic use of Oracle DATE format to store date data
Checking Primary keys format
Checking defaut conversion
Input Mask
It's probably the most obvious impact. Dates entered
into the system must include the century information. Corrections can be:
enforcing the user to type the century, or to compute the century using
a windowing function. The Oracle 'RR' format can be used for that. The
windowing function is not important. The important aspect is to centralise
this function so you can change it later.
Oracle Date Format
Any good transactional program should use the Oracle DATE format. It 's
a basic safeguard for minimum compliance. For Oracle Forms III, the DATE
format is not Y2K compliant. Change instead to DATE:TIME format and be
careful to initialise the time to 00h00 to avoid any side effect of that
substitution.
Checking Primary Keys format
This is probably the most vicious and the most problematic aspect of Y2K
compliance. Most programs use a varchar2 primary key to store the 'Sale'
entry. A lot of programs like to prefix the sequence by a date ie: '991200345'
as the 345th entry since the beginning of December 99. It's where all the
problems start: as a primary key you can be sure that you'll sort it somewhere,
let say in a batch job to treat each 'sale ticket' in the order that it
has been inserted into the database. Of course, there is little chance
that the programmer even bothers to add a 'sort by sales_id' because it
is a default order as a primary key. This is what we call default sorting.
You can be sure that this program will have unexpected behaviour when it's
going to deal with sales tickets in the year 2000. To correct this problem
you have to make a difficult choice:
Add the century to the primary key.
This will have a major impact. As a primary key, it is probably part
of foreign keys like 'sales parts'. Not only you can be sure that this
key is pretty much used everywhere (as a primary key or as a part
of a foreign key), you are going to face another problem: changing a primary
key in the database online. This is no fun and it's the first aspect that
you must considere: can you stop the database to :
- drop the primary key which will force you to put the table offline
- change the column format
- correct the values in the table
- create the primary key
- drop all the foreign keys which will force you to those tables put
offline
- change the column format of all the foreign tables (linked directly
and indirectly to the sale table)
- correct the values in the table
- create the foreign keys
If you can't stop the production, you've got to consider your second
option
Correct the program by using windowing function
This is no fun either. You've got to check each select using the 'sales
id' from the sales table or any other table having the 'sales id' column.
For each select, if there's no order by clause, you've got to add one like
'sort by windowing(sales_id)'. Unless of course you are sure that the rest
of the program doesn't care about the sorting (i.e. select count(*) ).
A classical windowing function is: decode(round(year/50),'20','19')
Checking default conversion
A lot of functions (e.g. nvl, last_day ), applied on date value, use default
conversion. The problem is that, default conversion doesn't always carry
the century of the date but uses instead the current century as the reference.
You've got to use explicit conversion using date format ie:(nvl(to_date(xx,'YYYYMMDD)).
Last Update : $Date: Jul 04 2001 22:02:40 $