blob11

CREATE OR REPLACE FUNCTION blob_to_text(p_blob BLOB) RETURN VARCHAR2 IS
l_text VARCHAR2(32767);
l_raw RAW(1000); -- Only take first 1000 bytes
l_blob_length NUMBER;
BEGIN
IF p_blob IS NULL THEN
RETURN '';
END IF;

l_blob_length := DBMS_LOB.GETLENGTH(p_blob);

IF l_blob_length IS NULL OR l_blob_length = 0 THEN
RETURN '';
END IF;

-- ALWAYS take only first 1000 bytes to be safe
-- This prevents any possible overflow
l_raw := DBMS_LOB.SUBSTR(p_blob, 1000, 1);

BEGIN
-- Try conversion but limit output length
l_text := UTL_RAW.CAST_TO_VARCHAR2(UTL_RAW.CONVERT(l_raw, 'AL32UTF8', 'AL32UTF8'));
l_text := SUBSTR(l_text, 1, 4000); -- Double safety limit
EXCEPTION
WHEN OTHERS THEN
-- If conversion fails, return size info
l_text := '[BINARY: ' || l_blob_length || ' bytes]';
END;

-- Add truncation notice if original was larger
IF l_blob_length > 1000 THEN
l_text := SUBSTR(l_text, 1, 3900) || '... [TRUNCATED: ' || l_blob_length || ' bytes]';
END IF;

RETURN l_text;

EXCEPTION
WHEN OTHERS THEN
RETURN 'ERROR: ' || l_blob_length || ' bytes';
END blob_to_text;
/