select3

SELECT LISTAGG(chunk_content, '') WITHIN GROUP (ORDER BY chunk_num) as full_text
FROM (
SELECT
c.chunk_num,
blob_to_text_range(t.VERSIONS, c.start_byte, c.end_byte) as chunk_content
FROM CacheIOIStates t
CROSS JOIN (
SELECT
level as chunk_num,
(level * 4000 - 3999) as start_byte,
(level * 4000) as end_byte
FROM dual
CONNECT BY level <= CEIL(
(SELECT blob_length(VERSIONS) FROM CacheIOIStates WHERE IOIID = 123) / 4000
)
) c
WHERE t.IOIID = 123
ORDER BY c.chunk_num
)
WHERE chunk_content IS NOT NULL;