MAXIMO 7 on Oracle 11 allows invalid date year to be stored in the database - solved

Body

We encountered an issue in MAXIMO 7.5 with the Oracle 11 database, where system allowed user to store 5 digit years into database. Such invalid date values in the database with the year over 9999 caused errors when reading data or interfacing them to other systems.

We found similar issue already reported, but no permanent solution was available.

http://www-01.ibm.com/support/docview.wss?uid=swg1IV36705

IV36705: MAXIMO DATE FIELDS (ALLOW > 4 LENGTH YEAR ENTRY)

There are several ways to reproduce this issue in the system:

·        Enter PM with 2 million days frequency

·        Setup  PM at 10,000 km where Asset Meter average goes to 0.01 km per day

·        Setup Customer Agreement in SP and enter 2 million months duration

Upon further investigation we discovered that this issue was due to an Oracle bug, which skips date year validation, if date is stored through JBDC bind variable as Date object.

http://download.oracle.com/otn/utilities_drivers/jdbc/112/Readme.txt

Oracle JDBC Drivers release 11.2.0.1.0 production Readme.txt      

BUG-7271519 Out of range Oracle date datatypes could be inserted into the database.                                                           

BUG-7249052 It was possible to construct invalid Oracle DATE values.            

BUG-7243157 Raise an appropriate error when attempting to use a date with year exceeding 9999.  

SOLUTION

MAXIMO 7.5 is delivered with Oracle JDBC driver 11.1.0.7.0, and fix for this issue is provided in the Oracle JDBC driver 11.2.0.1.0 and higher up to 11.2.0.4.0.

In order to fix this issue, you will need to replace “oraclethin.jar” in the MAXIMO folder. First download the latest JDBC driver from Oracle website and then rename it to “oraclethin.jar” and the copy it to “maximo\applications\maximo\lib“.

http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-112010-090769.html

Full details on how to reproduce this bug on Oracle 11g are provided below.

Step 1: create table TEST_DATE (TEST_DATE_COLUMN DATE);

Step 2: Create this JDBC class and compile it.

package test;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.Date;

public class JdbcTest

{

  public static void main(String[] args)

  {

    try

    {

      Connection conn = DriverManager.getConnection(args[0],args[1],args[2]);

      System.out.println("JDBC driver version is " + conn.getMetaData().getDriverVersion());

      System.out.println("Prepare statement");

      PreparedStatement ps = conn.prepareStatement("insert into TEST_DATE(TEST_DATE_COLUMN) values (:1)");

      Date dDate = new Date(12015,1,1); // Invalid Oracle Date

      ps.setDate(1, dDate);

      System.out.println("Execute statement");

      ps.execute();

      conn.commit();

      System.out.println("Commit");

    }

    catch (Exception e)

    {

      e.printStackTrace();

    }

  }

}

Step 3: Run this class with the current MAXIMO application driver

C:\workspace\test\bin>java -cp .;C:\maximo\applications\maximo\lib\oraclethin.jar test.JdbcTest jdbc:oracle:thin:@localhost:1521:jham maximo maximo

JDBC driver version is 11.1.0.7.0-Production

Prepare statement

Execute statement

Commit

Step 4: Run select from the table and you get an error

select TO_DATE(TEST_DATE_COLUMN) from TEST_DATE;

ORA-01847: day of month must be between 1 and last day of month

01847. 00000 -  "day of month must be between 1 and last day of month"

*Cause:   

*Action:

Step 5: Run the same class with the ORACLE JDBC5 standard driver

C:\workspace\test\bin>java -cp .;C:\app\Administrator\product\11.2.0\dbhome_1\jdbc\lib\ojdbc5.jar test.JdbcTest jdbc:oracle:thin:@localhost:1521:jham maximo maximo

JDBC driver version is 11.2.0.3.0

Prepare statement

java.lang.IllegalArgumentException: Invalid year value

        at oracle.sql.DATE.toBytes(DATE.java:654)

        at oracle.sql.DATE.<init>(DATE.java:131)

        at oracle.jdbc.driver.OraclePreparedStatement.setDate(OraclePreparedStatement.java:7215)

        at oracle.jdbc.driver.OraclePreparedStatementWrapper.setDate(OraclePreparedStatementWrapper.java:170)

        at test.JdbcTest.main(JdbcTest.java:20)