blob2

CREATE OR REPLACE FUNCTION blob_to_text(p_blob BLOB) RETURN VARCHAR2 IS
l_clob CLOB;
l_dest_offset INTEGER := 1;
l_src_offset INTEGER := 1;
l_lang_ctx INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;
l_warning INTEGER;
l_text VARCHAR2(32767);
BEGIN
IF p_blob IS NULL THEN
RETURN '';
END IF;

DBMS_LOB.CREATETEMPORARY(l_clob, TRUE);

DBMS_LOB.CONVERTTOCLOB(
dest_lob => l_clob,
src_blob => p_blob,
amount => DBMS_LOB.GETLENGTH(p_blob),
dest_offset => l_dest_offset,
src_offset => l_src_offset,
blob_csid => NLS_CHARSET_ID('AL32UTF8'),
lang_context => l_lang_ctx,
warning => l_warning
);

l_text := DBMS_LOB.SUBSTR(l_clob, 32767, 1);

DBMS_LOB.FREETEMPORARY(l_clob);

RETURN REPLACE(l_text, CHR(1), '|');
END;
/