Oracle通过DBLink操作Excel

很有意思的连接方法，Oracle通过DBLink操作Excel

连Excel非工业级应用，演示目的在于说明ODBC能连，Generic Connect都能连。

2.2.1. 创建数据库HSXLSExample.xls
1、 Sheet1“DEPT”，字段及数据如下：
DEPT_NO NAME
1 Test
2 Test2
2、 Sheet2“EMP”，字段及数据如下：
EMP_NO NAME DEPT_NO
1 Name1 1
2 Name2 2

2.2.2. 建立DSN HSXLSExample
创建ODBC数据源，并选系统DSN，如下图：

注意，如果想用DML操作，需要去掉只读设置。

2.2.3. 创建SID HSXLS
进入%ORACLE_HOME%/hs/admin，如D:/Oracle/proddb/9.2.0/hs/admin，拷贝inithsodbc.ora为initHSXLS.ora，并改写内容如下：
# This is a sample agent init file that contains the HS parameters that are
# needed for an ODBC Agent.

#
# HS init parameters
#
HS_FDS_CONNECT_INFO = HSXLSExample
HS_FDS_TRACE_LEVEL = 0

#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>
注意这里文件名中init之后的字符，它代表一个SID，而HS_FDS_CONNECT_INFO配置的就是ODBC数据源名称。

2.2.4. 添加HSXLS到Listener
进入Listener.ora所在目录如D:/Oracle/proddb/9.2.0/network/admin/PROD_huajhua，在SID_LIST里面添加：
(SID_DESC =
(SID_NAME = HSXLS)
(ORACLE_HOME = <your oracle_home> )
(PROGRAM = hsodbc)
)
比如我的数据库，添加完后内容如下：
#
# Net8 definition for Database listener
#

PROD =
(ADDRESS_LIST =
(ADDRESS= (PROTOCOL= IPC)(KEY= EXTPROCPROD))
(ADDRESS= (PROTOCOL= TCP)(Host= huajhua.leiko.com )(Port= 1521))
)

SID_LIST_PROD =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME= d:/oracle/proddb/9.2.0)
(SID_NAME = PROD)
)
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = d:/oracle/proddb/9.2.0)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = HSXLS)
(ORACLE_HOME = d:/oracle/proddb/9.2.0)
(PROGRAM = hsodbc)
)
)

……
重新Listener服务。至此，相当于建立了一个数据库HSXLS，有SID，有侦听。接下来可以像连接Oracle数据库一样用DB Link连接HSXLS。

2.2.5. 添加HSXLS到tnsnames.ora
可以在另一个Oracle数据库上配置，这里我还是在本机做，添加：
HSXLS=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=huajhua.leiko.com)(PORT=1521))
(CONNECT_DATA=(SID=HSXLS))
(HS=OK)
)
和普通TNSNAME不同点在于(HS=OK)。

2.2.6. 创建DB Link HSXLS_LINK
可以在另一个Oracle数据库上配置，这里我还是在本机做，创建脚本：
create database link HSXLS_LINK
connect to leiko identified by LEIKO_PASSWORD
using 'HSXLS';
注意这里用的是Excel文件所在的操作系统的用户名和密码，想象一下，Excel文件可能在网上邻居；如果是本机，则随便给都行。

2.2.7. 测试，请用标准SQL
1、 看看有哪些表
SELECT table_name FROM all_tables@hsxls_link
返回：
TABLE_NAME
1 DEPT$
2 EMP$
2、 查询下数据
SELECT dept.NAME dept_name, emp.NAME emp_name
FROM dept$@hsxls_link dept, emp$@hsxls_link emp
WHERE dept.dept_no = emp.dept_no
ORDER BY 1, 2
返回：
DEPT_NAME EMP_NAME
1 Test Name1
2 Test2 Name2
3、 DML操作
INSERT INTO dept$@hsxls_link VALUES (3, 'Test3');

UPDATE dept$@hsxls_link SET NAME = 'Changed' WHERE dept_no = 3;
如果DSN没有去掉只读标志，上面语句会报Ora-28500、Ora-02063错误。

2.2.8. 删除问题(oledb 方法连接的excel和文本也有同样问题)
Excel不支持删除，DELETE FROM dept$@hsxls_link WHERE dept_no = 3;报“该 ISAM 不支持在链接表中删除数据”。
因为Excel工作表中的行与真正的关系型数据库的行是有些不一样的，所以如果通过编程来访问Excel工作表的数据，那么检索，插入，更新应该是没有问题的，但要是删除的话，就会有一些限制。
删除 Excel 数据时，受到的限制要比从关系数据源中删除数据时更多。在关系数据库中，“行”除了表示一条“记录”外没有其他意义；但在 Excel 工作表中却不同。可以删除字段（单元格）中的值，但不能：
1、 一次删除一整条记录，否则将出现以下错误信息：
Deleting data in a linked table is not supported by this ISAM.
只能通过分别清空各个字段的内容来删除一条记录。
2、 删除包含 Excel 公式的单元格中的值，否则将出现以下错误信息：
Operation is not allowed in this context.
3、 虽然电子表格中已被删除的数据原来所在的行现在是空行，但无法将其删除，而且记录集将继续显示对应于这些空行的空记录。
针对这个问题，我们通常简单的做法就是清空字段的值，类似这样的写法：
UPDATE dept$@hsxls_link SET NAME = NULL, dept_no = NULL WHERE dept_no = 3;
当然，你要知道的是，这一行并没有真正删除掉，所以通常在select的时候要进行一定的筛选，例如：
SELECT * FROM dept$@hsxls_link WHERE dept_no IS NOT NULL;