blob18

CREATE OR REPLACE FUNCTION blob_to_text(p_blob BLOB) RETURN VARCHAR2 IS
l_result VARCHAR2(32767);
l_blob_len INTEGER;
l_raw_data RAW(32767);
BEGIN
IF p_blob IS NULL THEN
RETURN '';
END IF;

l_blob_len := DBMS_LOB.GETLENGTH(p_blob);

-- Use your exact original logic but with hard SUBSTR limit
l_raw_data := DBMS_LOB.SUBSTR(p_blob, LEAST(l_blob_len, 8000), 1);

l_result := SUBSTR(
REPLACE(
UTL_RAW.CAST_TO_VARCHAR2(
UTL_RAW.CONVERT(l_raw_data, 'AL32UTF8', 'AL32UTF8')
),
CHR(1),
'|'
),
1,
32767 -- HARD LIMIT
);

RETURN l_result;

EXCEPTION
WHEN OTHERS THEN
-- Fallback to hex if conversion fails
RETURN SUBSTR(RAWTOHEX(DBMS_LOB.SUBSTR(p_blob, 1000, 1)), 1, 32767);
END blob_to_text;
/