CREATE OR REPLACE FUNCTION Is_Date (par_date IN CHAR)
RETURN CHAR
IS
  l_date DATE;
BEGIN
  l_date := TO_DATE(par_date, 'dd.mm.yyyy');
  
  RETURN ('VALID');

EXCEPTION
  WHEN OTHERS THEN
    RETURN ('INVALID');
END;
/

select is_date('30.08.2007') d1, is_date('32.08.2007') d2 from dual;

D1         D2
---------- ----------
VALID      INVALID

SQL> select to_date('32.08.2007', 'dd.mm.yyyy') from dual;
select to_date('32.08.2007', 'dd.mm.yyyy') from dual
               *
ERROR at line 1:
ORA-01847: day of month must be between 1 and last day of month


SQL> select to_date('29.02.2007', 'dd.mm.yyyy') from dual;
select to_date('29.02.2007', 'dd.mm.yyyy') from dual
               *
ERROR at line 1:
ORA-01839: date not valid for month specified