blob5

CREATE OR REPLACE FUNCTION blob_to_text2(p_blob BLOB)
RETURN VARCHAR2
IS
l_raw RAW(4000);
l_len PLS_INTEGER;
l_text VARCHAR2(32767);
BEGIN
IF p_blob IS NULL THEN
RETURN '';
END IF;

l_len := DBMS_LOB.getlength(p_blob);
l_raw := DBMS_LOB.substr(p_blob, l_len, 1);

-- Decode UTF-8 safely
l_text := UTL_I18N.RAW_TO_CHAR(l_raw, 'AL32UTF8');

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