Options in MariaDB:
> connect_work_size=2
Bizarre, fortunately CONNECT takes a minimum value (This is actually the work memory size)
The variable giving the size of TEXT column is connect_conv_size but should not ne 2 anyhow.

> connect_type_conv=1
can also be given the value 'skip' (2) and the table will be created by discovery without the "wrong" column.

> optimizer_switch='engine_condition_pushdown=on'
Useless. This is now the default for MariaDB.

Let's come back to your problem.

This is very strange, type 12 is defined as VARCHAR and VARCHAR2 does not exist neither in sql.h nor in sqlext.h. Because the CONNECT type translate function works from the type number, not from its name, it should accept it (12 is defined as SQL_VARCHAR)

The problem is that I have no such table to debug this case. Can you provide one?

Sorry, I have many tables with VARCHAR2 columns, for instance:

create table countries_col

ENGINE=CONNECT CONNECTION='DSN=ORACLE_TEST;UID=system;DB=HR;PWD=manager' TABLE_TYPE=ODBC TABNAME=COUNTRIES CATFUNC=COL;

select * from countries_col;

Now I create the table:

create table countries

ENGINE=CONNECT CONNECTION='DSN=ORACLE_TEST;UID=system;PWD=manager' TABLE_TYPE=ODBC TABNAME='HR.COUNTRIES';

It was Ok, then:

select * from countries;

Everything Ok.
I am running Windows 7. My ORACLE ODBC driver is:
Oracle in XE version 11.02.00.02 Oracle Corporation SQORA32.DLL 02/11/2013
My data source ORACLE_TESTis defined as:
Description: Test Oracle
UserID: SYSTEM
Enable Result Sets
Enable Query Timeout
Enable Thread Safety
Commit only if all statement succeed
Use US settings
Fetch Buffer Size: 64000
Enable LOBs
Cache Buffer Size: 20
Enable Failover
Retry: 10
Delay: 10
Disable Microsoft Transaction Server
Disable RULE Hint

Hi,
I'm with the company Joffrey was trying this out on. I've replicated what I believe to be the issue. Destination instance is MariaDB 10.0.17-MariaDB-log source is Oracle 10.2.0.5.0. I've setup connect engines in a manner similar to how I believe Joffrey setup his system. I'm trying to bring in the ACCOUNTS tables into a empty database called remote_Daybreak.

Accounts setup in Oracle:

ColumnName,ColID,PK,IndexPos,Null,DataType,Default,Histogram,NumDistinct,NumNulls,Density,EncryptionAlg,Salt,Trigger,Virtual
ACC_AAD_ID,1,,5, 3, 2, 2, 1, 3,N,NUMBER,,Height Balanced,1113943,0,0,,,,
ACC_SAC_ID,2,,,N,NUMBER,0,None,1,0,1,,,,
ACC_PTC_COMPANY,3,,,N,VARCHAR2 (30 Byte),'UNDEFINED',Frequency,2,0,0,,,,
ACC_PCB_BRANCH_ORG,4,,,N,VARCHAR2 (30 Byte),'UNDEFINED',None,1,0,1,,,,
ACC_PCB_BRANCH,5,,,N,VARCHAR2 (30 Byte),'UNDEFINED',Frequency,3,0,0,,,,
ACC_APP_DT,6,,,N,DATE,SYSDATE,None,3770,0,0.00027,,,,
ACC_APP_NBR,7,,,N,VARCHAR2 (30 Byte),'UNDEFINED',None,1113943,0,0,,,,
ACC_APP_PURPOSE_CD,8,,,Y,VARCHAR2 (30 Byte),,None,0,1113959,0,,,,
ACC_APP_SOURCE_CD,9,,,N,VARCHAR2 (30 Byte),'UNDEFINED',None,1,0,1,,,,
ACC_APP_STATUS_CD,10,,,N,VARCHAR2 (30 Byte),'NEW',None,1,0,1,,,,
ACC_APP_SUB_STATUS_CD,11,,,N,VARCHAR2 (30 Byte),'UNDEFINED',None,1,0,1,,,,
ACC_APP_STATUS_CHG_DT_LAST,12,,,N,DATE,TO_DATE('01/01/1800',
'MM/DD/YYYY'),None,1,0,1,,,,
ACC_NBR,13,,1, 2,N,VARCHAR2 (30 Byte),'UNDEFINED',Height Balanced,1113943,0,0,,,,
+400ish lines. Can send the full thing if desired.

