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;