Viele Oracle Kunden werden den Jahrtausend Wechsel auf dem sehr stabilen Release 7.3.4.4 vornehmen, obwohl Oracle-8 bereits seit langen in einer stabilen Version zur Verfügung steht.

Oracle hat kurz vor Weihnachten ein Patch-Release 7.3.4.5 herausgegben, der neben anderen Bugs, drei Y2K Fehler behebt, welche im Release 7.3.4.4 sind. Dies hat bei einigen Kunden verständlicherweise für Aufregung gesorgt. Wir haben die drei Y2K Bugs näher angeschaut um eine Empfehlung ob ein Wechsel auf 7.3.4.5 noch vor dem Wechsel vorgenommen werden muss oder ob zugewartet werden kann. Es sei gleich vorweggenommen, die verbleibenden Y2K Bugs sind einerseits in Teilbereichen, die kaum eingesetzt werden und andererseits mit einem korrekten Umgang mit PL/SQL nicht von Bedeutung.

Bug 726481

When you set the init.ora parameter FIXED_DATE = "<date>" then PL/SQL TO_DATE() conversions do not pick up the correct century for date conversions. This can impact Y2K testing. SQL does pick up the <Parameter:FIXED_DATE> correctly. Cautionary note: The fix for this is a behavior change. To revert to the old (incorrect) behavior you should contact Oracle Support.

Empfehlung:

Benutzen Sie FIXED_DATE nicht im INIT.ORA, immer mit einer expliziten 4-stelligen TO_DATE Konversion arbeiten.

Bug 778615

2 digit years in CATEXP.SQL can means that Cumulative Exports may not include the correct tables for the first cumulative export in the year 2000. If you use cumulative exports then the to avoid this problem ensure that the first export taken in the year 2000 is of INCTYPE COMPLETE rather than CUMULATIVE. Normal FULL exports are NOT affected by this problem.

Empfehlung:

Cumulative Exports für die Datensicherung werden kaum von jemandem eingesetzt. Man kann davon ausgehen, dass man ein ordentliches Online-Backup (Hot-Backup) Konzept anwendet.

Bug 873722

When a date stored in a DATE column with full year information (YYYY) is selected using an NLS_DATE_FORMAT with either 'RR' or 'YY' year format it is possible to get an incorrect (zero) century if implicit type conversions occur. Eg: PLSQL: for r in (select nvl(varchar_col, date_col) col from tab ) LOOP dbms_output.put_line('Date is ',to_char(r.col,'DD-MON-YYYY')); END LOOP Can end up showing dates as 'DD-MON-00XX' NOTE: Application code must
already be exposed to Y2K issues to hit this bug . See <NOTE:73925.1> on Metalink.

NOTE:73925.1 von Metalink

Introduction
~~~~~~~~~~~~
This is an overview of Bug:873722 which is a problem which can return an incorrect date century value in unusual circumstances. This problem is highly unlikely to affect your applications but the
description here is intended to explain the problem in sufficient detail that the effects of the bug can be assessed.

Description of the Problem
~~~~~~~~~~~~~~~~~~~~~~~~~~
In very specific circumstances systems using an NLS_DATE_FORMAT containing a 2 digit year mask can return an incorrect year in the first century.

NOTE:

To see this problem an implicit date conversion must occur - implicit date conversions can be very risky when using a 2 digit year mask as dates can correctly appear to 'move' from one century to another.

The Problem in more detail
~~~~~~~~~~~~~~~~~~~~~~~~~~
This problem is characterized by implicit date conversions on date  members accessed directly from a cursor variables when using a 2-digit year format mask. This scenario is only rarely encountered in real-life implementations and is not an issue when using a 4-digit year format mask or when not relying on implicit date conversion.

There are 2 main scenarios where this bug can arise:

a. In a PLSQL block in Oracle8 (not Oracle7) it is possible for
   an implicit date conversion to result in a DATE variable being
   corrupted

