One Star

Double datatype moving data from MySQL to Oracle

I am in the process of migrating data from MySQL 5.1 to Oracle 9i.
Most of the floating point datatypes are of type DOUBLE in MySQL. Obviously Talend has no trouble reading the table metadata with the DOUBLE datatype and moving the data of DOUBLE datatype through the Java engine.
But, Oracle 9i does not have a "DOUBLE" datatype. The type is correctly called "DOUBLE PRECISION".
The problem is when my dataflow looks like this:
tMySQLInput --> tMap --> tOracleOutput

Talend does not automatically convert the MySQL "DOUBLE" datatype to an Oracle "DOUBLE PRECISION" datatype. It is writing the CREATE TABLE as "CREATE TABLE col1 DOUBLE..." which is invalid Oracle syntax.
I can manually change each DOUBLE to DOUBLE PRECISION in the tOracleOutput, but I would prefer an automated solution.
Is there a way to force Talend to use the correct Oracle syntax automatically?
3 REPLIES
Community Manager

Re: Double datatype moving data from MySQL to Oracle

Hello
Which version of TOS are you using? I checked that it has the correct data type mapping in TOS3.0.3. If you want to change the data type mapping, you can go to Windows-->Preference-->Talend-->Metadata of TalendType-->mapping_Oracle.xml and change it, eg:
<talendType type="id_Double">
<dbType type="DOUBLE PRECISION" default="true"/>
<dbType type="BINARY_DOUBLE"/>
</talendType>

Best regards

shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Double datatype moving data from MySQL to Oracle

Which version of TOS are you using?

Version: 3.1.0M1; Build id: r22164-20090226-0025
I checked that it has the correct data type mapping in TOS3.0.3. If you want to change the data type mapping, you can go to Windows-->Preference-->Talend-->Metadata of TalendType-->mapping_Oracle.xml and change it, eg:
<talendType type="id_Double">
<dbType type="DOUBLE PRECISION" default="true"/>
<dbType type="BINARY_DOUBLE"/>
</talendType>


This is what my mapping_Oracle.xml looks like:
				<talendType type="id_Double">
<dbType type="DOUBLE PRECISION" default="true"/>
<dbType type="BINARY_DOUBLE"/>
</talendType>

But (as illustrated above) when, using row connectors, I connect components as follows: tMysqlInput -> tMap -> tOracleOutput Talend is inheriting the datatypes in the target from the source. A "DOUBLE" datatype coming out of MySQL is inherited by the tMap and subsequently inherited by the tOracleOutput.
I am having the same issue with the MySQL DATETIME datatype. DATETIME is ultimately inherited by tOracleOutput, which is not a valid Oracle datatype. I am forced to manually change both DOUBLE and DATETIME to appropriate Oracle datatypes.
One Star

Re: Double datatype moving data from MySQL to Oracle

Bump this, I am having the same problem.
Although my data types are correctly mapped in the XML files, when I try a simple transfert from a MySQL Input to a Oracle Output, the Oracle Output component inherits the Data Types from MySQL. (In my case, it tries creating the Oracle table with TINYINT...
Here an image of my problem:

And here is the XML code from the file mapping_oracle.xml:
<talendType type="id_Short">
<dbType type="INT" default="true"/>
</talendType>

As you see the short metadata is correctly mapped to INT in the XML file, but Talend tries to create an oracle table with TINYINT nonetheless.