How can we extract date only from a column with various date and string formats in Oracle?

From Oracle 12, you do not need PL/SQL and can use:

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;

Which, for the sample data:

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;

Outputs:

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

Note: O9-SEP-2009 has not parsed because you have the letter O rather than the digit 0 as the first character.

db<>fiddle here

One option is to create a function which will cover all possible combinations. The following example has some of them - you'd have to add new when you find them.

SQL> create or replace function f_date (par_str in varchar2)
  2    return date
  3  is
  4    l_date date;
  5  begin
  6    begin
  7      l_date := to_date(par_str, 'mon-dd-yyyy');
  8      return l_date;
  9    exception
 10      when others then null;
 11    end;
 12
 13    --
 14
 15    begin
 16      l_date := to_date(par_str, 'mon dd yyyy');
 17      return l_date;
 18    exception
 19      when others then null;
 20    end;
 21
 22    --
 23
 24    begin
 25      l_date := to_date(par_str, 'dd-mon-yyyy');
 26      return l_date;
 27    exception
 28      when others then null;
 29    end;
 30
 31    --
 32
 33    begin
 34      l_date := to_date(par_str, 'month dd yyyy');
 35      return l_date;
 36    exception
 37      when others then null;
 38    end;
 39
 40    --
 41
 42    begin
 43      l_date := to_date(par_str, 'dd/mm/yyyy');
 44      return l_date;
 45    exception
 46      when others then null;
 47    end;
 48
 49    return null;
 50
 51  exception
 52    when others then
 53      return null;
 54  end f_date;
 55  /

Function created.

Testing:

SQL> select actual, f_date(actual) as expected
  2  from test;

ACTUAL          EXPECTED
--------------- -----------
SEP-10-2017     10-SEP-2017
SEP 30 2018     30-SEP-2018
OFFICE OF SMALL
09-SEP-2009     09-SEP-2009
Not applicable
AUGUST 03 2017  03-AUG-2017
97/2015
09/09/2018      09-SEP-2018

8 rows selected.

SQL>

Possible problems: if actual = 09/10/12, what is what?

• 09 can be day, month or year
• 10 can also be day, month or year
• 12 can also be day, month or year

So, what is it?

• 09-OCT-2012
• 10-SEP-2012
• 12-OCT-2009
• ...

If you're sure about that you don't have any other type of the format for those values which can be convertible to a date value(eg.all of the data conform with this sample data set), then use the following code block in order to populate another table(t2) with the decent date values while displaying the rotten values such as

SQL> SET SERVEROUTPUT ON
SQL> 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;
/

Demo