I have the research data I need on the data warehouse of a hospital. I need to transfer some of it into a research database server I have access to at the same hospital. The Oracle server runs Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit on an IBM/AIX RISC SYSTEM/6000 server. The MySQL server runs  MySQL  5.0.15-nt-max-log on Windows 2003 Server on a Intel Pentium D dual core machine.

I migrate data from Oracle to MySQL using Talend Open Studio 2.3.1 running on a separate Windows XP Professional machine. 

On Oracle, I have a column with datatype NUMBER(22,0). I have the equivalent column defined on MySQL as DECIMAL(22,0). Now, the Oracle field stores numbers with a decimal point, such as 12345.6789. However, when this data is moved to MySQL, it becomes 12345.

Oracle documentation says that NUMBER(22,0) denotes a numeric datatype with a precision of 22 (total number of digits) and a scale of 5 (number of digits to the right of the decimal point). MySQL documentations says that DECIMAL(22,0) denotes the same.

However, Oracle does store numbers with a decimal point in this field, whereas MySQL doesn't. This causes so much headaches when you transfer several millions of rows from one database to another.

If I redefine the column in MySQL as DECIMAL(22,10) instead of DECIMAL(22,0), another strange behaviour happens. MySQL then stores 12345 as 12345.0000000000. This is not what I expect. This is harmless, but it doesn't look elegant.

 Is there anyone who knows what is going on, and can help me with a solution? I will also post this on some mailing lists to see what happens.

By the way, here's another "gotcha" when you transfer data from Oracle to MySQL if you have date datatypes. Oracle's DATE datatype can store date, time and datetime. However, MySQL DATE will store only DATE data, it will truncate the time part from it. So, for predictable and safe results, use the DATETIME datatype on MySQL.

---