In MariaDB going in as the root user. Trying to create table.
[root@wfslxvddbcompare1 ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.0.17-MariaDB-log MariaDB Server

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use remote_Daybreak;
Database changed
MariaDB [remote_Daybreak]> create table accounts ENGINE=CONNECT TABLE_TYPE=ODBC tabname='ACCOUNTS' CONNECTION='DSN=ODBC;UID=daybreak;PWD=daybreak';
ERROR 1939 (HY000): Engine CONNECT failed to discover table `remote_Daybreak`.`accounts` with 'CREATE TABLE whatever (`ACC_AAD_ID` DOUBLE(22,0) NOT NULL,`ACC_SAC_ID` DOUBLE(22,0) NOT NULL,`ACC_PTC_COMPANY` VARCHAR(30) NOT NULL,`ACC_PCB_BRANCH_ORG` VARCHAR(30) NOT NULL,`ACC_PCB_BRANCH` VARCHAR(30) NOT NULL,`ACC_APP_DT` TIMESTAMP NOT NULL,`ACC_APP_NBR` VARCHAR(30) NOT NULL,`ACC_APP_PURPOSE_CD` VARCHAR(30),`ACC_APP_SOURCE_CD` VARCHAR(30) NOT NULL,`ACC_APP_STATUS_CD` VARCHAR(30) NOT NULL,`ACC_APP_SUB_STATUS_CD` VARCHAR(30) NOT NU
MariaDB [remote_Daybreak]> show errors;
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

3 rows in set (0.01 sec)
In /etc/odbinst.ini i had setup trace level logging.
{{[ODBC]
Trace = Yes
TraceFile = /tmp/odbc.log
ForceTrace = Yes
Pooling = No
DEBUG = 1}}
Here is the output for that:

[ODBC][28547][1428949088.341420][__handles.c][460]
Exit:[SQL_SUCCESS]
Environment = 0x7f334eaeea00
[ODBC][28547][1428949088.341569][SQLAllocHandle.c][375]
Entry:
Handle Type = 2
Input Handle = 0x7f334eaeea00
[ODBC][28547][1428949088.341647][SQLAllocHandle.c][493]
Exit:[SQL_SUCCESS]
Output Handle = 0x7f334eb78000
[ODBC][28547][1428949088.341701][SQLSetConnectOption.c][345]
Entry:
Connection = 0x7f334eb78000
Option = SQL_ATTR_ACCESS_MODE
Value = 1
[ODBC][28547][1428949088.341740][SQLSetConnectOption.c][508]
Exit:[SQL_SUCCESS]
+5k ish more lines

I know the error here is complaining about a duplicate column ("| Error | 1060 | Duplicate column name 'ACC_AAD_ID'"). But when I look at the definitions I got from oracle there seems to only be one column in this table.

I thought it could be a general setup issue but I was able to setup a smaller table (only 3 columns & 4 rows) in our environment. and it appears to pull data correctly.

CRH

A possible cause for "duplicate column name" is when your data source contains several tables with the same name in different databases (schema)
You can check this with a catalog table, for instance:

create table accounts ENGINE=CONNECT TABLE_TYPE=ODBC tabname='ACCOUNTS' CONNECTION='DSN=ODBC;UID=daybreak;PWD=daybreak' catfunc=table;  /* or catfunc=col */

If this is the case, specify the schema name of your table with the option dbname:

create table accounts ENGINE=CONNECT TABLE_TYPE=ODBC tabname='ACCOUNTS' DBNAME='schema_name' CONNECTION='DSN=ODBC;UID=daybreak;PWD=daybreak';