blob3

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

-- Get the smaller of the blob length or 32767 bytes
l_bytes := LEAST(DBMS_LOB.GETLENGTH(p_blob), 32767);

-- Extract raw bytes (safe for UTF-8)
l_raw := DBMS_LOB.SUBSTR(p_blob, l_bytes, 1);

-- Convert the raw bytes to text explicitly in SQL (not PL/SQL buffer)
SELECT CONVERT(UTL_RAW.CAST_TO_VARCHAR2(l_raw), 'AL32UTF8')
INTO l_text
FROM DUAL;

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