Re: Forms 3 Year 2000 Bug (Solution)

calderproj wrote:

> Is there anybody else out there come across a bug in Forms 3 which allows
> the entry of 0000 in the year element of a date.
>
> ie 13-FEB-0000.
>
> Forms accepts this as a valid date and stores it onto the database as
> 13-FEB-0000. However on retrieval back into Forms the date is shown as
> null.
>
> A serious problem which Oracle say is too complicated to resolve in the
> twilight years of Forms 3.
>
> There must be others out there which still use Forms 3 who have
> discovered this problem. If so I would like to hear your views.
>
> Disgruntled of Halifax.

Yes, we've been handling this one since 1996.You will find if you check using SQL*Plus that the date is actually stored in the database as 13-FEB-00.

To get around this problem do the following.

1.) Ensure that the date format mask for the field is set to DD-MON-YYYY

     e.g in the .INP file

       INPUT_MASK = DD-MON-YYYY
       OUTPUT_MASK = DD-MON-YYYY

2.) Use a date validation procedure to do something like the following:

   DEFINE PROCEDURE

      NAME = val_format_date
      DEFINITION = <<<
      procedure val_format_date (dte in out date) is
      yr number;
      begin
      if dte is not null then
       yr := to_number(to_char(dte,'YY'));
      --
      -- Convert date to DD-MON-YYYY format
      --
       if length(to_char(dte)) = 9 then
        if yr between 90 and 99 then
         dte := to_date(to_char(dte,'DD-MON')||'-19'||
                 to_char(dte,'YY'),'DD-MON-YYYY');
        elsif yr between 0 and 20 then
         dte := to_date(to_char(dte,'DD-MON')||'-20'||
                to_char(dte,'YY'),'DD-MON-YYYY');
        else trig_fail('*E* Date must between 01-JAN-1990 and 31-DEC-2020.');
        end if;
      --
      -- Check date between 01-JAN-1990 and 31-DEC-2020
      --
       else
        if dte between '01-JAN-1990' and '31-DEC-2020' then
              null;
          else trig_fail('*E* Date must between 01-JAN-1990 and 31-DEC-2020.');
        end if;
       end if;
      end if;
      end;
      >>>

   ENDDEFINE PROCEDURE  3.) In the ON-VALIDATE-FIELD trigger for the field use something like the following:

         DEFINE TRIGGER

            NAME = ON-VALIDATE-FIELD
            TRIGGER_TYPE = V3
            TEXT = <<<
            if :block.date_field is not null then
               val_format_date(:block.date_field);
            end if;
            >>>

         ENDDEFINE TRIGGER

Hope this helps.

I would have thought that Oracle knew about this sort of fix by now, however they are probably not supporting forms 3 anymore (even though it can be made to be millenium compliant), taking the 'fixed in next release - please upgrade' point of view.

--
+---------------------------------------------------------------------+
 Edwin Robson.  Information Services.
 Fujitsu Telecommunications Europe Ltd, Birmingham, B37 7YU. (UK).
 Tel:  +44 (0)121 717 6426  Fax:  +44 (0)121 717 6018
 E-mail: E.Robson_at_ftel.co.uk (Work)
         edwin_at_pandemonium.demon.co.uk (Home)
 WWW:    http://www.pandemonium.demon.co.uk/
+---------------------------------------------------------------------+