blob4

CREATE OR REPLACE FUNCTION blob_to_text(p_blob BLOB)
RETURN CLOB
IS
l_clob CLOB;
l_raw RAW(32767);
l_len INTEGER;
l_pos INTEGER := 1;
BEGIN
IF p_blob IS NULL THEN
RETURN EMPTY_CLOB();
END IF;

DBMS_LOB.createtemporary(l_clob, TRUE);
l_len := DBMS_LOB.getlength(p_blob);

WHILE l_pos <= l_len LOOP
l_raw := DBMS_LOB.substr(p_blob, 32767, l_pos);
DBMS_LOB.writeappend(
l_clob,
LENGTH(CONVERT(UTL_RAW.cast_to_varchar2(l_raw), 'AL32UTF8')),
CONVERT(UTL_RAW.cast_to_varchar2(l_raw), 'AL32UTF8')
);
l_pos := l_pos + 32767;
END LOOP;

RETURN REPLACE(l_clob, CHR(1), '|');
END;