SQL> create table t (acct_create_date varchar2(14));

Table created.

SQL> declare
  2    form1 constant varchar2(16) := 'yyyymmddhh24miss';
  3    form2 constant varchar2(16) := 'ssmihh24ddmmyyyy';
  4  begin
  5    for i in 1..10 loop
  6      insert into t 
  7      values (to_char(sysdate-dbms_random.value(0,1000),
  8                      decode(mod(i,2),1,form1,form2)));
  9    end loop;
 10    for i in 1..10 loop
 11      insert into t 
 12      values (trunc(dbms_random.value(20000000,20001000))||
 13               trunc(dbms_random.value(121980,122008)));
 14    end loop;
 15  end;
 16  /

PL/SQL procedure successfully completed.

SQL> select * from t;
ACCT_CREATE_DA
--------------
20060927091915
07041023022006
20070907143554
26291122012006
20050908213917
00120809072007
20051021201027
24561624032008
20080423020510
19300503092007
20000642121983
20000007121990
20000779122002
20000504121980
20000959121998
20000312122005
20000823121983
20000072122001
20000917121995
20000551122005

20 rows selected.

SQL> create or replace type tp is table of varchar2(250);
  2  /

Type created.

SQL> create or replace function f return tp pipelined
  2  is
  3    date1 date;
  4    date2 date;
  5    form1 constant varchar2(16) := 'yyyymmddhh24miss';
  6    form2 constant varchar2(16) := 'ssmihh24ddmmyyyy';
  7    err1 exception;
  8    err2 exception;
  9    err3 exception;
 10    err4 exception;
 11    err5 exception;
 12    err6 exception;
 13    err7 exception;
 14    err8 exception;
 15    err9 exception;
 16    errA exception;
 17    pragma exception_init(err1, -1858); -- Non numeric value
 18    pragma exception_init(err2, -1830); -- Too long value
 19    pragma exception_init(err3, -1840); -- Too short value
 20    pragma exception_init(err4, -1841); -- Invalid year
 21    pragma exception_init(err5, -1843); -- Invalid month
 22    pragma exception_init(err6, -1847); -- Invalid day
 23    pragma exception_init(err7, -1850); -- Invalid hour
 24    pragma exception_init(err8, -1851); -- Invalid minute
 25    pragma exception_init(err9, -1852); -- Invalid second
 26    pragma exception_init(errA, -1839); -- Invalid day for month
 27    
 28  begin
 29    for rec in (select rowid, t.* from t) loop
 30      date1 := null; date2 := null;
 31      begin
 32        date1 := to_date (rec.acct_create_date, form1);
 33      exception
 34        when err1 or err2 or err3 or err4 or err5 or 
 35              err6 or err7 or err8 or err9 or errA
 36          then null;
 37      end;
 38      begin
 39        date2 := to_date (rec.acct_create_date, form2);
 40      exception
 41        when err1 or err2 or err3 or err4 or err5 or 
 42              err6 or err7 or err8 or err9 or errA
 43          then null;
 44      end;
 45      -- Assuming dates must be between 01/01/1980 and sysdate
 46      if not date1 between to_date('01/01/1980','DD/MM/YYYY') and sysdate then
 47        date1 := null;
 48      end if;
 49      if not date2 between to_date('01/01/1980','DD/MM/YYYY') and sysdate then
 50        date2 := null;
 51      end if;
 52      case
 53        when date1 is not null and date2 is not null then
 54          pipe row (rec.rowid||' '||rec.acct_create_date||' both formats are valid');
 55        when date1 is null and date2 is null then
 56          pipe row (rec.rowid||' '||rec.acct_create_date||' both formats are invalid');
 57        else 
 58          pipe row (rec.rowid||' '||rec.acct_create_date||' is '||
 59                    to_char(nvl(date1,date2),'DD/MM/YYYY HH24:MI:SS'));
 60      end case;
 61    end loop;
 62  end;
 63  /

Function created.

SQL> set arraysize 1
SQL> select * from table(f);
COLUMN_VALUE
-------------------------------------------------------------------------------------------
AAAPFpAAEAAACsXAAA 20060927091915 is 27/09/2006 09:19:15
AAAPFpAAEAAACsXAAB 07041023022006 is 23/02/2006 10:04:07
AAAPFpAAEAAACsXAAC 20070907143554 is 07/09/2007 14:35:54
AAAPFpAAEAAACsXAAD 26291122012006 is 22/01/2006 11:29:26
AAAPFpAAEAAACsXAAE 20050908213917 is 08/09/2005 21:39:17
AAAPFpAAEAAACsXAAF 00120809072007 is 09/07/2007 08:12:00
AAAPFpAAEAAACsXAAG 20051021201027 is 21/10/2005 20:10:27
AAAPFpAAEAAACsXAAH 24561624032008 is 24/03/2008 16:56:24
AAAPFpAAEAAACsXAAI 20080423020510 is 23/04/2008 02:05:10
AAAPFpAAEAAACsXAAJ 19300503092007 is 03/09/2007 05:30:19
AAAPFpAAEAAACsXAAK 20000642121983 both formats are invalid
AAAPFpAAEAAACsXAAL 20000007121990 is 07/12/1990 00:00:20
AAAPFpAAEAAACsXAAM 20000779122002 both formats are invalid
AAAPFpAAEAAACsXAAN 20000504121980 is 04/12/1980 05:00:20
AAAPFpAAEAAACsXAAO 20000959121998 both formats are invalid
AAAPFpAAEAAACsXAAP 20000312122005 both formats are valid
AAAPFpAAEAAACsXAAQ 20000823121983 is 23/12/1983 08:00:20
AAAPFpAAEAAACsXAAR 20000072122001 both formats are invalid
AAAPFpAAEAAACsXAAS 20000917121995 is 17/12/1995 09:00:20
AAAPFpAAEAAACsXAAT 20000551122005 both formats are invalid

20 rows selected.