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博客

Blob-To-Clob