How to normalize DATE using BEFORE INSERT Trigger / PLSQL Procedure

We want to normalise date format which is being inserted into a table / updated / selected (WHERE Clause)

For Db2 there is the possibility to link a user exit (assembler), which checks the passed date format for different patterns and converts it to the "internal" format. The Db2 documentation: Date and time routines - Date and time routines - IBM Documentation and When date and time routines are taken - IBM Documentation

Ideally, we would like to see such a user exit in Oracle as well. We tried it with a trigger (and a function),

Scenarios that should work –

INSERT INTO TEST VALUES (1234, 12345, '1994.11.03', '11.06.1993', current_timestamp);

UPDATE TEST SET RELENR = 9999 WHERE VALDAT = '1994-06-11'

UPDATE TEST SET RELENR = 9999 WHERE VALDAT = '11-06-1993'

SELECT * FROM TEST where VALDAT = '1993-06-11';

SELECT * FROM TEST where VALDAT = '11-06-1993';

TABLE -

CREATE TABLE TEST
   (         "RELENR" NUMBER(7,0) NOT NULL DISABLE,
             "BETER" NUMBER(11,2) NOT NULL DISABLE,
             "VALDAT" DATE NOT NULL DISABLE,
             "CODAT" DATE NOT NULL DISABLE,
             "INVDAT" TIMESTAMP (6) DEFAULT SYSTIMESTAMP NOT NULL DISABLE,
              PRIMARY KEY ("RELENR", "VALDAT", "INVDAT") DISABLE
);

CREATE OR REPLACE TRIGGER date_check
BEFORE INSERT OR UPDATE
ON TEST
FOR EACH ROW
BEGIN
 :NEW.VALDAT := CLEAN_DATE(:NEW.VALDAT);
 :NEW.CODAT := CLEAN_DATE(:NEW.CODAT);
END;
/

INSERTING DATA AFTER TRIGGER IS COMPILED -

INSERT INTO TEST VALUES (1234, 12345, '1993.06.11', '11.06.1993', current_timestamp);

Error starting at line : 1 in command -
INSERT INTO TEST VALUES (1234, 12345, '1994.11.03', '11.06.1993', current_timestamp)
Error at Command Line : 1 Column : 1
Error report -
SQL Error: ORA-01861: De constante komt niet overeen met de notatiestring.
01861. 00000 - "literal does not match format string"
*Cause:   Literals in the input must be the same length as literals in
          the format string (with the exception of leading whitespace). If the
          "FX" modifier has been toggled on, the literal must match exactly,
          with no extra whitespace.
*Action:  Correct the format string to match the literal.

UPDATING DATA AFTER TRIGGER IS COMPILED -

UPDATE TEST SET RELENR = 9999 WHERE VALDAT = '1993-06-11);

Error starting at line : 3 in command -
UPDATE TEST SET RELENR = 9999 WHERE VALDAT = '1993-06-11);
Error at Command Line : 3 Column : 1
Error report -
SQL Error: ORA-01756: String tussen aanhalingstekens niet juist beëindigd.
01756. 00000 - "quoted string not properly terminated"
*Cause:   
*Action:

CLEAN_DATE FUNCTION CODE -

create or replace function clean_date
  ( p_date_str in varchar2)
  return date
is
  l_dt_fmt_nt sys.dbms_debug_vc2coll := sys.dbms_debug_vc2coll
    ('DD-MM-RR', 'MM-DD-RR', 'RR-MM-DD', 'RR-DD-MM'
     , 'DD-MM-YYYY', 'MM-DD-YYYY', 'YYYY-MM-DD', 'YYYY-DD-MM');
  return_value date;
begin
  for idx in l_dt_fmt_nt.first()..l_dt_fmt_nt.last()
  loop
    begin
      return_value := to_date(p_date_str, l_dt_fmt_nt(idx));
      exit;
    exception
       when others then null;
    end;
  end loop;
  if return_value is null then
    raise no_data_found; 
  end if;
  return return_value;
exception
  when no_data_found then
    raise_application_error(-20000, p_date_str|| ' is unknown date format');
end clean_date;
/