My Oracle Support Banner

ODI Knowledge Modules Generate Incorrect Lengths for Teradata TIMESTAMP Datatype Columns (Doc ID 849974.1)

Last updated on JUNE 16, 2022

Applies to:

Oracle Data Integrator - Version 10.1.3.2.0 and later
Information in this document applies to any platform.
***Checked for ODI 11g compliance on 22-Nov-2012***


Symptoms

The Oracle Data Integrator (ODI) "LKM SQL to Teradata (TTU)" Knowledge Module generates a datatype of:

  • TIMESTAMP(3) -- in the "Create work table" step, but a
  • TIMESTAMP(26) -- in the "Create view on work table" step which is incorrect.

The Teradata view is created from the LKM "Create View on work table" step, as follows:

create view <DATABASE_SCHEMA>.C$_0<TABLE2>
as select
cast(cast(C1_COLUMN as VARCHAR(46)) as timestamp(26)) C1_COLUMN,
...
from <DATABASE_SCHEMA>.C$_0<TABLE1>

The above code fails and following message is shown:

5401 : HY000 : com.teradata.jdbc.jdbc_4.util.JDBCException: [Teradata Database] [TeraJDBC13.00.00.06]
[Error 5401] [SQLState HY000] Number of fractional digits out of range.

For example, this may happen when:

  • Loading Microsoft SQLServer DATETIME field...
  • Into Teradata TIMESTAMP(3) FORMAT 'YYYYMMDDHHMISS.S(3)' NOT NULL target...

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.