Oracle Year 2000 Pitfalls

By Ben Lis

Practical Techniques for Addressing the Y2K Problem in Oracle - Based Applications

When we system professionals think about Year 2000 (Y2K) issues, a stereotypical problem system tends to come to mind: programs that were originally constructed in the '60s or '70s and coded in Cobol using an ISAM or VSAM database. The designers used a two-digit year in order to save precious space; they never expected the system to be in service at the turn of the century.

At this point, the developers responsible for maintaining our stereotypical problem system are fixing their Y2K problems or at least have a plan for addressing them. Unfortunately for us, many modern systems that don't fit our stereotype are afflicted with Y2K problems. We've been so inundated with warnings about Year 2000 issues in trade journal articles and vendor advertisements that it's difficult to remember that very few people were seriously considering Y2K issues before 1995. Were you thinking about them in 1993? I wasn't; I was just thinking about how to meet the next project milestone.

Thousands of Oracle-based applications in production at the turn of the century will have been developed before the industry became Y2K-aware. The purpose of this article is to review two key Y2K issues for Oracle-based applications: the default date format and dates stored in number or character columns. My goal is to provide a "heads-up" to the folks who are responsible for maintaining such applications. Although this article focuses on Oracle, you shouldn't take this to mean that you are off the hook if your applications run on another DBMS! Any application that stores dates in number or character columns may have Y2K issues regardless of the DBMS it uses.

Default Date Format

Before discussing the potential issues surrounding the default date format, we must review the characteristics of Oracle's date datatype. Oracle columns of the date data-type can store dates ranging from January 1, 4712 BC to December 31, 4712 AD. Oracle uses its own internal format for storing dates, but conceptually you can think of the server as storing each date in the format YYYY-MM-DDHH24:MI:SS. Because all dates occurring in a column of datatype date are stored with a four-digit century, Oracle regards the Oracle Server as Y2K compliant.

However, the situation gets less rosy when we take into consideration the default date format. Since Oracle uses its own internal representation for dates, it must convert from this format to a readable format on output and vice versa on input. Oracle provides a wide range of date formats that the programmer can explicitly request or specify by using the to_char() and to_date() functions, respectively. However, if you don't use these functions (for example, you just reference the date as a string as in "select * from emp where hiredate > '01-JAN-97'"), Oracle relies on the default date format to perform the conversion. There is a great deal of Oracle SQL code in use that relies on the default date format -- approximately three quarters of the Oracle SQL code that I've seen or coded in the last 10 years does.

The default date format for Oracle releases in North America has always been DD-MON-YY. Here's the potential pitfall: The YY always refers to years in the current century. Let's look at an example of how the use of this format could cause trouble for the classic order-entry system in the fourth quarter of 1999. For the sake of clear explanation, I will consider a simplified version of the order table:

CREATE TABLE orders (
id number(10,0)
constraint pk_orders_id
primary key,
customer number(5,0)
constraint
fk_orders_customers
references customers,
order_date date not null,
delivery_date date not null,
pmt_terms number(2,0)
constraint
fk_orders_pmt_terms
references pmt_terms,
amount_due number(12,2));

Let's further assume that the customer specifies the delivery date and that the order taker enters the information into the database via some sort of graphical interface, which generates this insert statement:

INSERT into orders
(id, customer, order_date,
delivery_date, pmt_terms,
amount_due)
values
(orders_id_seq.nextval, 1000,
'09-SEP-97', '15-NOV-97', 2,
1200.00);

Now let's assume that a customer places an order in November 1999 and wants to take delivery of the items ordered in January 2000. The order taker enters this information via the interface and the following insert statement gets generated:

INSERT into orders 
(id, customer, order_date,
delivery_date, pmt_terms,
amount_due)
values
(orders_id_seq.nextval, 1000,
'15-NOV-99', '03-JAN-00', 2,
1800.00);

If the shipping department ships orders based on a report that shows all orders that have a delivery date greater than the current date, this order will never will ship. Why? Well, the information in the database indicates that this order was scheduled to be delivered over 99 years ago. (See Listing 1.)

