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:
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;