blob14

CREATE OR REPLACE FUNCTION blob_to_text(p_blob BLOB) RETURN VARCHAR2 IS
l_result VARCHAR2(32767);
l_buffer RAW(32767);
l_text_chunk VARCHAR2(32767);
l_pos INTEGER := 1;
l_blob_len INTEGER;
l_chunk_size BINARY_INTEGER := 8000; -- Conservative for UTF-8 expansion
BEGIN
IF p_blob IS NULL THEN
RETURN '';
END IF;

l_blob_len := DBMS_LOB.GETLENGTH(p_blob);
l_result := '';

WHILE l_pos <= l_blob_len LOOP
-- Calculate safe chunk size considering UTF-8 expansion
l_chunk_size := LEAST(8000, l_blob_len - l_pos + 1);
l_buffer := DBMS_LOB.SUBSTR(p_blob, l_chunk_size, l_pos);

-- Your exact original conversion logic
l_text_chunk := REPLACE(
  UTL_RAW.CAST_TO_VARCHAR2(
    UTL_RAW.CONVERT(l_buffer, 'AL32UTF8', 'AL32UTF8')
  ), 
  CHR(1), 
  '|'
);

-- Check if we can add this chunk
IF LENGTH(l_result) + LENGTH(l_text_chunk) > 32767 THEN
  l_result := SUBSTR(l_result || l_text_chunk, 1, 32767);
  l_result := SUBSTR(l_result, 1, 32600) || '... [TRUNCATED]';
  EXIT;
ELSE
  l_result := l_result || l_text_chunk;
END IF;

l_pos := l_pos + l_chunk_size;

END LOOP;

RETURN l_result;

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