通过oracle Database Gateway for ODBC访问MySQL(win10环境
1. 配置ODBC源 --获得dsn名,这里是mysql_test
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/4ad0770717328434e9ff13b1def44f32.png)
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),而不出现乱码。
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/1a3fed26310c732afcf0bc83e674365c.png)