我是这么处理的。
1。读入该xml文档,变为字符串。
2。用update sql语句存入数据库。
fcontent为xmltype字段。
原sql语句如下: update WORKFLOW_FILE set MASTERID=1263,FILEID=1,fcontent=xmltype('<?xml version="1.0" encoding="GBK" standalone="yes"?> <Fields><receiveinfo/><receivedate/>........') 结果,出错!!
ORA-01704: 文字字符串过长 请问各位大人有何办法??
1。读入该xml文档,变为字符串。
2。用update sql语句存入数据库。
fcontent为xmltype字段。
原sql语句如下: update WORKFLOW_FILE set MASTERID=1263,FILEID=1,fcontent=xmltype('<?xml version="1.0" encoding="GBK" standalone="yes"?> <Fields><receiveinfo/><receivedate/>........') 结果,出错!!
ORA-01704: 文字字符串过长 请问各位大人有何办法??
Create or Replace Directory XMLDIR_SCHEMA as 'C:\SCHEMA';
Grant Create Any Directory to finegold;
Grant Read On Directory XMLDIR_SCHEMA to finegold;
Grant Write On Directory XMLDIR_SCHEMA to finegold;
Grant Create Procedure to finegold;
Grant Create Session to finegold;
Grant Alter Session to finegold;
Grant Create Trigger to finegold;
Grant Create Table to finegold;
Grant Create Type to finegold;
Grant Analyze any to finegold;
Grant Connect, Resource to finegold;2。用finegold登录,创建getDocument函数,用来读取位于C:\SCHEMA目录下的,SCHEMA文件
create or replace function getDocument(
filename varchar2
) return clob deterministic
authid current_user is
xbfile bfile;
xclob clob;
dest_offset number := 1;
src_offset number := 1;
lang_context number := 0;
conv_warning number := 0;
v_CharID int:=0 ;
begin
xbfile := bfilename('XMLDIR_SCHEMA',filename);
dbms_lob.open(xbfile);
dbms_lob.createtemporary(xclob,TRUE,dbms_lob.session);
DBMS_LOB.loadClobfromFile (
xclob,
xbfile,
DBMS_LOB.getLength(xbfile),
dest_offset,
src_offset,
v_CharID,
lang_context,
conv_warning
);
dbms_lob.close(xbfile);
return xclob;
end;
3.然后用insert into table values(xmltype(getDocument('test.xml')))
/CREATE OR REPLACE FUNCTION getClobDocument
( filename in varchar2,
charset in varchar2 default NULL,
dir IN VARCHAR2 default 'DIR_TEMP',
) RETURN CLOB deterministic is file bfile := bfilename(dir,filename);
charContent CLOB := ' ';
targetFile bfile;
lang_ctx number := DBMS_LOB.default_lang_ctx;
charset_id number := 0;
src_offset number := 1 ;
dst_offset number := 1 ;
warning number;
BEGIN
IF charset is not null then
charset_id := NLS_CHARSET_ID(charset);
end if; targetFile := file;
DBMS_LOB.fileopen(targetFile, DBMS_LOB.file_readonly);
DBMS_LOB.LOADCLOBFROMFILE(charContent, targetFile,
DBMS_LOB.getLength(targetFile), src_offset, dst_offset,
charset_id, lang_ctx,warning);
DBMS_LOB.fileclose(targetFile);
return charContent;
end;
/insert into xml_table values(1, XMLTYPE(GetClobDocument('purchaseorder.xml')))
/