** 高级数据迁移 **
很多时间，要在异构数据库之间进行数据迁移或抽取，如果在 SQL 中提取 ORACLE 的数据，可以通过 ODBC 、 OLEDB 等多种方式，要从 ORACLE 提取 SQL 中的数据，大都是通过透明网关来实现的。
在异构数据抽取过程中，最好采用 SQL92 标准的语法编写 SQL 代码，同时要注意不同数据库之间数据类型的转换关系，如 ORACLE 的日期类型用 DATE 、 SQL 用 Datetime 等。
一 关于 ORACLE 的透明网关配置
ORACLE 安装时（ 9i ），将 TRANSPARENT GATEWAY FOR MSSQL 选中，在 ORACLE 主目录 \BIN 中，有个 tg4msql.exe 程序，它是透明网关程序啦，同时在主目录中还有 tgrmsql 的一个目录， ORACLE_HOME\tg4msql\admin 目录中的 inittg4msql.ora 就是需要进行配置才能在 ORACLE 中连接 SQL 。
图 2.1 tg4msql
1 如何配置透明网关呢？打开 inittg4msql.ora:
HS init parameters
xzh 代表 SQL 服务名， pubs 代表要访问的 SQL 数据库
HS_FDS_CONNECT_INFO= "SERVER=xzh;DATABASE=pubs"
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
2 监听的配置 oracle_home\network\admin\Listiner.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = xzh)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = xzh)(PORT = 1527))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = xzh.world)
(ORACLE_HOME = D:\oracle\ora92)
(SID_NAME = xzh)
)
(SID_DESC =
(GLOBAL_DBNAME = tg4msql)
(PROGRAM = tg4msql)
(SID_NAME = tg4msql)
(ORACLE_HOME = D:\oracle\ora92)
)
)
加亮代码是要在监听文件中新加部分， GLOBAL_DBNAME 、 SID_NAME 可以任意， PROGRM 必须指向 tg4msql 如图 2.1 所示。
3 本地服务文件的配置 oracle_home\network\admin\TnsNames.ora
XZH =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = xzh)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = xzh.world)
)
)
TG4MSQL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = xzh)(PORT = 1527))
)
(CONNECT_DATA =
(SID = tg4msql)
)
(HS = OK)
)
使用 SQL 的本地服务名为 TG4MSQL ，可以任意写， SID 必须是 Listiner.ora 中指定的那个 SID_NAME ， PORT 也必须是监听中指定的 PORT ，主机等都要符合一致。 到此为止，我们把透明网关大致已经配置好啦，如果要访问 SQL ，还要使用数据库链才方便。
4 在 SQL 中创建登录帐号 xzh 口令 xzh ，使用可以访问 PUBS 数据库。
CREATE DATABASE LINK sql CONNECT TO xzh IDENTIFIED BY xzh
USING ‘TG4MSQL’
这里的 USING ‘TG4MSQL’ 是 tnsnames.ora 中配置好的本地服务名， sql 是我们以后要引用的数据库链名。
SQL>SELECT * FROM sales@sql
如果有数据返回，表示我们访问 SQL 数据库的通道已经建成，请大胆使用吧，但对远程异构数据库不充许进行 DDL 操作。
SQL>CREATE TABLE all_users@tg4msql FRIN all_users;
ERROR 位于第 1 行 :
ORA-02021: 不允许对远程数据库进行 DDL 操作
二 SQL 访问 ORACLE 的方法
1 通过行集函数 OPENDATASOURCE
OPENDATASOURCE ( provider_name, init_string **) **
** SELECT * **
** FROM OPENDATASOURCE( **
** 'MSDAORA', **
** 'Data Source=xzh.oracle;User ID=POS;Password=POS')..POS.A0325 **
MSDAORA 是 OLEDB FOR ORACLE 的驱动，初始化字符串指定本地服务名、用户名、口令。然后引用表中数据时要以 服务器 ** .. ** ** 用户名 . ** ** 表名。 ** 注意一定是四部分组成，用户名与表名一定要大写。
** SELECT * INTO PUBS.DBO.A0325 FROM **
** OPENDATASOURCE( **
** 'MSDAORA', **
** 'Data Source=xzh.oracle;User ID=POS;Password=POS' )..POS.A0325 **
** -- ** ** 将 ORACLE ** ** 中 POS ** ** 模式的 A0325 ** ** 导入 SQL ** ** 的 PUBS ** ** 数据库。 **
2 通过 SQL 的链接服务器引用 ORACLE 的数据
-- 查看已经存在的链接服务器
SELECT ＊ FROM SYSSERVERS
-- 添加链接服务器到 SQL
EXEC SP_ADDLINKEDSERVER
@server = 'ORCL', --ORCL 是 SQL 中链接服务器名称
@srvproduct = 'Oracle', --Oracle 固定的
@provider = 'MSDAORA', --MSDAORA 固定的
@datasrc = 'xzh.oracle' --DataSrc 本地服务名
EXEC SP_DROPSERVER ‘ORCL’ -- 删除链接服务器
在这里声明一下，如果现在就访问 ORACLE 肯定不能，因为没有提供登录 ORACLE 的用户名 / 口令
EXEC SP_ADDLINKEDSRVLOGIN 'ORCL', false, 'sa', 'POS', 'POS'
Sa 是 SQL 本地登录帐号， POS/POS 是 ORACLE 的登录帐号，但这句话对我们要达到的目的没有帮助。
SELECT * FROM ORCL..POS.A0325
还是四部分组成，注意事项同上，为什么不行呢，本人一直在寻找解决办法，最后发现通过 SQL 语句没法解决，只有打开 SQL 的企业管理器。
图 2.2 配置 ORACLE 的远程帐号
图 2.3 设置远程登录帐号
SELECT * FROM ORCL..POS.A0325 -- 这次终于 OK 。
SELECT * FROM OPENQUERY(ORCL, 'SELECT * FROM POS.A0325')
3 使用 SQL 的 OPENROWSET 函数
** SELECT a.* **
** FROM OPENROWSET('MSDAORA', **
** 'xzh.oracle';'POS';'POS', POS.A0325) AS a ORDER BY a.ID **
有些地方要用别名才能引用，请大家注意。
４使用 ODBC 的方式
** SELECT A.* **
** FROM OPENROWSET('MSDASQL','ORCL_ODBC';'POS';'POS', **
** POS.A0325) AS A **
** ORDER BY A.ID **
ORCL_ODBC 是 ORACLE 的 ODBC 数据源，创建 ODBC 不用我说吧，以上所有代码在 SQL QUERY ANALYZE 中通过。
三 总结
关于 SQL 访问 ORACLE 有四种方式，其中通过 ODBC 与链接服务器的方式做起来难度较大，对手新手会麻烦一些，使用 OLE DB FOR ORACL 驱动时，有 OPENDATASOURCE 与 OPENROWSET 函数可以使用，都很方便，笔记喜欢使用后者，交待一下，在数据存取方面，这四种方面效率相当。