blob15

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, 32767) LOOP
EXIT WHEN LENGTH(l_result) >= 32700;

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')
  );

  -- Apply your original CHR(1) replacement logic
  IF l_single_char = CHR(1) THEN
    l_single_char := '|';
  END IF;

  l_result := l_result || l_single_char;

EXCEPTION
  WHEN OTHERS THEN
    l_result := l_result || '?';
END;

END LOOP;

IF l_blob_len > 32767 OR LENGTH(l_result) >= 32700 THEN
l_result := l_result || '... [TRUNCATED]';
END IF;

RETURN l_result;
END blob_to_text;
/