Oracle 18c

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

CREATE TABLE table_name (ACTUAL) AS
SELECT 'SEP-10-2017'     FROM DUAL UNION ALL
SELECT 'SEP 30 2018'     FROM DUAL UNION ALL
SELECT 'OFFICE OF SMALL' FROM DUAL UNION ALL
SELECT 'OCT-11-2018'     FROM DUAL UNION ALL
SELECT 'O9-SEP-2009'     FROM DUAL UNION ALL
SELECT 'Not Applicable'  FROM DUAL UNION ALL
SELECT 'NOV-20-2001'     FROM DUAL UNION ALL
SELECT 'BANIJYA BHIBAG'  FROM DUAL UNION ALL
SELECT 'AUGUST 03 2017'  FROM DUAL UNION ALL
SELECT 'AUG-04-1991'     FROM DUAL UNION ALL
SELECT '97/2015'         FROM DUAL UNION ALL
SELECT '09/09/2018'      FROM DUAL;
12 rows affected

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';
SELECT actual,
       COALESCE(
         TO_DATE(
           actual DEFAULT NULL ON CONVERSION ERROR,
           'MM-DD-YYYY',
           'NLS_DATE_LANGUAGE=ENGLISH'
         ),
         TO_DATE(
           actual DEFAULT NULL ON CONVERSION ERROR,
           'DD-MON-YYYY',
           'NLS_DATE_LANGUAGE=ENGLISH'
         )
       ) AS parsed
FROM   table_name;


ACTUAL
PARSED


SEP-10-2017
10-SEP-2017

SEP 30 2018
30-SEP-2018

OFFICE OF SMALL
null

OCT-11-2018
11-OCT-2018

O9-SEP-2009
null

Not Applicable
null

NOV-20-2001
20-NOV-2001

BANIJYA BHIBAG
null

AUGUST 03 2017
03-AUG-2017

AUG-04-1991
04-AUG-1991

97/2015
null

09/09/2018
09-SEP-2018

ACTUAL	PARSED
SEP-10-2017	10-SEP-2017
SEP 30 2018	30-SEP-2018
OFFICE OF SMALL	null
OCT-11-2018	11-OCT-2018
O9-SEP-2009	null
Not Applicable	null
NOV-20-2001	20-NOV-2001
BANIJYA BHIBAG	null
AUGUST 03 2017	03-AUG-2017
AUG-04-1991	04-AUG-1991
97/2015	null
09/09/2018	09-SEP-2018