blob1

WITH VersionsToAscii AS (
  SELECT
    t.INTERNIDENTITY,
    t.INTERNINSTID,
    t.INTERNVERSION,
    t.INTERNTIMESTAMP,
    t.IOIID,
    RTRIM(
      XMLAGG(
        XMLELEMENT(E, c.chunk_content) ORDER BY c.chunk_num
      ).EXTRACT('//text()').GETCLOBVAL()
    ) AS full_text
  FROM CacheIOIStates t
  -- For each row t, generate chunk rows 1..N where N = CEIL(DBMS_LOB.GETLENGTH(t.VERSIONS)/4000)
  CROSS JOIN LATERAL (
    SELECT level AS chunk_num,
           blob_to_text_range(t.VERSIONS,
                              (level * 4000 - 3999),
                              (level * 4000)) AS chunk_content
    FROM dual
    CONNECT BY level <= CEIL(NVL(DBMS_LOB.GETLENGTH(t.VERSIONS),0) / 4000)
  ) c
  WHERE
    t.INTERNTIMESTAMP = to_oratime_from_syb($TARGET_INTERNTIMESTAMP)
    AND t.IOIID = $OKEY
    AND c.chunk_content IS NOT NULL
  GROUP BY
    t.INTERNIDENTITY,
    t.INTERNINSTID,
    t.INTERNVERSION,
    t.INTERNTIMESTAMP,
    t.IOIID
)
SELECT
    m.INTERNIDENTITY || '~' ||
    m.INTERNINSTID || '~' ||
    m.INTERNVERSION || '~' ||
    format_ts(m.INTERNTIMESTAMP) || '~' ||
    m.INTERNRECTYPE || '~' ||
    m.IOIID || '~' ||
    m.AUTOREFRESH || '~' ||
    m.SYMBOL || '~' ||
    v.full_text || '~' ||
    m.ORDERID || '~' ||
    m.SLICE || '~' ||
    m.EXECUTEDQTY
FROM CACHEIOISTATES m
JOIN VersionsToAscii v
  ON m.IOIID = v.IOIID
 AND m.INTERNTIMESTAMP = v.INTERNTIMESTAMP
-- If you still want to *require* the first-1000-char hash to match, add it:
AND STANDARD_HASH(DBMS_LOB.SUBSTR(v.full_text, 1000, 1)) =
    STANDARD_HASH(UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(m.VERSIONS, 1000, 1)))
WHERE
  m.INTERNTIMESTAMP = to_oratime_from_syb($TARGET_INTERNTIMESTAMP)
  AND m.IOIID = $OKEY;