It turns out that you can fix this problem without resorting to any code modifications. First, you need to learn how to change the default date format on your server. Second, you need to find a default date format that behaves reasonably at the turn of the century.

Changing the Default Date Format

The capability to alter the server's default date format is available from release 7.0 onward. The default date format can be altered at both the session and instance (or server) level. Modifying the format at the instance level causes the default to be changed for all the sessions connecting to the database -- a powerful option. Since the default date format is used by Oracle only on input (values clause of insert, set clause of update, or where clause of select) or output (date column not explicitly in to_char referenced in select statement), changing the value of the default date format has no impact on date data already stored in the database.

At the session level the default date format is changed by executing the alter session command. Here's an example:

SQL> ALTER SESSION 
2 SET NLS_DATE_FORMAT = 
'MM/DD/YYYY'; 
Session altered.
SQL> 
SQL> SELECT sysdate from dual;
SYSDATE
----------
09/21/1997

At the instance level, the default date format is changed by modifying the parameter nls_date_format in the initialization file (init.ora). Here's an example:

NLS_DATE_FORMAT = "MM/DD/YYYY"

The value of this parameter can be set to any valid date format mask. This sort of change is made by the DBA. You may not even find an entry for this parameter in your initialization file. In fact, the default initialization file that Oracle ships with the product does not set this parameter. If this value is not explicitly set in the initialization file, the server derives its value from the parameter nls_territory. Several other parameters dealing with such settings as currency and decimal symbols are also derived from this parameter. For Oracle sites in the United States, nls_territory is set to america.

Note to Windows Users

If your server runs Windows NT or your client runs Windows 3.1 or Windows 95 and you've been typing in the examples while reading this, you're probably scratching your head trying to figure out why the examples are not working. Well, the answer is that you have a little more work to do.

On the server side, the parameter nls_date_format also has to be changed in the Windows NT registry. The steps involved in doing this are documented in the Oracle Corporate Support Problem Repository Soln# 2065837.6. Essentially, you must add a new key under named nls_date_format under hkey_local_machine, software, oracle. This key should have a string value of DD-MON-YYYY or any valid date mask.

The same change also needs to be made on the client. The registry program under Windows 95 is a little different, so the sequence of steps is slightly different, but the basic idea is the same. Finally, if you are still running 16-bit clients, you must enter the value for nls_date_format in the oracle.ini file.

DD-MON-RR to the Rescue

Now that you know how to change the default format on your server, let's focus our attention on finding a different suitable date format. Our first instinct might be to go with a four-digit representation for year like DD-MON-YYYY. Although this will certainly address the problem, it will also require us to modify our existing programs to support the two extra digits. Think of all the reports that will need to be reformatted.

We need a format that displays using the same format as DD-MON-YY so that no existing programs will have to be modified. But our format needs to behave more reasonably with dates that it encounters in where clauses and insert and update statements. We want it to interpret '01-JAN-01' as January 1, 2001 and not January 1, 1901. Fortunately, Oracle has provided a format, DD-MON-RR, that does just this. Under this format, Oracle interprets any date with a year (RR) between 50 and 99 as occurring in the 20th century and any date with a year between 00 and 49 as occurring in the 21st century. OK, I know that technically the year 2000 is not in the 21st century but in the 20th, but as you guessed, under this format, 01-JAN-00 is interpreted as January 1, 2000. You might have also guessed that the interpretation of DD-MON-RR will change on January 1, 2051. Don't worry about it though, by then we will all be off living the good life in retirement communities in Florida -- when the calls from the headhunters start.

Let's revisit our order-entry problem to see if DD-MON-RR resolves the problem. Assume that our DBA has set the instance's default date format to DD-MON-RR by adding the definition of nls_date_format to the initialization file and restarting the database. We then re-enter our second order:

INSERT into orders 
(id, customer,
order_date,
delivery_date,
pmt_terms,
amount_due)
values 
(orders_id_seq.nextval, 1000, '15-NOV-99', '03-JAN-00', 2, 1800);

