Re: Forms 3.0, Rpt and Year 2000
Date: Sun, 05 Jul 1998 23:26:40 +0200
Message-ID: <359FEF90.9507F194_at_xs4all.nl>
Henk de Wilde schreef:
> On Sat, 04 Jul 1998 13:14:37 GMT, ironmtn_at_my-dejanews.com wrote:
>
> >I am also looking for the same information. There is some information on
> >Oracle's web site (metallink) but it would be helpful to find someone who has
> >done work in this area. I am also looking to upgrade the forms 3.0 to 4.5 (I
> >think via 4.0) - maybe there is a third party software package that can help
> >with this.
> >
> >I will pass on anything interesting and would appreciate anything you can
> >find.
> >
> >Steve
> >
> >In article <359CAB31.A3BD1772_at_deagostini.it>,
> > Andrea.Cannaos_at_deagostini.it wrote:
> >>
> >> Whe have a lot of forms developed with Forms 3.0 and some RPT ; so we
> >> need to make these YEAR 2000 compliant :
> >>
> >> We need hints, bugs and everithing that can help us !!!!!
>
> The principle is to let all date conversions be done in the database
> with one of Oracle's new "RR" format string. As long as the original
> developers used the DATE column type for dates that should solve your
> problem.
>
> Of course, you will have to scan your applications to see what other
> techniques might have been used.
>
> For rpt it is probably sufficient to scan for "TO_DATE", change the
> format mask to the equivalent "RR" mask and then scan all uses of the
> involved variables. If you have less than say 50 rpt sources, or when
> there are many differences in coding style this is probably best done
> by hand.
>
> For Sql*Forms 3 you need two procedures, one to fix all dates without
> a time component, one to fix the ones with a time component. Oracle
> has a white paper somewhere that describes how to build these
> procedures using SELECT... FROM DUAL;. It is possible to work out
> something similar with the PL/SQL string functions that does not need
> to go to the database, and so performs a little better.
>
> One important bug :
> All DATE fields will have to be changed to DATETIME. Else the
> procedure will not help.
>
> Once these procedures have been created all dates will have to be
> subjected to them. Of course this leaves open all instances where a
> date has not been treated as such..
>
> I hope this helped.
>
> --
> Henk de Wilde
Here is a workaround that does the job:
Workaround for datefields with a two-digit year that allows 29 Februari 2000 to be entered. The examples below are for a Field called 'date2 in Block 'b1'.
Change the existing date field to type DATETIME and move it to page 0 so it it not displayed. Change the querylength on this field to 100 and the field length to 11.
Add a new character type non-database field length 9 to sit where the date field previously resided (example called b1.date2_fg).
Add or modify a PRE-QUERY trigger on the relevant block(s) to enable the non-database field for query processing:
DECLARE
-- This trigger will copy the date entered in the -- non-database displayed field to the hidden -- database field using the advanced query '#' option. -- As LIKE is used (DATETIME fields have a time element) the query -- predicate requires a to_char comparison.BEGIN
if :b1.date2_fg is not null
then
copy ('#=date2 and to_char(date2,''dd-mon-yy'') like '''||:b1.date2_fg||'%''','b1.date2'); end if;
END; Add or modify a POST-QUERY trigger on the relevant block(s) to populate the foreground non-database field during query processing:
DECLARE
-- Set global to indicate that form is in QUERY-MODE to stop the
-- ON-VALIDATE-FIELD trigger firing in QUERY-MODE then populate
BEGIN
:global.mode := 'QUERY';
:b1.date2_fg := to_char(:b1.date2,'dd-mon-yy');
END;
Add or modify an ON-VALIDATE-FIELD trigger to the new character field:
DECLARE
-- Does not execute if form is in QUERY-MODE (global.mode) -- this trigger can/should be expanded to validate the syntax af the -- date entered as it is a char field and no automatic date -- format mask checking is available
datevar date;
BEGIN
if :global.mode = 'QUERY'
then
:global.mode := 'NORMAL';
else
select to_date(:b1.date2_fg,'dd-mon-rr') into datevar
from dual;
:b1.date2 := datevar;
:b1.date2_fg := to_char(:b1.date2,'dd-mon-yy'); end if;
END; Add or modify a KEY-STARTUP trigger to initialize the global:
BEGIN
:global.mode := 'NORMAL';
END;
This workaround was issued by the Oracle agent in The Netherlands.
Take notice of the following problems that occured when I used it:
If the Forms 3 files had been converted from version 2 into version3 the pre and post query and the key-startup triggers might exist in V2 style. To modify a V2 style key-startup trigger ad the following line: #EXEMACRO COPY 'NORMAL' INTO :global.mode;
To modify the pre and post query triggers you can make userdefined V3 triggers
that do what is mentioned in the workaround. Execute the new triggers by adding
the following line to the V2 triggers:
#EXEMACRO EXETRG <<user-defined triggername>>;
Both steps mentioned above can be placed in an existing step where an #EXEMACRO statement exists. If such step does not exist, then make one. Do not add the statement to a step which does not contain it. An errormessage will be the result.
If there are more than one datefields in a form, only set the global to NORMAL in
the last ON-VALIDATE-FIELD trigger. The last one in the script that is, not the
last one on your screen. In the other ones replace the line
:global.mode :='NORMAL';
by
NULL;
If you don't do this, all ON-VALIDATE-FIELD triggers but the first will fire in
query-mode.
The code in the ON-VALIDATE-FIELD and the PRE-QUERY triggers can be placed in procedures if you want to. I did it and it increased the performance slightly. Received on Sun Jul 05 1998 - 23:26:40 CEST