select

WITH ReplayMessageToAscii AS (
  SELECT
    t.ORDERID,
    RTRIM(
      XMLAGG(
        XMLELEMENT(e, chunk_content).EXTRACT('//text()')
        ORDER BY chunk_num
      ).getclobval()
    ) AS full_text
  FROM (
    SELECT
      t.ORDERID,
      c.chunk_num,
      blob_to_text_range(
        t.REPLAYMESSAGE,
        c.start_byte,
        c.end_byte
      ) AS chunk_content
    FROM $TABLE_NAME 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(REPLAYMESSAGE)
         FROM $TABLE_NAME
         WHERE ORDERID = $OKEY) / 4000
      )
    ) c
    WHERE t.ORDERID = $OKEY
  )
  WHERE chunk_content IS NOT NULL
  GROUP BY ORDERID
)
SELECT
  NVL(TO_CHAR(INTERNIDENTITY),'') || '~' ||
  NVL(TO_CHAR(INTERNINSTID),'') || '~' ||
  NVL(TO_CHAR(INTERNVERSION),'') || '~' ||
  NVL(TO_CHAR(format_ts(INTERNTIMESTAMP)),'') || '~' ||
  NVL(TO_CHAR(INTERNRECTYPE),'') || '~' ||
  NVL(TO_CHAR(TRANSACTIONID),'') || '~' ||
  NVL(TO_CHAR(ORDERID),'') || '~' ||
  NVL(TO_CHAR(STOPID),'') || '~' ||
  NVL(TO_CHAR(RIOSTATEEVENT),'') || '~' ||
  NVL(TO_CHAR(EVENTSTATUS),'') || '~' ||
  NVL(TO_CHAR(EVENTTYPE),'') || '~' ||
  NVL(TO_CHAR(ORDERVERSION),'') || '~' ||
  r.full_text || '~' ||
  NVL(TO_CHAR(SYMBOL),'') || '~' ||
  NVL(TO_CHAR(COESSENDERSUBID),'') || '~' ||
  NVL(TO_CHAR(RIOMSGSRC),'') || '~' ||
  NVL(TO_CHAR(RECONDATA),'') || '~' ||
  NVL(TO_CHAR(MORECONDATA),'')
FROM $TABLE_NAME t
JOIN ReplayMessageToAscii r
  ON r.ORDERID = t.ORDERID
WHERE t.ORDERID = $OKEY;