REM
REM pre_1950_dates.sql
REM ==================
REM
REM This script is provided by Database Specialists, Inc.
REM (http://www.dbspecialists.com) for individual use and not for sale.
REM Database Specialists, Inc. does not warrant the script in any way
REM and will not be responsible for any loss arising out of its use.
REM
REM Your feedback is welcome! Please send your comments about this script
REM to scriptfeedback@dbspecialists.com
REM
REM This script looks at every table in the current schema. For each table
REM with at least one column with the DATE data type, every row of the 
REM table is checked. A tally of all rows with dates prior to January 1, 
REM 1950 is listed. 
REM
REM If you expect that your database contains no dates prior to 1950, this
REM script can clue you in to possibly defective data in your database caused
REM by the Y2K bug.
REM
REM Version 11-05-1999
REM

SET SERVEROUTPUT ON SIZE 1000000
SET TIMING    ON
SET TAB       OFF
SET TRIMSPOOL ON

SPOOL pre_1950_dates.lst

DECLARE
  TYPE t_strings IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
  TYPE t_numbers IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  CURSOR c_tables IS
    SELECT   table_name
    FROM     user_tables
    ORDER BY table_name;
  CURSOR c_date_columns (cp_table_name IN VARCHAR2) IS
    SELECT   column_name
    FROM     user_tab_columns
    WHERE    table_name = cp_table_name
    AND      data_type = 'DATE'
    ORDER BY column_name;
  v_table_count    NUMBER := 0;
  v_pre_1950_dates NUMBER := 0;
  v_pre_1950_tabs  NUMBER := 0;
  v_column_count   NUMBER;
  v_row_count      NUMBER;
  v_pre_1950_rows  NUMBER;
  v_tab_inc        NUMBER;
  v_row_inc        NUMBER;
  v_column_names   t_strings;
  v_pre_1950_vals  t_numbers;
  v_query          VARCHAR2 (10000);
  v_cursor         INTEGER;
  v_dummy          NUMBER;
  v_fetched_date   DATE;
  v_compare_date   DATE := TO_DATE ('01-01-1950', 'mm-dd-yyyy');
BEGIN
  FOR t IN c_tables LOOP
    v_table_count := v_table_count + 1;
    dbms_output.put_line (CHR (9));
    dbms_output.put_line ('Table: ' || t.table_name);
    v_column_count := 0;
    v_query := 'SELECT ';
    FOR c IN c_date_columns (t.table_name) LOOP
      v_column_count := v_column_count + 1;
      v_column_names (v_column_count) := c.column_name;
      v_pre_1950_vals (v_column_count) := 0;
      v_query := v_query || '"' || c.column_name || '", ';
    END LOOP;
    IF v_column_count = 0 THEN
      dbms_output.put_line ('This table has no columns of data type DATE');
    ELSE
      v_tab_inc := 1;
      v_row_count := 0;
      v_pre_1950_rows := 0;
      v_query := SUBSTR (v_query, 1, LENGTH (v_query) -2) || 'FROM "' ||
                 t.table_name || '"';
      v_cursor := dbms_sql.open_cursor;
      dbms_sql.parse (v_cursor, v_query, dbms_sql.native);
      FOR i IN 1..v_column_count LOOP
        dbms_sql.define_column (v_cursor, i, v_fetched_date);
      END LOOP;
      v_dummy := dbms_sql.execute (v_cursor);
      LOOP
        v_dummy := dbms_sql.fetch_rows (v_cursor);
        EXIT WHEN v_dummy = 0;
        v_row_count := v_row_count + 1;
        v_row_inc := 1;
        FOR i IN 1..v_column_count LOOP
          dbms_sql.column_value (v_cursor, i, v_fetched_date);
          IF v_fetched_date < v_compare_date THEN
            v_pre_1950_dates := v_pre_1950_dates + 1;
            v_pre_1950_tabs := v_pre_1950_tabs + v_tab_inc;
            v_pre_1950_rows := v_pre_1950_rows + v_row_inc;
            v_pre_1950_vals (i) := v_pre_1950_vals (i) + 1;
            v_tab_inc := 0;
            v_row_inc := 0;
          END IF;
        END LOOP;
      END LOOP;
      dbms_sql.close_cursor (v_cursor);
      dbms_output.put_line ('Rows in table: ' || TO_CHAR (v_row_count));
      dbms_output.put_line ('Rows with dates prior to 1950: ' ||
                            TO_CHAR (v_pre_1950_rows));
      dbms_output.put_line (CHR (9) || 'Column Name                    ' ||
                            'Dates Prior to 1950');
      dbms_output.put_line (CHR (9) || '------------------------------ ' ||
                            '-------------------');
      FOR i IN 1..v_column_count LOOP
        dbms_output.put_line (CHR (9) || RPAD (v_column_names (i), 30) ||
                              LPAD (TO_CHAR (v_pre_1950_vals (i)), 20));
      END LOOP;
    END IF;
  END LOOP;
  dbms_output.put_line (CHR (9));
  dbms_output.put_line ('Tables examined: ' || TO_CHAR (v_table_count));
  dbms_output.put_line ('Tables with pre-1950 dates: ' || 
                        TO_CHAR (v_pre_1950_tabs));
  dbms_output.put_line ('Total number of pre-1950 dates found: ' || 
                        TO_CHAR (v_pre_1950_dates));
END;
/

SPOOL OFF