Oracle 18c

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

CREATE TABLE t(id,col) AS
SELECT 1,'SEP-10-2017' FROM dual UNION ALL
SELECT 2,'SEP 30 2018' FROM dual UNION ALL
SELECT 3,'OFFICE OF SMALL' FROM dual UNION ALL
SELECT 4,'OCT-11-2018' FROM dual UNION ALL
SELECT 5,'09-SEP-2009' FROM dual UNION ALL
SELECT 6,'Not Applicable' FROM dual UNION ALL
SELECT 7,'NOV-20-2001' FROM dual UNION ALL
SELECT 8,'BANIJYA BHIBAG' FROM dual UNION ALL
SELECT 9,'AUGUST 03 2017' FROM dual UNION ALL
SELECT 10,'AUG-04-1991' FROM dual UNION ALL
SELECT 11,'97/2015' FROM dual UNION ALL
SELECT 12,'09/09/2018' FROM dual
12 rows affected

CREATE TABLE t2(id INT,col VARCHAR2(50))
DECLARE
  dt DATE;
BEGIN
  FOR c IN (SELECT * FROM t) LOOP
    BEGIN
      dt := TO_DATE(c.col, 'MON-DD-YYYY', 'NLS_DATE_LANGUAGE=ENGLISH');
      INSERT INTO t2 VALUES(c.id,c.col);
    EXCEPTION
      WHEN OTHERS THEN
        BEGIN
          dt := TO_DATE(c.col, 'DD/MM/YYYY', 'NLS_DATE_LANGUAGE=ENGLISH');
          INSERT INTO t2 VALUES(c.id,c.col);
        EXCEPTION
          WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE(c.col || ' is not a valid date value');          
        END;
    END;
  END LOOP;
  COMMIT;
END;
/
1 rows affected

dbms_output:
OFFICE OF SMALL is not a valid date value
Not Applicable is not a valid date value
BANIJYA BHIBAG is not a valid date value
97/2015 is not a valid date value

SELECT * FROM t2


ID
COL


1
SEP-10-2017

2
SEP 30 2018

4
OCT-11-2018

5
09-SEP-2009

7
NOV-20-2001

9
AUGUST 03 2017

10
AUG-04-1991

12
09/09/2018

ID	COL
1	SEP-10-2017
2	SEP 30 2018
4	OCT-11-2018
5	09-SEP-2009
7	NOV-20-2001
9	AUGUST 03 2017
10	AUG-04-1991
12	09/09/2018