By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
WITH yourTable AS (
SELECT 'HOSTNAME: sample.com INCIDENT TYPE: SampleSiteUpDown SOURCED FROM External External ALARM ID: 57536' AS msg FROM dual
)
SELECT SUBSTR(msg,
INSTR(msg, 'INCIDENT TYPE:') + 15,
INSTR(msg, 'ALARM ID:') - INSTR(msg, 'INCIDENT TYPE:') - 16) AS incident
FROM yourTable;
| INCIDENT |
|---|
| SampleSiteUpDown SOURCED FROM External External |