测试数据,存储在D:\oracle,文件名为T_PRINTCLOBOUT.XML。
<?xml version = '1.0'?><ROWSET><ROW num="1"><USER_ID>I0383</USER_ID><PASSWORD>AuyPaTRJcyj3BuapVsqDbVdcvdNMDNbJkuznTg==</PASSWORD><USER_NAME>王鹏</USER_NAME><BRANCH_ID>0383</BRANCH_ID><CHARGE>I0087</CHARGE><PHONE>8463</PHONE><MOBILE>13500789578</MOBILE><EMAIL>[email protected]</EMAIL><USER_TYPE>1</USER_TYPE><USER_LEVEL>S</USER_LEVEL><USER_STATUS>1</USER_STATUS><SIGN_STATUS>F</SIGN_STATUS><PWDCH_TIME>10-12月-07</PWDCH_TIME>
<PWDERR_CNT>0</PWDERR_CNT></ROW>
<ROW num="2"><USER_ID>F0427</USER_ID><PASSWORD>AuyPaTRJcyj3BuapVsqDbVdcvdNMDNbJkuznTg==</PASSWORD><USER_NAME>廖文庆</USER_NAME><BRANCH_ID>0254</BRANCH_ID><CHARGE>F0661</CHARGE><PHONE>88911695</PHONE><MOBILE>13805730684</MOBILE><EMAIL>[email protected]</EMAIL><USER_TYPE>1</USER_TYPE><USER_LEVEL>S</USER_LEVEL><USER_STATUS>1</USER_STATUS><SIGN_STATUS>F</SIGN_STATUS><PWDCH_TIME>10-12月-07</PWDCH_TIME><PWDERR_CNT>0</PWDERR_CNT></ROW>
<ROW num="3"><USER_ID>M0547</USER_ID><PASSWORD>AuyPaTRJcyj3BuapVsqDbVdcvdNMDNbJkuznTg==</PASSWORD><USER_NAME>胡文晴</USER_NAME><BRANCH_ID>0210</BRANCH_ID><CHARGE>M0612</CHARGE><PHONE>63190025-8617</PHONE><MOBILE>13916201875</MOBILE><EMAIL>[email protected]</EMAIL><USER_TYPE>1</USER_TYPE><USER_LEVEL>S</USER_LEVEL><USER_STATUS>1</USER_STATUS><SIGN_STATUS>F</SIGN_STATUS><PWDCH_TIME>10-12月-07</PWDCH_TIME><PWDERR_CNT>0</PWDERR_CNT></ROW>
</ROWSET>
建表语句:
create table T_USER
(
USER_ID VARCHAR2(20),
PASSWORD VARCHAR2(50),
USER_NAME VARCHAR2(30),
BRANCH_ID VARCHAR2(30),
CHARGE VARCHAR2(30),
PHONE VARCHAR2(20),
MOBILE VARCHAR2(20),
EMAIL VARCHAR2(30),
USER_TYPE VARCHAR2(6),
USER_LEVEL VARCHAR2(6),
USER_STATUS VARCHAR2(6),
SIGN_STATUS VARCHAR2(6),
PWDCH_TIME DATE,
PWDERR_CNT VARCHAR2(6)
)
<?xml version = '1.0'?><ROWSET><ROW num="1"><USER_ID>I0383</USER_ID><PASSWORD>AuyPaTRJcyj3BuapVsqDbVdcvdNMDNbJkuznTg==</PASSWORD><USER_NAME>王鹏</USER_NAME><BRANCH_ID>0383</BRANCH_ID><CHARGE>I0087</CHARGE><PHONE>8463</PHONE><MOBILE>13500789578</MOBILE><EMAIL>[email protected]</EMAIL><USER_TYPE>1</USER_TYPE><USER_LEVEL>S</USER_LEVEL><USER_STATUS>1</USER_STATUS><SIGN_STATUS>F</SIGN_STATUS><PWDCH_TIME>10-12月-07</PWDCH_TIME>
<PWDERR_CNT>0</PWDERR_CNT></ROW>
<ROW num="2"><USER_ID>F0427</USER_ID><PASSWORD>AuyPaTRJcyj3BuapVsqDbVdcvdNMDNbJkuznTg==</PASSWORD><USER_NAME>廖文庆</USER_NAME><BRANCH_ID>0254</BRANCH_ID><CHARGE>F0661</CHARGE><PHONE>88911695</PHONE><MOBILE>13805730684</MOBILE><EMAIL>[email protected]</EMAIL><USER_TYPE>1</USER_TYPE><USER_LEVEL>S</USER_LEVEL><USER_STATUS>1</USER_STATUS><SIGN_STATUS>F</SIGN_STATUS><PWDCH_TIME>10-12月-07</PWDCH_TIME><PWDERR_CNT>0</PWDERR_CNT></ROW>
<ROW num="3"><USER_ID>M0547</USER_ID><PASSWORD>AuyPaTRJcyj3BuapVsqDbVdcvdNMDNbJkuznTg==</PASSWORD><USER_NAME>胡文晴</USER_NAME><BRANCH_ID>0210</BRANCH_ID><CHARGE>M0612</CHARGE><PHONE>63190025-8617</PHONE><MOBILE>13916201875</MOBILE><EMAIL>[email protected]</EMAIL><USER_TYPE>1</USER_TYPE><USER_LEVEL>S</USER_LEVEL><USER_STATUS>1</USER_STATUS><SIGN_STATUS>F</SIGN_STATUS><PWDCH_TIME>10-12月-07</PWDCH_TIME><PWDERR_CNT>0</PWDERR_CNT></ROW>
</ROWSET>
建表语句:
create table T_USER
(
USER_ID VARCHAR2(20),
PASSWORD VARCHAR2(50),
USER_NAME VARCHAR2(30),
BRANCH_ID VARCHAR2(30),
CHARGE VARCHAR2(30),
PHONE VARCHAR2(20),
MOBILE VARCHAR2(20),
EMAIL VARCHAR2(30),
USER_TYPE VARCHAR2(6),
USER_LEVEL VARCHAR2(6),
USER_STATUS VARCHAR2(6),
SIGN_STATUS VARCHAR2(6),
PWDCH_TIME DATE,
PWDERR_CNT VARCHAR2(6)
)
解决方案 »
- fetch into 问题
- select * from dept 当前 deptno 的 dname 值取 deptno+1 的 dname,如果是最大的,取最小的那个
- 帮忙提高一下这个存储过程的效率,存储过程很简单
- 如何使用LogMiner察看重做日志文件??中秋快乐!
- 关于触发器的夸用户触发的问题。
- 求几个有点难度,但是很常用的sql语句
- select ltrim('abc_abc','_') from dual;
- 请问哪有Oracle 9 的 Oracle ODBC 驱动程序 下载
- “占位列”有什么作用?
- oracle 中有类似sql Server中的case when...作用的函数吗?
- dba_data_files里面 字段relative_fno 如何理解?谢谢!!
- oracle9i实例进程每隔几个小时自动关掉
http://topic.csdn.net/u/20100610/10/d0416f57-b9db-49c4-acee-3bc2f6d8cdf4.html?78823
SQL> create or replace procedure add_to_T_USER(fileName IN varchar2) as
2 document xmldom.DOMDocument;
3 subelement xmldom.DOMElement;
4 nodelistStock xmldom.DOMNodeList;
5 nodelistStockChild xmldom.DOMNodeList;
6 USER_ID VARCHAR2(20);
7 PASSWORD VARCHAR2(50);
8 USER_NAME VARCHAR2(30);
9 BRANCH_ID VARCHAR2(30);
10 CHARGE VARCHAR2(30);
11 PHONE VARCHAR2(20);
12 MOBILE VARCHAR2(20);
13 EMAIL VARCHAR2(30);
14 USER_TYPE VARCHAR2(6);
15 USER_LEVEL VARCHAR2(6);
16 USER_STATUS VARCHAR2(6);
17 SIGN_STATUS VARCHAR2(6);
18 PWDCH_TIME DATE;
19 PWDERR_CNT VARCHAR2(6);
20 recordCountOuter number;
21 quantity exception;
22 begin
23
24 document:=xmlparser.parse(fileName);
25 subelement:=xmldom.getDocumentElement(document);
26 nodelistStock:=xmldom.getElementsByTagName(subelement,'T_USER');
27 recordCountOuter:=xmldom.getLength(nodelistStock);
28 for outerCycle in 0..recordCountOuter-1 loop
29 nodelistStockChild:=xmldom.getChildNodes(xmldom.item(nodelistStock,outerCyc
le));
30 if xmldom.getLength(nodelistStockChild)<>13 then
31 raise quantity;
32 end if;
33 USER_ID :=xmldom.getNodeValue(xmldom.getFirstChild(xmldom.item(nodelistStoc
kChild,0)));
34 PASSWORD:=xmldom.getNodeValue(xmldom.getFirstChild(xmldom.item(nodelistStoc
kChild,1)));
35 USER_NAME:=xmldom.getNodeValue(xmldom.getFirstChild(xmldom.item(nodelistSto
ckChild,1)));
36 BRANCH_ID:=xmldom.getNodeValue(xmldom.getFirstChild(xmldom.item(nodelistSto
ckChild,2)));
37 CHARGE:=xmldom.getNodeValue(xmldom.getFirstChild(xmldom.item(nodelistStock
Child,3)));
38 PHONE:=xmldom.getNodeValue(xmldom.getFirstChild(xmldom.item(nodelistStockCh
ild,4)));
39 MOBILE:=xmldom.getNodeValue(xmldom.getFirstChild(xmldom.item(nodelistStockC
hild,5)));
40 EMAIL :=xmldom.getNodeValue(xmldom.getFirstChild(xmldom.item(nodelistStockC
hild,6)));
41 USER_TYPE:=xmldom.getNodeValue(xmldom.getFirstChild(xmldom.item(nodelistSto
ckChild,7)));
42 USER_LEVEL:=xmldom.getNodeValue(xmldom.getFirstChild(xmldom.item(nodelistSt
ockChild,8)));
43 USER_STATUS:=xmldom.getNodeValue(xmldom.getFirstChild(xmldom.item(nodelistS
tockChild,9)));
44 SIGN_STATUS:=xmldom.getNodeValue(xmldom.getFirstChild(xmldom.item(nodelistS
tockChild,10)));
45 PWDCH_TIME:=to_date(xmldom.getNodeValue(xmldom.getFirstChild(xmldom.item(no
delistStockChild,11))),'yyyy-fmMM-fmDD HH24:MI:SS');
46 PWDERR_CNT :=xmldom.getNodeValue(xmldom.getFirstChild(xmldom.item(nodelistS
tockChild,12)));
47 insert into T_USER values(USER_ID,PASSWORD,USER_NAME,BRANCH_ID,CHARGE,PHONE
,MOBILE,EMAIL,USER_TYPE,USER_LEVEL,USER_STATUS,SIGN_STATUS,PWDCH_TIME,PWDERR_CNT
);
48 end loop;
49 commit;
50 exception
51 when quantity then
52 raise_application_error(-20010,'请核对该xml文档与数据库表T_USER的结构是否一
致,'
53 ||chr(10)||'若仍不能解决问题,请与系统管理员联系!');
54 end;
55 /过程已创建。SQL> execute add_to_T_USER('D:\oracle\T_PRINTCLOBOUT.XML');
BEGIN add_to_T_USER('D:\oracle\T_PRINTCLOBOUT.XML'); END;*
第 1 行出现错误:
ORA-31001: 资源句柄或路径名 "D:\oracle\T_PRINTCLOBOUT.XML" 无效
ORA-06512: 在 "SYS.XDBURITYPE", line 11
ORA-06512: 在 "XDB.DBMS_XSLPROCESSOR", line 142
ORA-29280: 目录路径无效
ORA-29280: 目录路径无效
ORA-29280: 目录路径无效
ORA-06512: 在 "XDB.DBMS_XMLPARSER", line 40
ORA-06512: 在 "XMLIMP.ADD_TO_T_USER", line 24
ORA-06512: 在 line 1
为什么啊?
谁有详细方法吗?