Bug #73943 wbcopytables utility error for Oracle Number data type
Submitted: 17 Sep 2014 5:55 Modified: 12 Feb 2016 9:46
Reporter: Vinod Sugur Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Workbench: Migration Severity:S1 (Critical)
Version:6.1.7 OS:Windows
Assigned to: CPU Architecture:Any
Tags: wbcopytables

[17 Sep 2014 5:55] Vinod Sugur
Description:
We are  not able to migrate data using wbcopytables, our source is oracle   which has column ID column of type NUMBER(19), we are mapping this column to  MySQL  BigInt type.

We get following error during  execution.

BEGIN:test.test_v:Copying 3 columns of 1 rows from table nomis.test_v
ERROR:test.test_v:Type mismatch fetching field 1 (should be string, was MYSQL_TYPE_LONGLONG)

How to repeat:
Create following table on Oracle.

SQL> create table test_v (id number(10,0), name varchar2(100), dt date);

SQL> insert into test_v values(101010101,'vinod',sysdate);

Create following table on MySQL.

'CREATE TABLE `test_v` (
  `id` bigint(20) DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  `dt` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1';

Execute below command:

wbcopytables  --odbc-source="DSN=ORACLEXE;UID=test;PWD=test123" --target=test@localhost:3306 --target-password="nomis" --table test test_v test test_v "id,name,dt"
[17 Sep 2014 5:59] Vinod Sugur
Corrected Oracle Create table statement below

create table test_v (id number(19), name varchar2(100), dt date);
[17 Sep 2014 17:32] Milosz Bodzek
Hi,

Thank you for your report. 
Maybe casting resolve your issue? For instance:
wbcopytables  --odbc-source="DSN=ORACLEXE;UID=test;PWD=test123" --target=test@localhost:3306 --target-password="nomis" --table test test_v test test_v "CAST(id as VARCHAR2(20)) as id,name,dt"
[17 Sep 2014 18:38] Alfredo Kojima
If cast as varchar doens't solve it, try it as some other numeric/integer type.
[18 Sep 2014 6:04] Vinod Sugur
No, it doesn't work, it only works if data type in MySQL is changed to decimal.

Please take this up as bug and let us know the ETA.
[23 Jan 2017 10:54] Martynas Jusevicius
Why is that a won't fix?

We are having the same issue: "Type mismatch fetching field 1 (should be string, was MYSQL_TYPE_LONG)" when using wbcopytables to copy from Oracle.

The source column type is NUMBER(38,0) and the target column type is int(11).