小弟得到图片二进制流对应的十六进制数据字符串,如何将该十六进制字符串更新写入到blob对象的hex中,通过以下方式更新的话,会写为字符串形式
create or replace procedure pro_union_binary(recordid number) is
cursor v_cursor is
select * from tb_image_info where itemid = recordid order by ilineid;
v_item v_cursor%rowtype;
v_count integer default 0;
v_ilineid integer default 0;
v_sql varchar2(32767) default '';
v_characters clob;
v_seq integer default 1;
v_blob blob;
warning integer;
dest_offset1 integer := 1;
src_offset1 integer := 1;
lang_ctx INTEGER := DBMS_LOB.default_lang_ctx;
begin
select count(*) into v_count from tb_image_info where itemid = recordid;
if v_count = 1 then
return;
end if;
open v_cursor;
loop
fetch v_cursor
into v_item;
exit when v_cursor%notfound;
if v_ilineid = 0 then
v_ilineid := v_item.ilineid;
end if;
v_characters := v_characters || v_item.image; --获取已经得到的二进制文件流对应的十六进制字符串
if v_seq = v_count then
if dbms_lob.getlength(v_characters) = 0 then
return;
end if;
DBMS_LOB.createtemporary(v_blob, TRUE);
DBMS_LOB.OPEN(v_blob, DBMS_LOB.lob_readwrite);
DBMS_LOB.convertToBlob(v_blob,
v_characters,
DBMS_LOB.lobmaxsize,
dest_offset1,
src_offset1,
DBMS_LOB.default_csid,
lang_ctx,
warning);
v_sql := 'update tb_image_info set image0 = :1 where ilineid = :2';
execute immediate v_sql
using v_blob, v_ilineid;
end if;
v_seq := v_seq + 1;
end loop;
end pro_union_binary;我是想直接将得到的十六进制数据字符串更新blob对象的hex数据?请有类似经验的朋友帮忙指点哈,谢谢。
create or replace procedure pro_union_binary(recordid number) is
cursor v_cursor is
select * from tb_image_info where itemid = recordid order by ilineid;
v_item v_cursor%rowtype;
v_count integer default 0;
v_ilineid integer default 0;
v_sql varchar2(32767) default '';
v_characters clob;
v_seq integer default 1;
v_blob blob;
warning integer;
dest_offset1 integer := 1;
src_offset1 integer := 1;
lang_ctx INTEGER := DBMS_LOB.default_lang_ctx;
begin
select count(*) into v_count from tb_image_info where itemid = recordid;
if v_count = 1 then
return;
end if;
open v_cursor;
loop
fetch v_cursor
into v_item;
exit when v_cursor%notfound;
if v_ilineid = 0 then
v_ilineid := v_item.ilineid;
end if;
v_characters := v_characters || v_item.image; --获取已经得到的二进制文件流对应的十六进制字符串
if v_seq = v_count then
if dbms_lob.getlength(v_characters) = 0 then
return;
end if;
DBMS_LOB.createtemporary(v_blob, TRUE);
DBMS_LOB.OPEN(v_blob, DBMS_LOB.lob_readwrite);
DBMS_LOB.convertToBlob(v_blob,
v_characters,
DBMS_LOB.lobmaxsize,
dest_offset1,
src_offset1,
DBMS_LOB.default_csid,
lang_ctx,
warning);
v_sql := 'update tb_image_info set image0 = :1 where ilineid = :2';
execute immediate v_sql
using v_blob, v_ilineid;
end if;
v_seq := v_seq + 1;
end loop;
end pro_union_binary;我是想直接将得到的十六进制数据字符串更新blob对象的hex数据?请有类似经验的朋友帮忙指点哈,谢谢。
http://www.eygle.com/archives/2005/04/eoaiplsqlieoeao.html你怎么用字符串去更新BLOB?类型都不一样你应该用流将字符串写进文件,然后再更新BLOB字段
update trkhdr set noteblob = '王' where id='asdf'
出错: 无效的16进制数字
update trkhdr set noteblob = utl_raw.cast_to_raw('王') where id='asdf'插入空值是最好先插入 empty_blob()blob转换成 varchar ,并限制读取blob大小 (如果太长报错)utl_raw.cast_to_varchar2(dbms_lob.substr(blob_rec.col2,2000,1))#好像只能2000哦