select2

SELECT RTRIM(XMLAGG(XMLELEMENT(E, chunk_content).EXTRACT('/text()') ORDER BY chunk_num).GETCLOBVAL()) as full_text
FROM (
    SELECT
        c.chunk_num,
        blob_to_text_range(t.STPTYSSUBGRP, c.start_byte, c.end_byte) as chunk_content
    FROM
        ZOrdDetParties t
    CROSS JOIN LATERAL (
        SELECT
            level as chunk_num,
            (level * 4000 - 3999) as start_byte,
            (level * 4000) as end_byte
        FROM dual
        CONNECT BY level <= CEIL(blob_length(t.STPTYSSUBGRP) / 4000)
    ) c
    WHERE
        t.INTERNSEQUENCE = 1 AND t.ORDERID = '252901546gy'
    ORDER BY c.chunk_num
)
WHERE chunk_content IS NOT NULL;