Technical Blog Post
Abstract
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)
UID
ibm11132563