Andrew Fraser DBA

Randomize Date of Birth Data

CREATE OR REPLACE PROCEDURE dateOfBirth AS
/*
|| Purpose : Fix skewed dates of birth (e.g. 15-Oct-1970).
||           Updates dates of birth to a new date of birth ranging from 25 to 75 years old.
*/
   l_idealNum NUMBER ;
BEGIN
   SELECT ROUND ( COUNT(*) / ( 75*365.25 - 25*365.25 ) ) INTO l_idealNum
     FROM customersTable t
   ;
   dbms_output.put_line ( 'l_idealNum = ' || TO_CHAR ( l_idealNum ) ) ;
   FOR r1 IN (
      SELECT t.dateOfBirth
           , COUNT(*) AS num
        FROM customersTable t
       GROUP BY t.dateOfBirth
      HAVING COUNT(*) >= l_idealNum * 10
       ORDER BY COUNT(*) DESC , dateOfBirth DESC
   )
   LOOP
      dbms_output.put_line ( 'Fixing ' || TO_CHAR ( r1.dateOfBirth , 'DD-Mon-YYYY' ) || ' records = ' || TO_CHAR ( r1.num ) ) ;
      UPDATE customersTable t
         SET t.dateOfBirth = TRUNC ( SYSTIMESTAMP - dbms_random.value ( low => 25*365.25 , high => 75*365.25 ) )  -- if timestamp column
         --SET t.dateOfBirth = TRUNC ( SYSDATE - dbms_random.value ( low => 25*365.25 , high => 75*365.25 ) )  -- if date column
       WHERE t.dateOfBirth = r1.dateOfBirth
      ;
   END LOOP ;
END dateOfBirth ;