Xref: alice comp.databases.oracle.tools:14219
Path: alice!news-feed.fnsi.net!priori!netnews.com!feed2.news.erols.com!erols!not-for-mail
From: "Peter H. Larsen" <plarsen.nospam@dc.dynares.com>
Newsgroups: comp.databases.oracle.tools
Subject: Re: Y2k and d2k
Date: Tue, 07 Jul 1998 15:40:01 -0400
Organization: Dynamic Resources Inc.
Lines: 113
Message-ID: <35A27991.FE3D74CD@dc.dynares.com>
References: <01bda4fe$2c3d3860$1201010a@Kent.national.draeger.com> <1998070200544500.UAA16481@ladder03.news.aol.com> <6nt2le$bbf$1@nclient5-gui.server.virgin.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Trace: winter.news.erols.com 899840525 1090 207.96.14.31 (7 Jul 1998 19:42:05 GMT)
X-Complaints-To: abuse@erols.com
X-Mailer: Mozilla 4.05 [en] (Win95; I)

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
@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@cheerful.com
> =============================
> 
> DanHW wrote in message <1998070200544500.UAA16481@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@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.