Oracle 11g Release 2

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

CREATE TABLE test_data ( id, value ) AS
SELECT 1, '1550226213Bharath
1550226559LakshmanUpdate to Current Summary: Under Investigation
1550227918SitaUpdate to Current Summary: Outage restored- Under Observation
1550301176Rama' FROM DUAL UNION ALL
SELECT 2, '0ABC' FROM DUAL UNION ALL
SELECT 3, NULL FROM DUAL UNION ALL
SELECT 4, '1234567890A
1234567891B
1234567892C' FROM DUAL;
4 rows affected

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
WITH lines ( id, value, unix_time, description, line_no, total_lines ) AS (
  SELECT id,
         value,
         TO_NUMBER( REGEXP_SUBSTR( value, '^\d+', 1, 1, 'm' ) ),
         REGEXP_SUBSTR( value, '^\d+(.*)$', 1, 1, 'm', 1 ),
         1,
         COALESCE( REGEXP_COUNT( value, '^\d+', 1, 'm' ), 0 )
  FROM   test_data
UNION ALL
  SELECT id,
         value,
         TO_NUMBER( REGEXP_SUBSTR( value, '^\d+', 1, line_no + 1, 'm' ) ),
         REGEXP_SUBSTR( value, '^\d+(.*)$', 1, line_no + 1, 'm', 1 ),
         line_no + 1,
         total_lines
  FROM   lines
  WHERE  line_no < total_lines
)
SELECT id,
       DATE '1970-01-01' + unix_time * INTERVAL '1' SECOND AS time,
       description
FROM   lines
ORDER BY id, line_no;


ID
TIME
DESCRIPTION


1
2019-02-15 10:23:33
Bharath

1
2019-02-15 10:29:19
LakshmanUpdate to Current Summary: Under Investigation

1
2019-02-15 10:51:58
SitaUpdate to Current Summary: Outage restored- Under Observation

1
2019-02-16 07:12:56
Rama

2
1970-01-01 00:00:00
ABC

3
null
null

4
2009-02-13 23:31:30
A

4
2009-02-13 23:31:31
B

4
2009-02-13 23:31:32
C

ID	TIME	DESCRIPTION
1	2019-02-15 10:23:33	Bharath
1	2019-02-15 10:29:19	LakshmanUpdate to Current Summary: Under Investigation
1	2019-02-15 10:51:58	SitaUpdate to Current Summary: Outage restored- Under Observation
1	2019-02-16 07:12:56	Rama
2	1970-01-01 00:00:00	ABC
3	null	null
4	2009-02-13 23:31:30	A
4	2009-02-13 23:31:31	B
4	2009-02-13 23:31:32	C