我尝试了以下两种方法: update table set blobcolname='<?xml>...'; 出现以下错误:“string literal tool long”-因为我插入的xml值是tool long2.
update table set blobcolname='<?xml>...';
DECLAREstr varchar2(32767);BEGINstr:='<?xml>...';update table set blobcolname = str;END;/
DECLARE
str varchar2(32767);
BEGIN
str:='<?xml>...';
update table set blobcolname = str;
END;
/
出现此错误:ora-01461:只能为插入到长列绑定长值
rjjhvcjd1#
如果您可以选择在数据库中安装两个函数,我将使用以下方法:首先:我创建一个函数来将clob转换为blob对象
create or replace function clob_to_blob (p1_clob CLOB) return BLOB is Result BLOB; o1 integer; o2 integer; c integer; w integer;begin o1 := 1; o2 := 1; c := 0; w := 0; DBMS_LOB.CreateTemporary(Result, true); DBMS_LOB.ConvertToBlob(Result, p1_clob, length(p1_clob), o1, o2, 0, c, w); return(Result);end clob2blob;/
create or replace function clob_to_blob (p1_clob CLOB) return BLOB is
Result BLOB;
o1 integer;
o2 integer;
c integer;
w integer;
begin
o1 := 1;
o2 := 1;
c := 0;
w := 0;
DBMS_LOB.CreateTemporary(Result, true);
DBMS_LOB.ConvertToBlob(Result, p1_clob, length(p1_clob), o1, o2, 0, c, w);
return(Result);
end clob2blob;
第二:我创建一个相反的函数,将blob转换为clob
create or replace function blob_to_char (p1_blob BLOB)return clob is out_clob clob; n number;begin if (p1_blob is null) then return null; end if; if (length(p1_blob)=0) then return empty_clob(); end if; dbms_lob.createtemporary(out_clob,true); n:=1; while (n+32767<=length(p1_blob)) loop dbms_lob.writeappend(out_clob,32767,utl_raw.cast_to_varchar2(dbms_lob.substr(p1_blob,32767,n))); n:=n+32767; end loop; dbms_lob.writeappend(out_clob,length(p1_blob)-n+1,utl_raw.cast_to_varchar2(dbms_lob.substr(p1_blob,length(p1_blob)-n+1,n))); return out_clob;end;/
create or replace function blob_to_char (p1_blob BLOB)
return clob is
out_clob clob;
n number;
if (p1_blob is null) then
return null;
end if;
if (length(p1_blob)=0) then
return empty_clob();
dbms_lob.createtemporary(out_clob,true);
n:=1;
while (n+32767<=length(p1_blob)) loop
dbms_lob.writeappend(out_clob,32767,utl_raw.cast_to_varchar2(dbms_lob.substr(p1_blob,32767,n)));
n:=n+32767;
end loop;
dbms_lob.writeappend(out_clob,length(p1_blob)-n+1,utl_raw.cast_to_varchar2(dbms_lob.substr(p1_blob,length(p1_blob)-n+1,n)));
return out_clob;
end;
第三:验证是否可以插入
declarestr clob;BEGINstr :='<?xml>...';update table set blobcolname = clob_to_blob ( str );END;/
declare
str clob;
str :='<?xml>...';
update table set blobcolname = clob_to_blob ( str );
最后:做一个回归测试,检查我得到的xml是否与最初一样
select blob_to_char( blobcolname ) from your table;
1条答案
按热度按时间rjjhvcjd1#
如果您可以选择在数据库中安装两个函数,我将使用以下方法:
首先:我创建一个函数来将clob转换为blob对象
第二:我创建一个相反的函数,将blob转换为clob
第三:验证是否可以插入
最后:做一个回归测试,检查我得到的xml是否与最初一样