If you rerun the shipping department report, you'll see that your problems have been eliminated.

As soon as the record with 03-JAN-1900 delivery date has been deleted, you can declare victory and move on to your next Y2K problem. Before doing that though, we should consider if there is any downside risk to switching from a default date format of 'DD-MON-YY' to a default date format of 'DD-MON-RR.' This change could cause problems if you are actively querying or updating tables that contain date columns from the first half of this century and are actively making use of the default date format. A great example of this is the birthdate column in our employee table. What happens after we change over to the 'DD-MON-RR' format and a new employee joins our company who was born on '11-DEC-49'. If we are using the default date format, we just shaved 100 years off the age of our new hire! There may be date columns like this in your applications.

Given that there is such little downside risk from making this change, it's surprising to find that the initial release of Oracle8 is still shipping with a default date format of DD-MON-YY. Regardless, the bottom line is that you should give serious consideration to changing your default date format to DD-MON-RR.

Dates Stored in Number or Character Columns

Columns with a datatype of number or varchar2 and that contain date data are another rich area for potential Y2K issues.

Please don't be too quick to dismiss the notion that any of the applications you are responsible for store dates in columns with datatypes other than date. Even the Oracle data dictionary contains an example: the column all_objects.timestamp is defined as varchar2(75). Who knows why? Unless you really know your schemas inside and out, you should conduct some sort of systematic review. One way to start is to run this SQL*Plus script, suspects.sql, as shown in Listing 2.

