通过oracle Database Gateway for ODBC访问MySQL

1. 配置ODBC源 --获得dsn名，这里是mysql_test

2. 修改ORACLE自带的异构服务配置 ：$ORACLE_HOME/hs/admin/init<dsn_name>.ora

HS_FDS_CONNECT_INFO = mysql_test

3. 修改ORACLE_HOME/network/admin/中的listener.ora与tnsnames.ora

listener.ora:

LISTENER =
 (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
 )
SID_LIST_LISTENER=
  (SID_LIST=
      (SID_DESC=
         (SID_NAME=mysql_test)
         (ORACLE_HOME=D:\oracle\product\11.2.0.4)
         (PROGRAM=dg4odbc)
      )
  )
tnsnames.ora:

orcl_mysql =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1 )(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = mysql_test)
    )
   (HS = OK)
  )

4. 在oracle数据库中创建database link

create  PUBLIC DATABASE LINK tomysql connect to "test" identified by "123456" using 'orcl_mysql';

5. 在oralce端查询与插入mysql

select * from student@tomysql;
insert into person@tomysql values(2,'李四');

6. 以上配置完成，如有乱码，根本原因是字符集配置不当引起。由于mysql优秀的字符集机制，会自动进行字符集转换，使得oracle端是zhs16gbk时，仍可通过odbc gateway访问mysql中的数据（存储字符集为utf8mb4)，而不出现乱码。