I've configured an Oracle 11.2 database with dg4odbc connectivity to one of our MySQL databases, however, when doing a CTAS operation, I see the varchar fields are triple in size in Oracle.
Yes, I could cast the fields in the view, but that would potentially be a maintenance headache.
I had to set Charset=utf8, in the /etc/odbc.ini file or else Oracle implicitly casted to nvarchar. This caused issues when doing a select... case... statement, as I got "ORA-12704: character set mismatch" errors.
Just wondering if someone can point me in the right direction of getting the table on Oracle to be created with the same varchar field length.
In MySQL:
mysql (sparx)>show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
mysql (sparx)>create show table for mytable
| CREATE TABLE ...
...
...
) ENGINE=InnoDB DEFAULT CHARSET=utf8
In Oracle:
$ cat /etc/odbc.ini
[MYSQL_MYDB]
Driver=/opt/mysql-connector-odbc/P/lib/libmyodbc5.so
DATABASE=mydb
DESCRIPTION=MySQL ODBC 5.1.5 Connector Sample
Server=myserver.com
Port=3306
user=myuser
password=mypassword
Database=mydb
Option=0
Trace=off
Charset=utf8
$ cat $ORACLE_HOME/hs/admin/initMYSQL_MYDB.ora
HS_FDS_CONNECT_INFO=MYSQL_MYDB
HS_FDS_TRACE_LEVEL=0
HS_FDS_SHAREABLE_NAME=/opt/mysql-connector-odbc/P/lib/libmyodbc5.so
HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P15
HS_FDS_PROC_IS_FUNC = TRUE
HS_FDS_RESULTSET_SUPPORT = TRUE
HS_NLS_NCHAR=UTF8
HS_KEEP_REMOTE_COLUMN_SIZE=LOCAL
set ODBCINI=/etc/odbc.ini
set LD_LIBRARY_PATH=/opt/mysql-connector-odbc/P/lib:/usr/lib64:/u04/app/oracle/product/11.2.0.4.5/dbhome_jan15psu/lib
> select * from v$nls_parameters;
PARAMETER VALUE
------------------------------------- ----------------------------------------------------------------
NLS_LANGUAGE ENGLISH
NLS_TERRITORY NEW ZEALAND
NLS_CURRENCY $
NLS_ISO_CURRENCY NEW ZEALAND
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD/MM/RR
NLS_DATE_LANGUAGE ENGLISH
NLS_CHARACTERSET AL32UTF8
NLS_SORT BINARY
NLS_TIME_FORMAT HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT DD/MM/RR HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT DD/MM/RR HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY $
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
19 rows selected.
The listener and tnsnames.ora are set up.
When describing the table in MySQL:
mysql (root)>show columns from sparxuser;
+----------------------------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------------+---------------------+------+-----+---------+-------+
| uid | int(10) unsigned | NO | PRI | NULL | |
| who_are_you | varchar(20) | YES | | | |
| who_are_you_other | varchar(255) | YES | | | |
| age | int(10) unsigned | YES | | NULL | |
| ethnic_group | varchar(200) | YES | | | |
| ethnic_group_other | varchar(255) | YES | | | |
| gender | varchar(20) | YES | | | |
| email_allow | tinyint(3) unsigned | YES | | NULL | |
| mobile | varchar(23) | YES | | | |
| txt_allow | tinyint(3) unsigned | YES | | NULL | |
| where_do_you_live | varchar(20) | YES | | | |
| how_did_you_find_out_other | varchar(255) | YES | | | |
| contact_permission | tinyint(3) unsigned | YES | | NULL | |
+----------------------------------+---------------------+------+-----+---------+-------+
I create a view on the MySQL table, so I can convert the case, e.g. create view sparxuser_vw as select uuid UUID,...
When doing a CTAS in Oracle, and describing the table, I see the column length is three times the size of the MySQL column.
> create table mytest as select * from sparx.sparxuser_vw@mysql_mydb
Table created.
> desc mytest
Name Null? Type
---------------------------------------- -------- ------------------------------------------------
UUID NOT NULL NUMBER(10)
WHO_ARE_YOU VARCHAR2(60)
WHO_ARE_YOU_OTHER VARCHAR2(765)
AGE NUMBER(10)
ETHNIC_GROUP VARCHAR2(600)
ETHNIC_GROUP_OTHER VARCHAR2(765)
GENDER VARCHAR2(60)
EMAIL_ALLOW NUMBER(3)
MOBILE VARCHAR2(69)
TXT_ALLOW NUMBER(3)
WHERE_DO_YOU_LIVE VARCHAR2(60)
HOW_DID_YOU_FIND_OUT_OTHER VARCHAR2(765)
CONTACT_PERMISSION NUMBER(3)