一。两种方法实现
blob到clob的转换
CREATE OR REPLACE FUNCTION blob2clob(v_blob_in IN BLOB) RETURN CLOB IS
v_file_clob CLOB;
v_file_size INTEGER := dbms_lob.lobmaxsize;
v_dest_offset INTEGER := 1;
v_src_offset INTEGER := 1;
v_blob_csid NUMBER := dbms_lob.default_csid;
v_lang_context NUMBER := dbms_lob.default_lang_ctx;
v_warning INTEGER;
v_length NUMBER;
BEGIN
dbms_lob.createtemporary(v_file_clob, TRUE);
dbms_lob.converttoclob(v_file_clob,
v_blob_in,
v_file_size,
v_dest_offset,
v_src_offset,
v_blob_csid,
v_lang_context,
v_warning);
RETURN v_file_clob;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error found');
END;
/
CREATE OR REPLACE FUNCTION blob2clob(b BLOB) RETURN CLOB IS
c CLOB;
n NUMBER;
BEGIN
IF (b IS NULL) THEN
RETURN NULL;
END IF;
IF (length(b) = 0) THEN
RETURN empty_clob();
END IF;
dbms_lob.createtemporary(c, TRUE);
n := 1;
WHILE (n + 32767 <= length(b)) LOOP
dbms_lob.writeappend(c,
32767,
utl_raw.cast_to_varchar2(dbms_lob.substr(b,
32767,
n)));
n := n + 32767;
END LOOP;
dbms_lob.writeappend(c,
length(b) - n + 1,
utl_raw.cast_to_varchar2(dbms_lob.substr(b,
length(b) - n + 1,
n)));
RETURN c;
END;
会出现错误的地方
1.如果转换的blob文件长度为0则会报错 ORA-22994: 源偏移量超出源 LOB 的结尾
2. offset是in out参数,第一次传入后会被更改,如果是loop处理。应该将其值重新初始化。否则会报错ora-22994
如下
declare
v_cl clob;
dest_offset integer := 1;
src_offset integer := 1;
lang_context integer := dbms_lob.default_lang_ctx;
warning integer;
begin
for pr in (select pr_id, cmms_log
from purchase_requisition
where dbms_lob.getlength(cmms_log) > 0) loop
dbms_lob.createtemporary(lob_loc => v_cl, cache => false);
dbms_lob.convertToClob(dest_lob => v_cl,
src_blob => pr.cmms_log,
amount => dbms_lob.lobmaxsize,
dest_offset => dest_offset,
src_offset => src_offset,
blob_csid => dbms_lob.default_csid,
lang_context => lang_context,
warning => warning);
--dbms_output.put_line(warning);
update purchase_requisition
set cmms_log_clob = v_cl
where pr_id = pr.pr_id;
end loop;
--commit;
rollback;
exception
when others then
rollback;
dbms_output.put_line('更新出错:' || sqlcode || ';' || sqlerrm);
end;
正确应该是
declare
v_cl clob;
dest_offset integer := 1;
src_offset integer := 1;
lang_context integer := dbms_lob.default_lang_ctx;
warning integer;
begin
dbms_output.put_line('开始执行');
for pr in (select pr_id, cmms_log
from purchase_requisition
where dbms_lob.getlength(cmms_log) > 0) loop
dbms_output.put_line(pr.pr_id);
dest_offset := 1;
src_offset := 1;
lang_context := dbms_lob.default_lang_ctx
dbms_lob.createtemporary(lob_loc => v_cl, cache => false);
dbms_lob.convertToClob(dest_lob => v_cl,
src_blob => pr.cmms_log,
amount => dbms_lob.lobmaxsize,
dest_offset => dest_offset,
src_offset => src_offset,
blob_csid => dbms_lob.default_csid,
lang_context => lang_context,
warning => warning);
dbms_output.put_line(warning);
update purchase_requisition
set cmms_log_clob = v_cl
where pr_id = pr.pr_id;
end loop;
commit;
exception
when others then
rollback;
dbms_output.put_line('更新出错:' || sqlcode || ';' || sqlerrm);
end;
二。clob到blob的转换
同一,将函数换成 dbms_lob.converttoblob即可