Re: Forms 3 Year 2000 Bug (Solution)
Date: 1998/02/16
Message-ID: <34E85B66.335FEBD6_at_ftel.co.uk>#1/1
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/ +---------------------------------------------------------------------+Received on Mon Feb 16 1998 - 00:00:00 CET