blob7

CREATE OR REPLACE FUNCTION blob_to_text2(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);

-- Convert BLOB (binary UTF-8) → CLOB (text)
DBMS_LOB.CONVERTTOCLOB(
dest_lob => l_clob,
src_blob => p_blob,
amount => DBMS_LOB.LOBMAXSIZE,
dest_offset => l_dest_offset,
src_offset => l_src_offset,
blob_csid => NLS_CHARSET_ID('AL32UTF8'),
lang_context => l_lang_ctx,
warning => l_warning
);

-- Trim to VARCHAR2(32767) safely (your blobs are tiny anyway)
l_text := DBMS_LOB.SUBSTR(l_clob, 32767, 1);

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