Re: Y2K database check

From: Suresh Bhat <suresh.bhat_at_mitchell-energy.com>
Date: Wed, 22 Dec 1999 12:06:32 -0600
Message-ID: <Wq884.1617$y3.67269@insync>

Hi,

If you mean the programs that the developers have fixed, that are working properly then I have the following script that you can use.

It will check all tables for date type columns and the dates that are prior to 01-jan-1905(Can be changed interactively) or beyond 31-dec-2049. Of course this may not work for all the enterprises. Our company's dates do not go prior to 1940, therefore, 1905 is a good indication of problem with programs. Another would be 0000 or 0099 etc., which is usually a problem with using 2 digit year in SQL*Forms or may be even Oracle forms.

You need to have select privileges to all the tables to use this script, otherwise you may have to modify it to add and owner = ' ' in the where clause.

Here we go

set doc off pause off
/*

   Author     : Suresh N. Bhat
   Date       : 12/10/1999
   File Name  : $HOME/dba_scripts/check_y2k_compliance.sql

   Description: Check if correct dates are being entered in DATE type
                columns in all tables in the year 2000.

   Input Paramaeter:
                Lower Date Range, default is 01-JAN-1905.  The report
                will list all dates that are prior to January 1, 1905
                and beyond December 31, 2049.

   Output Files:$HOME/suresh1.sql.  This temporary file is creatred and
                then removed after the report is created.

                Report file is: $HOME/rep/check_y2k_compliance.txt

   Restrictions:Tables owned by SYS, SYSTEM, certain Large tables and tables
                with valid dates are excluded from the listing.  Check
                the WHERE clause.

*/

set heading off feedback off verify off set pagesize 4000

column line1  fold_a
column line2  fold_a
column line3  fold_a
column line4  fold_a
column line5  fold_a
column line6  fold_a
column line7  fold_a

column begin_date new_value begin_date

accept begin_date char prompt 'Enter Lower Date Range [01-jan-1905]: '

set termout off

select nvl( upper('&begin_date'), '01-JAN-1905') begin_date   from dual
/

set termout on

prompt
prompt This Script will list the DATE TYPE COLUMNS in all Tables where the prompt date values are less than &begin_date or greater then 31-DEC-2049. prompt
prompt Please Wait ... This may take a while. prompt When finished look for the file: $HOME/rep/check_y2k_compliance.txt prompt

set termout off

spool $HOME/suresh1.sql

prompt set doc off pause off heading off feedback off prompt set pagesize 4000
prompt break on line0
prompt alter session set nls_date_format = 'dd-MON-yyyy' prompt/
prompt spool $HOME/rep/check_y2k_compliance.txt

select
'select'
line1,
'''TABLE: '||c.table_name||' COLUMN: '||c.column_name||' '' line0,' line2,

        ' '||c.column_name
line3,

        ' from '||c.table_name
line4,
' where '||column_name||' != to_date(''01-JAN-1901'', ''dd-MON-yyyy'')' line5,
' and '||column_name||' != to_date(''01-JAN-1900'', ''dd-MON-yyyy'')' line6,
' and ('||column_name||' < to_date(''&begin_date'', ''dd-MON-yyyy'')' line7,
' or '||column_name||' > to_date(''31-DEC-2049'', ''dd-MON-yyyy''));'   from all_tab_columns c,

        all_tables t

 where  c.data_type  = 'DATE'
   and  c.table_name = t.table_name
   and  t.table_name not like '%ARCH'
   and  t.table_name not like '%HIST'
   and  t.table_name not like 'ROW%'
   and  t.table_name != 'TEST'
   and  t.owner      not in   ('SYS','SYSTEM','OPS$SEN','OPS$BRASS','SCOTT')

/

prompt spool off
@$HOME/suresh1.sql
host rm $HOME/suresh1.sql
spool off

exit

<junlei_at_my-deja.com> wrote in message news:83odfs$1s7$1_at_nnrp1.deja.com...

> Does anyone have scripts to check if databases are working coming Y2K?
>
> Thanks.
>
> Junlei
>
>
> Sent via Deja.com http://www.deja.com/

> Before you buy.

Received on Wed Dec 22 1999 - 12:06:32 CST