oracle创建与mysql的dblink

    由于项目开发的需求，需要通过oracle访问mysql，必须配置“网关”,通过ODBC实现

    总结了一下从oracle访问mysql的步骤：

    1.先简单介绍下环境

    操作系统：windows xp

    oracle：10.0.2.0.1

    mysql：5.0

    2.下载mysql的odbc驱动，我用的是myodbc 3.5.1并安装

    下载路径:http://dev.mysql.com/downloads/connector/odbc/3.51.html

    3.配置odbc数据源，在控制面板中配置就可以了，使用系统DSN，取名为mysqltest

    4.在E:/oracle/product/10.2.0/db_1/hs/admin下(E:/oracle/product/10.2.0/db_1为ORACLE_HOME的安装目录)建立initmysqltest.ora文件

    输入：

    HS_FDS_CONNECT_INFO = mysqltest

    HS_FDS_TRACE_LEVEL = OFF

    5.在E:/oracle/product/10.2.0/db_1/network/admin下修改

    listener.ora

    输入：

    (SID_DESC =

    (SID_NAME = mysql)

    (ORACLE_HOME = E:/oracle/product/10.2.0/db_1)

    (PROGRAM = hsodbc)

    )

    修改后的listener.ora如下:

    SID_LIST_LISTENER =

    (SID_LIST =

    (SID_DESC =

    (SID_NAME = PLSExtProc)

    (ORACLE_HOME = E:/oracle/product/10.2.0/db_1)

    (PROGRAM = extproc)

    )

    (SID_DESC =

    (SID_NAME = mysql)

    (ORACLE_HOME = E:/oracle/product/10.2.0/db_1)

    (PROGRAM = hsodbc)

    )

    )

    LISTENER =

    (DESCRIPTION_LIST =

    (DESCRIPTION =

    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

    )

    (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = MAJY)(PORT = 1521))

    )

    (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = MAJY)(PORT = 1541))

    )

    )

    6.修改tnsnames.ora

    输入：

    mysqltest =

    (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = MAJY)(PORT = 1541))

    (CONNECT_DATA =

    (SID=mysqltest)

    )

    (HS =ok)

    )

    7.创建dblink

    create public database link mysqllink

    connect to “ws” identified by “ws”

    using ‘mysqltest’;

    要注意用户名和密码处需要用双引号，否则Oracle所传输的都是大写字母，可能无法登录进入MySQL。

    8.访问mysql库中的表user

    select “username” from “user”@mysqllink

    由于MySQL中的表名的大小写敏感，因此需要在进行SQL查询时对表名用双引号扩起来