I had a requirement to validate date entered in a free text field (standard field on a standard form). I have solved it by writing a function to validate the date format entered and called the function from forms personalization.
Here is a function to validate whether date entered is in valid format (‘MM/DD/YYYY’) or not.
What does this function do?
This function assumes that input is in format (‘MM/DD/YYYY’) and negates with sysdate which should either return a positive or a negative number. It goes into exception “ORA-01843: not a valid month” when input is not in expected format.
Function Code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | CREATE OR REPLACE FUNCTION date_format_val_f( p_result_date VARCHAR2) RETURN VARCHAR2 -- -- +========================================================================= -- | Name : DATE_FORMAT_VAL_F -- | Description: Function to validate date entered in Result field of Sample -- | results -- | Author : Shailender Thallam -- +========================================================================= -- IS l_date_diff VARCHAR2(20); -- BEGIN -- --Date Validation -- BEGIN SELECT trunc(sysdate - to_date(p_result_date,'MM/DD/YYYY'),0) INTO l_date_diff FROM dual; EXCEPTION WHEN OTHERS THEN -- l_date_diff := 'FAIL'; -- END; -- RETURN l_date_diff; -- END date_format_val_f; |
Testing:
