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;
/

Search BijooS.com Exact Match Search      
Home Oracle DBA TKMCE Alumni H1B Info Guestbook

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.