Oracle通过DBLink操作Excel

最新推荐文章于 2023-04-11 13:19:55 发布

原创最新推荐文章于 2023-04-11 13:19:55 发布 · 2.2k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #microsoft #excel #数据库 #database #sql

Oracle 专栏收录该内容

61 篇文章

订阅专栏

本文详细介绍了如何使用Oracle的DBLink连接并操作Excel文件，包括创建ODBC数据源、配置SID、Listener、tnsnames.ora和数据库链接，以及进行数据查询和修改。在操作过程中需要注意Excel文件的列名不能含有特殊字符，并且需要确保非只读设置才能执行DML操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Oracle通过DBLink操作Excel
连Excel非工业级应用，演示目的在于说明ODBC能连，Generic Connect都能连。

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

2、建立DSN HRXL
创建ODBC数据源，并选系统DSN，可参考下面的网页：
http://www.databasejournal.com/features/oracle/article.php/10893_3442661_1/Making-a-Connection-from-Oracle-to-SQL-Server.htm
注意：
1、在选择数据源的驱动程序的时候，此时选择Driver do Microsoft Excel(*.xls)
2、填写数据库源名：HRXL
选择工作薄路径：c:/HRXL.xls
3、如果想用DML操作，需要去掉只读设置。

3、创建SID HRXL
进入%ORACLE_HOME%/hs/admin，如D:/oracle/product/10.2.0/db_1/hs/admin，拷贝inithsodbc.ora为initHRXL.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 = HRXL
HS_FDS_TRACE_LEVEL = OFF

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

4、添加HRXL到Listener
进入Listener.ora所在目录如D:/oracle/product/10.2.0/db_1/NETWORK/ADMIN，在SID_LIST里面添加：
*******************************************************
(SID_DESC=
      (ORACLE_HOME = D:/oracle/product/10.2.0/db_1)
      (SID_NAME=HRXL)
      (PROGRAM=hsodbc)
    )
*******************************************************
修改后的Listener.ora文件内容如下：
# listener.ora Network Configuration File: D:/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = D:/oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL88.test.com.cn)
      (ORACLE_HOME = D:/oracle/product/10.2.0/db_1)
      (SID_NAME = ORCL88)
    )
    (SID_DESC=
      (SID_NAME=tg4msql)
      (ORACLE_HOME=D:/oracle/product/10.2.0/db_1)
      (PROGRAM=tg4msql)
    )
   (SID_DESC=
      (ORACLE_HOME = D:/oracle/product/10.2.0/db_1)
      (SID_NAME=stapleslink2)
      (PROGRAM=hsodbc)
    )
   (SID_DESC=
      (ORACLE_HOME = D:/oracle/product/10.2.0/db_1)
      (SID_NAME=HRXL)
      (PROGRAM=hsodbc)
    )
)

LISTENER =
(DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.3.88)(PORT = 1521))
    )
)

重新启动下lsnrctl

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

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

7、动态修改global_names参数值：
alter system set global_names = false;
否则会在执行sql时报错：ORA-02085: 数据库链接MSQL与HO.WORLD相连结
原因如下：The GLOBAL_NAMES parameter when set to TRUE implies that database link name should be
similar to the Global database name to which you are trying to connect.

8、测试，请用标准SQL
1) 看看有哪些表
SELECT table_name FROM all_tables@HRXL_LINK;
显示如下：
TABLE_NAME
DEPT$
EMP$

注：如果出现下面的错误，就应该注意查询应该在同一个会话中进行，不然就会出现下面的错误
ORA-28500: 连接 ORACLE 到非 Oracle 系统时返回此信息:
[Generic Connectivity Using ODBC][Microsoft][ODBC Excel Driver] Microsoft Jet 数据库引擎打不开文件'(未知的)'。
它已经被别的用户以独占方式打开，或没有查看数据的权限。 (SQL State: S1000; SQL Code: -1032)
ORA-02063: 紧接着 2 lines (起自 HRXL_LINK)

2)查询下数据
SELECT dept.NAME dept_name, emp.NAME emp_name
FROM dept$@HRXL_LINK dept, emp$@HRXL_LINK emp
WHERE dept.dept_no = emp.dept_no
ORDER BY 1, 2;

出现下面的问题
1、ORA-28500: 连接 ORACLE 到非 Oracle 系统时返回此信息:
[Generic Connectivity Using ODBC][Microsoft][ODBC Excel Driver] INSERT INTO 语句包含下列未知的字段名： 'DEPT_NO? ??'。确定键入的名称是正确的，然后重试。 (SQL State: S0022; SQL Code: -1507)
ORA-02063: 紧接着 2 lines (起自 HRXL_LINK)
解决：EXCEL中列名中不能有空格等其他符号
2、ORA-00904: "DEPT"."DEPT_NO": 标识符无效
解决：换个窗体重新查询就可以了，也挺怪的

查询成功！！
显示如下：
DEPT_NAME EMP_NAME
TEST Name1
TEST2 Name2

3)修改数据
INSERT INTO dept$@HRXL_LINK VALUES (3, 'Test3');
commit;
插入成功！！

UPDATE dept$@HRXL_LINK SET NAME = 'Changed' WHERE dept_no = 3;
commit;
更新成功！！

如果DSN没有去掉只读标志，上面语句会报Ora-28500、Ora-02063错误。

本文参考网页：

http://bbs.erp100.com/thread-36621-1-3.html