Oracle 23c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE test (objectid, db_instance_seq, data) AS
  SELECT 1,
         1,
         EMPTY_CLOB()
         || '|RES_GetResData_Public_ScreenPrint - AAAAA|BBBB|CCCC|DDDD|' || CHR(10)
         || 'XXXX|RES_GetResData_Public_ScreenPrint - EEEEE|F||H|I|J|K' || CHR(10)
  FROM   DUAL;
1 rows affected

WITH rsqfc (objectid, db_instance_seq, data, spos, dpos, epos) AS (
  SELECT objectid,
         db_instance_seq,
         data,
         INSTR(data, '|RES_GetResData_Public_ScreenPrint', 1),
         INSTR(data, '|', INSTR(data, '|RES_GetResData_Public_ScreenPrint', 1) + 34, 1),
         INSTR(data, '|', INSTR(data, '|RES_GetResData_Public_ScreenPrint', 1) + 34, 4)
  FROM   test
UNION ALL
  SELECT objectid,
         db_instance_seq,
         data,
         INSTR(data, '|RES_GetResData_Public_ScreenPrint', epos + 1),
         INSTR(data, '|', INSTR(data, '|RES_GetResData_Public_ScreenPrint', epos + 1) + 34, 1),
         INSTR(data, '|', INSTR(data, '|RES_GetResData_Public_ScreenPrint', epos + 1) + 34, 4)
  FROM   rsqfc
  WHERE  spos > 0
  AND    epos > 0
)
SEARCH DEPTH FIRST BY objectid, db_instance_seq SET order_id
SELECT objectid,
       db_instance_seq,
       SUBSTR(data, spos + 1, dpos - spos - 1) AS text
from   rsqfc
WHERE  spos > 0
AND    epos > 0;


OBJECTID
DB_INSTANCE_SEQ
TEXT


1
1
RES_GetResData_Public_ScreenPrint - AAAAA

1
1
RES_GetResData_Public_ScreenPrint - EEEEE

OBJECTID	DB_INSTANCE_SEQ	TEXT
1	1	RES_GetResData_Public_ScreenPrint - AAAAA
1	1	RES_GetResData_Public_ScreenPrint - EEEEE