Check Y2K Dates - Example for Dynamic SQL
Check the date datatype storage in all tables for possible bad values. The script identifies the table name and column which have date values that do not belong to 20th century. This may be useful if your application does not provide correct format mask while accepting date data and oracle stores them with a different century. With a little modification, this script could be used to verify the date values or other values based on the where condition you specify. This script uses UTL_FILE, an Oracle supplied package to write the output file.
declare
/* Identify tables which have Date columns */
cursor ccols is
select owner, table_name, column_name
from dba_tab_columns
where data_type = 'DATE'
and owner not in ('SYS', 'SYSTEM');
dyna_cursor INTEGER;
dyna_return INTEGER;
dyna_string VARCHAR2 (500);
counts NUMBER;
mindate DATE;
outfilename VARCHAR2 (20) := 'y2ktabs.out';
outfiletype UTL_FILE.FILE_TYPE;
begin
/* Select the database name to prepare file name */
select 'y2k.' || lower(substr(global_name,1,8)) into outfilename
from global_name;
/* Open file to write output */
outfiletype := UTL_FILE.FOPEN ('/tmp', outfilename, 'w');
for rcols in ccols loop
/* Build dynamic sql to find the minimum date value */
dyna_cursor := dbms_sql.open_cursor;
dyna_string := 'select nvl(count(' || rcols.column_name || '),0), min(' || rcols.column_name ||
') from ' || rcols.owner ||'.'|| rcols.table_name || ' where ' ||
rcols.column_name || ' < to_date(''01011901'',''ddmmyyyy'')';
dbms_sql.parse (dyna_cursor, dyna_string, dbms_sql.v7);
dbms_sql.define_column (dyna_cursor, 1, counts);
dbms_sql.define_column (dyna_cursor, 2, mindate);
dyna_return := dbms_sql.execute (dyna_cursor);
if dbms_sql.fetch_rows (dyna_cursor) > 0 then
dbms_sql.column_value (dyna_cursor, 1, counts);
dbms_sql.column_value (dyna_cursor, 2, mindate);
end if;
if counts > 0 then
utl_file.put_line (outfiletype, rpad((rcols.owner ||'.'|| rcols.table_name ||'.'||
rcols.column_name),50)|| to_char(mindate,'DD-MON-YYYY ') || to_char(counts,'9999999999'));
utl_file.fflush (outfiletype);
end if;
dbms_sql.close_cursor (dyna_cursor);
end loop;
utl_file.fclose (outfiletype);
end;
/
 Biju Thomas is Oracle7.3 OCP, Oracle8 OCP, Oracle8i OCP and Oracle9i OCA/OCP Certified DBA |
Questions/Comments? Write to webmaster@bijoos.com. © The scripts, tips and articles appearing on BijooS.com cannot be reproduced elsewhere without the prior permission from the webmaster. |