Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Incorrect conversion of Date (data type) to TimeStamp (data type) while reading from Oracle DB

avatar
Expert Contributor

Hello,

We are trying to read data from Oracle tables, "Date" based data types are converted into "Timestamp" Data types.

e.g: Table is Oracle.

desc hr.employees;

Name Null? Type

----------------------------------------- -------- ----------------------------

EMPLOYEE_ID NOT NULL NUMBER(6)

FIRST_NAME VARCHAR2(20)

LAST_NAME NOT NULL VARCHAR2(25)

EMAIL NOT NULL VARCHAR2(25)

PHONE_NUMBER VARCHAR2(20)

HIRE_DATE NOT NULL DATE

JOB_ID NOT NULL VARCHAR2(10)

SALARY NUMBER(8,2)

COMMISSION_PCT NUMBER(2,2)

MANAGER_ID NUMBER(6)

DEPARTMENT_ID NUMBER(4)

SSN VARCHAR2(55)

and schema read in the DataFrame in Scala

|-- EMPLOYEE_ID: decimal(6,0) (nullable = false)

|-- FIRST_NAME: string (nullable = true)

|-- LAST_NAME: string (nullable = false)

|-- EMAIL: string (nullable = false)

|-- PHONE_NUMBER: string (nullable = true)

|-- HIRE_DATE: timestamp (nullable = false) (Incorrect data type read here)

|-- JOB_ID: string (nullable = false)

|-- SALARY: decimal(8,2) (nullable = true)

|-- COMMISSION_PCT: decimal(2,2) (nullable = true)

|-- MANAGER_ID: decimal(6,0) (nullable = true)

|-- DEPARTMENT_ID: decimal(4,0) (nullable = true)

|-- SSN: string (nullable = true)

Hire_Date is read incorrectly as TimeStamp, is there a way to correct.

Data is being read from Oracle on the fly and the application does not have an upfront knowledge of datatypes and can't convert it after being read.

Thanks in advance.

Nagesh

1 ACCEPTED SOLUTION

avatar

Analysis: As per oracle -

Oracle Database 8i and earlier versions did not support TIMESTAMP data, but Oracle DATE data used to have a time component as an extension to the SQL standard. So, Oracle Database 8i and earlier versions of JDBC drivers mapped oracle.sql.DATE to java.sql.Timestamp to preserve the time component. Starting with Oracle Database 9.0.1, TIMESTAMP support was included and 9i JDBC drivers started mapping oracle.sql.DATE to java.sql.Date. This mapping was incorrect as it truncated the time component of Oracle DATE data. To overcome this problem, Oracle Database 11.1 introduced a new flag mapDateToTimestamp. The default value of this flag is true, which means that by default the drivers will correctly map oracle.sql.DATE to java.sql.Timestamp, retaining the time information. If you still want the incorrect but 10g compatible oracle.sql.DATE to java.sql.Date mapping, then you can get it by setting the value of mapDateToTimestamp flag to false.

Ref link is here.

Solution:

  1. So as instructed by oracle provide property jdbc.oracle.mapDateToTimestamp as false -
    Class.forName("oracle.jdbc.driver.OracleDriver")var info : java.util.Properties=new java.util.Properties()
      info.put("user", user)
      info.put("password", password)
      info.put("oracle.jdbc.mapDateToTimestamp","false")val jdbcDF = spark.read.jdbc(jdbcURL, tableFullName, info)
  2. Add Oracle database connector jar which supports "oracle.jdbc.mapDateToTimestamp" flag is ojdbc14.jar

Hope it helps!

View solution in original post

1 REPLY 1

avatar

Analysis: As per oracle -

Oracle Database 8i and earlier versions did not support TIMESTAMP data, but Oracle DATE data used to have a time component as an extension to the SQL standard. So, Oracle Database 8i and earlier versions of JDBC drivers mapped oracle.sql.DATE to java.sql.Timestamp to preserve the time component. Starting with Oracle Database 9.0.1, TIMESTAMP support was included and 9i JDBC drivers started mapping oracle.sql.DATE to java.sql.Date. This mapping was incorrect as it truncated the time component of Oracle DATE data. To overcome this problem, Oracle Database 11.1 introduced a new flag mapDateToTimestamp. The default value of this flag is true, which means that by default the drivers will correctly map oracle.sql.DATE to java.sql.Timestamp, retaining the time information. If you still want the incorrect but 10g compatible oracle.sql.DATE to java.sql.Date mapping, then you can get it by setting the value of mapDateToTimestamp flag to false.

Ref link is here.

Solution:

  1. So as instructed by oracle provide property jdbc.oracle.mapDateToTimestamp as false -
    Class.forName("oracle.jdbc.driver.OracleDriver")var info : java.util.Properties=new java.util.Properties()
      info.put("user", user)
      info.put("password", password)
      info.put("oracle.jdbc.mapDateToTimestamp","false")val jdbcDF = spark.read.jdbc(jdbcURL, tableFullName, info)
  2. Add Oracle database connector jar which supports "oracle.jdbc.mapDateToTimestamp" flag is ojdbc14.jar

Hope it helps!