blob16

CREATE OR REPLACE FUNCTION blob_to_text(p_blob BLOB) RETURN VARCHAR2 IS
l_result VARCHAR2(32767) := '';
l_single_byte RAW(1);
l_single_char VARCHAR2(4);
l_blob_len INTEGER;
i INTEGER;
BEGIN
IF p_blob IS NULL THEN
RETURN '';
END IF;

l_blob_len := DBMS_LOB.GETLENGTH(p_blob);

FOR i IN 1..LEAST(l_blob_len, 8000) LOOP
-- STRICT length check before concatenation
IF LENGTH(l_result) >= 32700 THEN
EXIT;
END IF;

l_single_byte := DBMS_LOB.SUBSTR(p_blob, 1, i);

BEGIN
  l_single_char := UTL_RAW.CAST_TO_VARCHAR2(
    UTL_RAW.CONVERT(l_single_byte, 'AL32UTF8', 'AL32UTF8')
  );

  -- Check if adding this character would exceed limit
  IF LENGTH(l_result) + LENGTH(l_single_char) > 32767 THEN
    EXIT;
  END IF;

  l_result := l_result || l_single_char;

EXCEPTION
  WHEN OTHERS THEN
    -- Only add replacement if we have space
    IF LENGTH(l_result) < 32767 THEN
      l_result := l_result || '?';
    ELSE
      EXIT;
    END IF;
END;

END LOOP;

RETURN l_result; -- No truncation message to avoid overflow

EXCEPTION
WHEN OTHERS THEN
-- Return whatever we have without concatenation
RETURN SUBSTR(l_result, 1, 32767);
END blob_to_text;
/