Oracle Decimal to Teradata integer in TPT script


Oracle Decimal to Teradata integer in TPT script

I am writing a PT script which loads from an oracle source to Teradata.

PROBLEM_COL in Oracle is defined as DECIMAL(10,0). In Teradata PROBLEM_COL is defined as Integer.

The load fails, and all rows are inserted into the error table.

Reviewing the logs I believe I see the problem:

Column Name              Offset   Length Type         

PROBLEM_COL             200      4            INTEGER

I understand that Teradata stores Integers as a 4 byte binary values, hence the length of 4.

Whereas in the SELECT * FROM statement, Oracle brings in this:

Column Name                                        Offset  Length Type         

CAST(PROBLEM_COLASFLOAT(10))    200      8           BINARY_DOUBLE

I’ve tried casting it as NUMBER(10,0), DECIMAL(10), INTEGER, and many variations where I change the number in the brackets. I can’t get the length to be any shorter than six.

To confirm this is the issue, I picked a set of records where PROBLEM_COL is a 4 digit value. I did a TO_CHAR(SUBSTR(PROBLEM_COL),1,4). And the load completed successfully. There are values in PROBLEM_COL much bigger than 4 digits so I can’t use this method.

How am I supposed to handle this case?

Teradata Employee

Re: Oracle Decimal to Teradata integer in TPT script

This is a known issue when moving data from non-Teradata databases to Teradata.

Especially Oracle.

In general, the ODBC operator does not perform data conversions, or data type conversions.

The ODBC operator does not know the layout of the destination (target table).

If necessary, I would recommend the user load to a staging table and then perform an INSERT-SELECT.

One thing to consider is to use TO_CHAR (or some equivalent function) to convert the field to character, and code the schema so the column is VARCHAR, and then let Teradata convert from character to integer.

Oracle can be a challenge in other areas.

An INTEGER in Oracle is really stored as a NUMBER (sometimes NUMBER(38,0)), and moving that data to a Teradata INTEGER is challenging.

But since the operator does not perform conversions, and the operator does not know the layout of the target table, it is up to the user to either make sure the target table can handle the data as it arrives from Oracle, or CAST the fields in the SELECT statement to match the target table (and let the ODBC driver do the conversion).

-- SteveF

Re: Oracle Decimal to Teradata integer in TPT script

Does the TPT documentation or an Orange Book discuss the Oracle ODBC drivers?

Teradata Employee

Re: Oracle Decimal to Teradata integer in TPT script

The documentation states that TPT is only certified with the ODBC drivers from DataDirect.

We bundle the drivers with TPT starting in 15.0 (although the user must obtain license files from Teradata). The license files are free.

-- SteveF

Re: Oracle Decimal to Teradata integer in TPT script

Hi Steve,

There is an impending data migration for an application stack within our organisation.

An upgrade from the current Teradata 14.0 to 15.0 is planned in the final state.

On the current 14.0 software platform data is extracted typically from Oracle sources through TPT using DataDirect drivers.   If possible we would prefer to get rid of the additional cost and maintenance associated with DataDirect.

While your comment indicates that the drivers are bundled with TPT, would you be able to advise:

a. If there is a standard approach to deploy and test TPT without DataDirect on 15.0 ?

b. Is there additional cost associated with the user license you alluded to?

If this is readily available information do point me to the documents and I could take it from there?

Thanks in advance!