Thomas Kyte wrote
>Huh? why obviously. To me it means that you have 1900
>in your database, not 2000.
Thanks Thomas, your example takes my worries away :-)
Carl: don't scare us again, letting us believe that Oracle support has told
you to use RR in your select statement...
>SQL> insert into t values ( to_date( '01-JAN-1900' ) );
Carl: if you want to try this as well, then make sure that your
NLS_DATE_FORMAT supports yyyy, or use
insert into t values ( to_date( '01-JAN-1900', 'dd-mon-yyyy' ) );
>looks like you might already have bad data in your database
Carl: you might want to run the script below to check for that. Note that it
takes a long time to run, so you might want to run it outside production
hours. Also note that CC returns the century, which is 20 for 1999!
Arjan.
- File : sql/check_dates.sql
--
- Application : General Y2000 date check
--
- Description : Check of centuries in all user date columns.
--
- Creates and fills the table y2000_date_checks.
- All tables that belong to the current user will
- be checked for centuries other than 20 and 21
- in all date columns.
--
- Date 31-DEC-4712 is the maximum possible value
- for older versions of Oracle, and does not
- necessarily indicate a Y2000 problem.
--
- As this script might encounter invalid views or
- other errors, and as the checks may take a very
- long time to finish, results are stored in a
- table. This way, the script can be restarted
- whenever needed. This table can also be used to
- check the progress of the scrip, as all changes
- are committed directly.
--
- Syntax : check_dates
--
- History : Arjan van Bentem 01-Aug-1997 Initial Issue
--
- The table might already exist due to restart of this script:
--
set serveroutput on
whenever sqlerror continue
--
create table y2000_date_checks
( check_date date not null
, table_name varchar2(132)
, column_name varchar2(132)
, check_result varchar2(50)
);
--
whenever sqlerror exit failure
--
- Delete uncompleted checks in case of restart of this script:
--
delete from y2000_date_checks
where check_result = 'Checking';
commit;
--
declare
l_result integer;
--
cursor c_DateCols is
select usercol.table_name, usercol.column_name
from user_tab_columns usercol
where usercol.data_type = 'DATE'
and usercol.table_name != 'Y2000_DATE_CHECKS'
and not exists
( select null
from y2000_date_checks checked
where checked.table_name = usercol.table_name
and checked.column_name = usercol.column_name
and checked.check_result != 'Checking'
);
--
- Dynamic SQL function to execute any query which returns an
- integer value:
--
function ExecQuery( p_query in varchar2)
return integer
is
l_cursorid integer;
l_rowcount integer;
l_result integer;
begin
l_result := null;
--
l_cursorid := dbms_sql.open_cursor;
dbms_sql.parse( l_cursorid, p_query, dbms_sql.native);
dbms_sql.define_column( l_cursorid, 1, l_result);
l_rowcount := dbms_sql.execute_and_fetch( l_cursorid, true);
dbms_sql.column_value( l_cursorid, 1, l_result);
dbms_sql.close_cursor( l_cursorid);
--
if l_result is null then
dbms_output.put_line( 'No result for ' || p_query);
end if;
--
return l_result;
exception
when others then
if dbms_sql.is_open( l_cursorid) then
dbms_sql.close_cursor( l_cursorid);
end if;
raise_application_error
( -20001, 'ExecQuery: error executing ' || p_query
, true
);
end ExecQuery;
--
begin
for r_DateCols in c_DateCols loop
--
insert into y2000_date_checks
( check_date, table_name
, column_name, check_result
)
values
( sysdate, r_DateCols.table_name
, r_DateCols.column_name, 'Checking'
);
commit;
--
- Note that CC returns the century. For example: 20 for 1997
- and 21 for 2001.
--
l_result := ExecQuery
( 'select count(*)'
|| ' from ' || r_DateCols.table_name
|| ' where ' || r_DateCols.column_name || ' is not null'
|| ' and to_char( ' || r_DateCols.column_name || ', ''CC'')'
|| ' not in (''20'', ''21'')'
);
--
if l_result != 0 then
update y2000_date_checks
set check_result = 'Errors: ' || to_char( l_result)
where table_name = r_DateCols.table_name
and column_name = r_DateCols.column_name;
commit;
else
update y2000_date_checks
set check_result = 'OK'
where table_name = r_DateCols.table_name
and column_name = r_DateCols.column_name;
commit;
end if;
end loop;
end;
/
--
- Show the results. Results will be kept in table
- y2000_date_checks for later use:
--
set pagesize 60
column check_result format a10 heading 'Result'
column table_name format a37 heading 'Table'
column column_name format a29 heading 'Column'
select table_name, column_name, check_result
from y2000_date_checks
where check_result = 'OK';
select table_name, column_name, check_result
from y2000_date_checks
where check_result != 'OK';
select check_result, count(*)
from y2000_date_checks
group by check_result;
Received on Tue Mar 02 1999 - 13:42:30 CST
