CREATE OR REPLACE FUNCTION blob_to_clob (blob_in IN BLOB) RETURN CLOB
AS
v_clob CLOB;
v_varchar VARCHAR2(32767);
v_start PLS_INTEGER := 1;
v_buffer PLS_INTEGER := 32767;
tmp_num number;
BEGIN
DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);
tmp_num := CEIL(DBMS_LOB.GETLENGTH(blob_in) / v_buffer);
if tmp_num > 0 then
FOR i IN 1..tmp_num
LOOP
v_varchar := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(blob_in, v_buffer, v_start));
DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_varchar), v_varchar);
v_start := v_start + v_buffer;
END LOOP;
end if;
RETURN v_clob;
END blob_to_clob;
2.调用blob_to_clob 来完成转换
update gs_gift p set p.description1=blob_to_clob(p.description);
commit;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26764973/viewspace-1173629/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26764973/viewspace-1173629/