loop3

CREATE OR REPLACE FUNCTION blob_length(p_blob BLOB) RETURN NUMBER IS
BEGIN
IF p_blob IS NULL THEN
RETURN 0;
END IF;

RETURN DBMS_LOB.GETLENGTH(p_blob);
END blob_length;
/
CREATE OR REPLACE FUNCTION blob_range_exists(
p_blob BLOB,
p_start_byte NUMBER,
p_end_byte NUMBER
) RETURN VARCHAR2 IS
l_blob_len INTEGER;
BEGIN
IF p_blob IS NULL THEN
RETURN 'NO_BLOB';
END IF;

l_blob_len := DBMS_LOB.GETLENGTH(p_blob);

IF p_start_byte > l_blob_len THEN
RETURN 'BEYOND_END';
ELSIF p_end_byte > l_blob_len THEN
RETURN 'PARTIAL';
ELSE
RETURN 'FULL';
END IF;
END blob_range_exists;
/

CREATE OR REPLACE FUNCTION blob_chunks_count(
p_blob BLOB,
p_chunk_size NUMBER DEFAULT 100
) RETURN NUMBER IS
l_blob_len INTEGER;
BEGIN
IF p_blob IS NULL THEN
RETURN 0;
END IF;

l_blob_len := DBMS_LOB.GETLENGTH(p_blob);

-- Calculate total chunks needed (ceiling division)
RETURN CEIL(l_blob_len / p_chunk_size);
END blob_chunks_count;
/