Correct Date Formats When Entered in Oracle Forms

A HunBug Document
Document No.: 11600
Forms Version: 9i +
Last Updated: 01 Jun 2009
Author: HunBug

This article explains how to correct a date format, from that entered to that displayed.

NB: This article assumes that the displayed date item has the format mask "DD Mon YYYY" and corrects "MMDDRRRR" or "MM/DD/RRRR" when American language settings are used. When other language settings are used, such as United Kingdom, then "DDMMRRRR" or "DD/MM/RRRR" will be corrected.

The Function

This is a Forms function and can either sit in the form program units or a library.

FUNCTION lfn_dateFormatCorrection RETURN BOOLEAN IS

  v_charDate   VARCHAR2(30) := NAME_IN('SYSTEM.CURSOR_VALUE');
  v_dateDate   DATE;
  v_return     BOOLEAN := FALSE;
    
  FUNCTION convertDate(p_mask IN VARCHAR2) RETURN BOOLEAN IS
  BEGIN
    v_dateDate := TO_DATE(v_charDate,p_mask);
    COPY(TO_CHAR(v_dateDate,'DD Mon YYYY'),NAME_IN('SYSTEM.CURSOR_ITEM'));
    RETURN(TRUE);
  EXCEPTION
  WHEN OTHERS THEN
    RETURN(FALSE);
  END convertDate;

BEGIN

  IF INSTR(GET_APPLICATION_PROPERTY(USER_NLS_LANG),'AMERICAN') != 0 THEN
    IF convertDate('MMDDRRRR') THEN
      v_return := TRUE;
    ELSIF convertDate('MM/DD/RRRR') THEN
      v_return := TRUE;
    END IF;
  ELSE
    IF convertDate('DDMMRRRR') THEN
      v_return := TRUE;
    ELSIF convertDate('DD/MM/RRRR') THEN
      v_return := TRUE;
    END IF;
  END IF;
 
  RETURN(v_return);

END;
 

Integrating with ON-ERROR

The function needs to be called from the ON-ERROR trigger, so that when a date format error is raised the function is called to correct the date. Most applications have standardised ON-ERROR triggers, specific to the application, so the following code extract would need to be adapted to your own ON-ERROR trigger.

...

IF ERROR_CODE IN (50002,50026) THEN
  IF NOT lfn_dateFormatCorrection THEN
    RAISE FORM_TRIGGER_FAILURE; --// Handle as appropiate for your on-error trigger.
  END IF;
END IF;

...

How it Works

When the date is entered incorrectly, an error 50002 or 50026 is raised. The ON-ERROR trigger handles this and calls the function.

After getting the cursor value of the date item, the function checks whether the USER_NLS_LANG is AMERICAN is not.

if it is AMERICAN, then the function calls the sub function convertDate. This tries to convert the date with the format mask "MMDDRRRR", so 06012009 would return 01 Jun 2009. As it uses RRRR for the year 060109 would also be corrected. If the value from the form date item will not convert to a date with this mask, the TO_DATE call will raise an exception and convertDate will return false. If false is returned the function goes on to check whether the user entered seperators, such as "06/01/2009". Again convertDate is called and returns true or false.

If AMERICAN language settings are not used, the same procedure is followed except the day and month positions are switched.

Further Reading



HunBug Store








Comments are all individually read, none are automatically posted to the site.