blob19

CREATE OR REPLACE FUNCTION blob_to_text_range(
p_blob BLOB,
p_start_byte NUMBER, -- Starting byte position (1-based)
p_end_byte NUMBER -- Ending byte position
) RETURN VARCHAR2 IS
l_raw_chunk RAW(32767);
l_blob_len INTEGER;
l_chunk_size NUMBER;
l_result VARCHAR2(32767);
BEGIN
-- Return NULL for invalid inputs
IF p_blob IS NULL OR p_start_byte IS NULL OR p_end_byte IS NULL THEN
RETURN NULL;
END IF;

-- Validate range
IF p_start_byte < 1 OR p_start_byte > p_end_byte THEN
RETURN NULL;
END IF;

l_blob_len := DBMS_LOB.GETLENGTH(p_blob);

-- Return NULL if start byte is beyond BLOB length
IF p_start_byte > l_blob_len THEN
RETURN NULL;
END IF;

-- Calculate actual chunk size we can extract
l_chunk_size := LEAST(p_end_byte - p_start_byte + 1, l_blob_len - p_start_byte + 1);

-- Safety limit to prevent excessive memory usage
l_chunk_size := LEAST(l_chunk_size, 10000);

-- Extract the raw byte range from BLOB
l_raw_chunk := DBMS_LOB.SUBSTR(p_blob, l_chunk_size, p_start_byte);

-- Convert to text using your exact conversion logic
BEGIN
l_result := REPLACE(
UTL_RAW.CAST_TO_VARCHAR2(
UTL_RAW.CONVERT(l_raw_chunk, 'AL32UTF8', 'AL32UTF8')
),
CHR(1),
'|'
);

RETURN l_result;

EXCEPTION
WHEN OTHERS THEN
-- If conversion fails, return hex representation for debugging
RETURN 'HEX[' || l_chunk_size || ']:' || RAWTOHEX(l_raw_chunk);
END;

END blob_to_text_range;
/