blob10

CREATE OR REPLACE FUNCTION blob_to_text_large(p_blob BLOB) RETURN VARCHAR2 IS
l_text VARCHAR2(32767);
l_raw RAW(32767);
l_blob_length NUMBER;
l_max_safe_bytes NUMBER := 32000; -- Conservative safe limit
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;

-- Handle large BLOBs by truncating to safe size
IF l_blob_length > l_max_safe_bytes THEN
-- Extract only safe portion and indicate truncation
l_raw := DBMS_LOB.SUBSTR(p_blob, l_max_safe_bytes, 1);

BEGIN
  l_text := UTL_RAW.CAST_TO_VARCHAR2(UTL_RAW.CONVERT(l_raw, 'AL32UTF8', 'AL32UTF8'));
  l_text := SUBSTR(l_text, 1, 32000) || '... [TRUNCATED: ' || l_blob_length || ' bytes]';
EXCEPTION
  WHEN OTHERS THEN
    l_text := '[BINARY_DATA_TRUNCATED: ' || l_blob_length || ' bytes]';
END;

ELSE
-- Handle normal-sized BLOBs
l_raw := DBMS_LOB.SUBSTR(p_blob, l_blob_length, 1);

BEGIN
  l_text := UTL_RAW.CAST_TO_VARCHAR2(UTL_RAW.CONVERT(l_raw, 'AL32UTF8', 'AL32UTF8'));
EXCEPTION
  WHEN OTHERS THEN
    l_text := '[BINARY_DATA: ' || l_blob_length || ' bytes]';
END;

END IF;

RETURN l_text;

EXCEPTION
WHEN OTHERS THEN
RETURN 'ERROR: ' || SUBSTR(SQLERRM, 1, 100);
END blob_to_text;
/