ITPub博客

首页 > 数据库 > Oracle > 很有意思的连接方法,Oracle通过DBLink操作Excel

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

原创 Oracle 作者:box 时间:2015-11-28 15:34:54 0 删除 编辑
转载链接:http://www.itpub.net/thread-755726-1-1.html
PUB大神:
huajhua


连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 =
注意这里文件名中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 =
     (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.        删除问题
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;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/121967/viewspace-1848554/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 团队协同精神!
请登录后发表评论 登录
全部评论

注册时间:2011-03-09

  • 博文量
    77
  • 访问量
    185112