This script searches through all the columns you have access to for columns that are not of datatype date and are not owned by SYS (this excludes Oracle's data dictionary) but that do have names that indicate they may contain date data. For example, if schemas you have access to contain columns with names like study_yr_mon or expiration_date, this query will alert you to the fact.

To demonstrate how storing date data in number or character fields can be the source of a Y2K problem, let's assume that the previous query returned the column drug_studies_history.study_yr_mon. Furthermore, the following DDL was used to create the drug_studies_history table:

CREATE TABLE drug_studies_history
(study_id varchar2(8) not null,
study_yr_mon number(4) not
null,
doseage number(5,2)
.
.
.
);

Querying the study_yr_mon column shows that the column contains dates in the format YYMM:

1 SELECT study_yr_mon FROM drug_studies_history
2* WHERE study_id = 'ABC'
SQL> /
STUDY_YR_MON
------------
9908
9909
9910

Now suppose that, in the year 2000, someone runs a report that contains the following SQL statement:

SELECT study_yr_mon, doseage from drug_studies_history 
WHERE study_id = 'ABC' and study_yr_mon > 9901;

One thing for sure is that the report won't return any dose data rows with study_yr_mon like 0001, 0002, and 0003. Unfortunately, this problem won't be as easy to fix as the problem with our order entry system; the most reasonable approach is to either widen the column to support a four-digit year or convert over to a date datatype. This implies not only changing the underlying table, but also modifying most if not all of the code that accesses this column. Of course, if we were convinced that the column study_yr_mon is never referenced in a range predicate (such as >, <, =, between) or an order by clause, we might consider not modifying the table.

I feel compelled to emphasize that this is not a contrived example. It is loosely based on a real example that I dealt with recently. The real table was originally built by a group of very bright developers about 10 years ago. Like just about everybody else in the world at that time, they weren't giving any thought to Y2K issues.

Script Enhancements

Here are a few hints to get more mileage out of the suspects.sql script given at the beginning of this section. One that you've probably already thought of is including domain-specific terms in your search for nondate columns that contain date data. For example, if your database deals with bonds, you may want to expand the where clause to include columns containing terms like maturity or expire in their names because there is a high probability that they contain date data. The expanded where clause would look something like this:

WHERE ((column_name like '%DATE%' 
or column_name like %TIME%' or
column_name like '%MON%' or
column_name like '%YR%' or
column_name like '%YEAR%' or
column_name like '%DAY%') or
(column_name like '%MATURITY%'
or column_name like '%ISSUE%'
or column_name like '%EXPIRE%' or 
column_name like '%FIRSTCPN%' or column_name like
%FIRSTCALL%')) and 
data_type != 'DATE' and owner
!= 'SYS'

If you've already run the script, you may have noticed that suspects.sql often returns a high percentage of false positives; that is, columns that don't contain date data. If the number of columns returned by suspects.sql is high, checking each column becomes relatively tedious. Under this circumstance, the script suspects_detail.sql (see Listing 3) might be more useful.

First, just as in suspects.sql, we find the columns that look suspicious based on their names. However, instead of simply displaying the column name, we generate a SQL statement that selects 10 non-null values from the column. All the generated SQL statements get spooled to a file called query_suspects.sql, which we then execute. This script displays some actual data from the suspicious column, allowing us to make a quicker assessment of whether or not a column is a potential problem.

Of course, there is no guarantee that, even with the domain-specific terms included, these scripts will catch every occurrence of a nondate column in which date data is stored. However, it will probably give you a lead or two. Maybe it will at least allow you to sleep a little more easily at night.

Act Now

The default date format and dates stored in number or character columns are two key Y2K issues for Oracle-based applications. Following through on these two issues will put your Oracle-based applications on the road to Y2K compliance. However, it will not get you to the final destination. There is no way you can reasonably assume that your applications are compliant without some sort of detailed inspection of your source code and a fair amount of testing -- two topics that I didn't even touch on in this article. As long as you complement your analysis of front-end application code with a thorough examination of your back-end data-base server, you stand a good chance of surviving the year 2000 unscathed.

Listing 1. Where Y2K Poses Problems.

SQL> column order_date format a12 SQL> column delivery_date format a12 SQL> SQL> SELECT id, customer, 2 to_char(order_date, 'DD-MON-YYYY') order_date, 3 to_char(delivery_date, 'DD-MON-YYYY') delivery_date, 4 pmt_terms, amount_due 5 from orders; ID CUSTOMER ORDER_DATE DELIVERY_DAT PMT_TERMS AMOUNT_DUE _________ ________ ___________ ____________ _________ __________ 2022 1000 09-SEP-1997 15-NOV-1997 2 1200 2023 1000 15-NOV-1999 03-JAN-1900 2 1800

COLUMN table_name format a20
COLUMN column_name format a20
COLUMN owner format a15
SET pagesize 800
break on owner on table_name 
spool candidates.lst
SELECT owner, table_name, column_name, data_type
FROM all_tab_columns
WHERE (column_name like '%DATE%' or column_name like '%TIME%' or
column_name like '%MON%' or
column_name like '%YR%' or column_name like '%YEAR%' or
column_name like '%DAY%') and
data_type != 'DATE' and owner != 'SYS'
ORDER BY owner, table_name, column_id
/
spool off

SET heading off
SET feedback off
SET pagesize 800
SET linesize 256
SET echo off
BREAK on owner on table_name 
SPOOL query_suspects.sql
SELECT 'select ' || column_name || ' FROM ' || owner || '.' ||
table_name || ' WHERE rownum < 10 and '||
column_name || ' is not null;' 
FROM all_tab_columns
WHERE (column_name like '%DATE%' or column_name like '%TIME%' or
column_name like '%MON%' or
column_name like '%YR%' or column_name like '%YEAR%' or
column_name like '%DAY%') and
data_type != 'DATE' and owner != 'SYS'
order by owner, table_name, column_id
/
SPOOL off
SET echo on
SET heading on 
SET feedback on
SPOOL suspect_details.lst
@query_suspects.sql
SPOOL off

Ben Lis is an independent consultant in the New York City area. He has 10 years' experience working with databases in both the pharmaceutical and finance industries. You can email Ben at blis@nojc.com.

DBMS and Internet Systems (http://www.dbmsmag.com/index.shtml)
Copyright © 1998 Miller Freeman, Inc. ALL RIGHTS RESERVED
Redistribution without permission is prohibited.