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

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

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

二、配置多个odbc连接：

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

2. 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)
  )

4. 用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": 标识符无效