Oracle通过ODBC连接MySql(windows下)--配置多个odbc连接

一、环境及安装参考网址：

https://blog.csdn.net/zltAlma/article/details/89333202

二、配置多个odbc连接：

配置2个odbc连接，一个叫dg4odbc，一个叫mysql_test

Oracle数据库相关配置 （1）F:\app\admin\product\11.2.0\dbhome_1\hs\admin\initdg4odbc.ora，添加如下内容：

HS_FDS_CONNECT_INFO = dg4odbc 说明：和mysql的odbc保持一致 HS_FDS_TRACE_LEVEL = off 说明：需要调试时可以改为debug，调试完成改为off；

在F:\app\admin\product\11.2.0\dbhome_1\hs\admin\目录下增加文件initmysql_test.ora，内容如下，保存：

HS_FDS_CONNECT_INFO = mysql_test HS_FDS_TRACE_LEVEL = off

（2）打开F:\app\admin\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora 添加内容：

SID_LIST_LISTENER1 = (SID_LIST = (SID_DESC = (SID_NAME = dg4odbc) (ORACLE_HOME = F:\app\admin\product\11.2.0\dbhome_1) (PROGRAM = dg4odbc) ) (SID_DESC = (SID_NAME = mysql_test) (ORACLE_HOME = F:\app\zlt\product\11.2.0\dbhome_1) (PROGRAM = dg4odbc) ) ) LISTENER1 = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = john1-john)(PORT = 1522)) ) ) ADR_BASE_LISTENER1 = F:\app\admin

（3）配置tnsnames.ora（目录：F:\app\admin\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora），添加如下代码：

dg4odbc = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1522)) ) (CONNECT_DATA = (SERVICE_NAME = dg4odbc) ) (HS = OK) ) mysql_test = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1522)) ) (CONNECT_DATA = (SERVICE_NAME = mysql_test) ) (HS = OK) ) 用oracle连接工具创建dblink，使用管理员身份用户执行： create database link dg4odbc connect to "root" identified by "123456" using 'dg4odbc';--注意使用单引号 create database link mysql_test connect to "root" identified by "123456" using 'mysql_test';

或者 先用管理员身份用户对业务用户授权，再用scott用户创建dblink

grant create database link to scott;

注：如果在create之后加public(即create pubic database link……),则创建的dblink就是公共的,不加public就只有创建者可以使用了. 6. 测试

select * from dual@dg4odbc; select * from “table1”@dg4odbc;--表名区分大小写时，需要用双引号 insert into table1@"dg4odbc" values (2, '你好'); select m.*,m."aaa",m."bBb" from table1@dg4odbc m --注意区分大小写，否则报错：Oracle ORA-00904："xxx": 标识符无效