b. In a client/server scenario fetching a character variable into
   a DATE host variable can result in the DATE host variable being
   corrupted.

The first of these is the simplest to describe...

PLSQL form (Oracle8 Only)
~~~~~~~~~~~~~~~~~~~~~~~~~
The following code demonstrates the problem in PLSQL:

REM Prepare demo table

drop table tab;
create table tab( varchar_col varchar2(20), date_col date);
insert into tab values(null,to_date('01-JAN-1940','DD-MON-YYYY'));
insert into tab values(null,to_date('01-JAN-1970','DD-MON-YYYY'));
insert into tab values(null,to_date('01-JAN-2001','DD-MON-YYYY'));
commit;

REM Demonstrate the problem

set serverout on
alter session set NLS_DATE_FORMAT='DD-MON-YY';
begin
for r in (select nvl(varchar_col, date_col) col from tab)
loop
  dbms_output.put_line('Date is '||to_char(r.col,'DD-MON-YYYY'));
end loop;
end;
/

Running the above example in an Oracle8 release where the problem
exists gives the following incorrect output:

Date is 01-JAN-0040
Date is 01-JAN-0070
Date is 01-JAN-0001

Note that even with the bug fixed the implicit date conversion which
occurs above can still result in 'corrupt' output. With the bug fixed and NLS_DATE_FORMAT='DD-MON-YY' then the correct results are:

Date is 01-JAN-1940
Date is 01-JAN-1970
Date is 01-JAN-1901  << Incorrect century due to use of YY mask

Workaround
~~~~~~~~~~
The workaround, and indeed the correct way to code the above PLSQL block, is to use an explicit date conversion in the PLSQL block which uses a YYYY or RRRR date format mask.

Eg: Change the SELECT to:

(select to_date(nvl(varchar_col,
        to_char(date_col,'DD-MON-YYYY')),'DD-MON-YYYY') col
   from tab)

Such that there is no implicit date conversion which can occur.

Pro*C form
~~~~~~~~~~
This form of the problem affects Oracle7 and Oracle8. In the code sample below a VARCHAR2 is FETCHED into a DATE host variable. When this happens the year appears in the FIRST century which is incorrect.

#include <sqlca.h>
#include <stdio.h>

typedef char krm_date[7];  /* ORACLE date */
typedef char krm_text;     /* unknown length string */

int main(int argc, char ** argv)
{
  exec sql type krm_date is date;
  exec sql type krm_text is string;
  exec sql declare db database;
  exec sql begin declare section;
  char *uid = "scott/tiger";
  krm_date mydate;
  krm_text stmtbuf[1000];
  krm_text nls[1000];
  krm_text output[100];
  exec sql end declare section;
  strcpy(nls, "alter session set nls_date_format='DD-MON-YY HH24:MI'");
  strcpy(stmtbuf, "select '14-APR-99 17:21' from sys.dual");
  printf("Statement to execute: %s\n", stmtbuf);
  exec sql at db connect :uid;
  exec sql at DB EXECUTE IMMEDIATE :nls;
  EXEC SQL AT db PREPARE krmkstd_query from :stmtbuf;
  EXEC SQL AT db DECLARE c1 CURSOR FOR krmkstd_query;
  EXEC SQL AT db OPEN c1;
  EXEC SQL AT db FETCH c1 into :mydate;
  EXEC SQL AT db CLOSE c1;
  EXEC SQL AT db SELECT TO_CHAR(:mydate, 'DD-MON-YYYY HH24:MI:SS')
                   INTO :output FROM dual;
  printf("Translated date: %s\n", output);
  return 0;
}
Running this results in the output:

Statement to execute: select '14-APR-99 17:21' from sys.dual
Translated date: 14-APR-0099 17:21:00

Workaround
~~~~~~~~~~
As in the previous example the workaround is to ensure that a full
year mask is used for the date conversion. This is best achieved
using an explicit TO_DATE operator in the SELECT statement.