Re: Y2k and d2k

Hi,
Your thought of using a trigger is the right solution. Stressed by the number of triggers? Well, use SQL*Plus to generate them all for you. You don't have to program them.

Make a package/procedure that fixes the bug ... by returning the proper date.
Every datefield you have has to set :NEW.<fieldname> := fixbug(:NEW.<fieldname>).

So you create one trigger for each date column in each table ... using the data dictionary, that's easy done ...

• SQL*PLUS script begins here ....

SET PAGESIZE 0 LINESIZE 100 FEEDBACK OFF VERIFY OFF SCAN OFF ECHO OFF SPOOL TMP.SQL SELECT 'CREATE OR REPLACE TRIGGER DF_'||SUBSTR(TABLE_NAME,1,20)||         SUBSTR(COLUMN_NAME,1,10)||' ON '||TABLE_NAME|| ' BEFORE INSERT OR UPDATE OF '||COLUMN_NAME||' FOR EACH ROW BEGIN
  :NEW.'||COLUMN_NAME||' := FIXBUG(:NEW.'||COLUMN_NAME||');' END;'||chr(13)||'/' LINE
from USER_TAB_COLUMNS
WHERE DATA_TYPE = 'DATE'; SPOOL OFF SET PAGESIZE 40 FEEDBACK ON ECHO ON VERIFY ON SPOOL RESULTS.LST
_at_TMP.SQL
SPOOL OFF EXIT

• SQL*PLUS script ends ....

Simple and "dirty" but it works. I havn't checked the syntax for the CREATE TRIGGER command .. somehow I always get FOR EACH ROW and the BEFORE statement switched around - but I think this is correct.

Anyway ... why do manual work when the computer can do all the borring stuff for you?

Hope this helps you out ...
- Peter H. Larsen

Dominique Potter wrote:
>
> Hi,
> Can anyone help with this little problem.
>
> Environment:
> Oracle 7.3, Win95, SQLWindows application.
>
> Constraints: Win95 config cannot change, SQLWindows application cannot
> change.
>
> Problem:
> If a user enters a date for the next century, e.g 2001, the SQLWindows
> application passes back to Oracle the date '1901' (it's a BUG). I need to
> provide a solution that will update ANY dates inserted/updated into Oracle.
> I am not worried about dates prior to 1949, hence I tried to set the
> NLS_DATE_FORMAT to 'DD-MM-RRR' to force Oracle into correcting the century.
> This will work if a user enters a two digit century, but not with four digit
> centuries.
>
> One thought would be to create a 'before insert trigger' on every date field
> in every table. This trigger would read the date columns and correct it if
> the century is incorrect. Problem is I have approx two hundred tables with
> *lots* of date fields.
>
> Does anyone have any thoughts on this ? Any help would be really
> appreciated.
> Thanks
>
> Dominique
>
> Dom_at_cheerful.com
> =============================
>
> DanHW wrote in message <1998070200544500.UAA16481_at_ladder03.news.aol.com>...
> >>I need to make some forms y2k compiant and I wondered if I am covering all
> >>the bases
> >>by making date fields in reports 4 character, and in reports, changing all
> >>yys to RRs in
> >>item properties. Does anyone have experience here. What about triggers?
> >>
> >>thanks,
> >>
> >>sean
> >
> >Oracles position is that if you store date data in the DATE datatype, the
> dates
> >are ok. What you do with them in triggers, etc may of course violate that
> >somehow. I do not use the RR format, I just use the YYYY format everywhere
> so
> >there is no question.
> >
> >Dan Hekimian-Williams

-- 

Peter H. Larsen, Oracle and Applications specialist
Dynamic Resources Inc, Alexandria
Email: plarsen_at_dc.dynares.com
(please remove the nospam from the header when replying to this email)

The above views are my own and does not reflect the views of my
employer.