Humble Trader

Sunday, October 01, 2006

Country Dimension - m_o2w_countries_test.sql 

SET SERVEROUTPUT ON SIZE 1000000;

DECLARE
  l_run_no                  NUMBER;
  l_mapping_histories_count NUMBER;
  l_countries_count         NUMBER;
  l_d_countries_count       NUMBER;
  l_init_d_countries_count  NUMBER;
  l_final_d_countries_count NUMBER;
  l_iso2_code               VARCHAR2(2);
  l_iso3_code               VARCHAR2(3);
  l_un3_number              VARCHAR2(3);
  l_start_date              DATE;
  l_end_date                DATE;
  l_row_data                VARCHAR2(4000);
  l_passed                  BOOLEAN := TRUE;
BEGIN
  -- Initialisation code.
  DELETE FROM d_country;

  COMMIT;

  dbms_output.put_line('-');
  dbms_output.put_line('- Prepare a COUNTRIES dataset by running');
  dbms_output.put_line('- get_html.fetch_html, get_html.parse_html, ' ||
     'm_s2s_countries.map,');
  dbms_output.put_line('- m_s2v_countries.map and m_v2o_countries.');
  dbms_output.put_line('-');

  get_html.fetch_html('COUNTRIES');

  dbms_output.put_line('- Fetch countries data done.');

  get_html.parse_html;

  dbms_output.put_line('- Parse countries data done.');

  m_s2s_countries.map;

  dbms_output.put_line('- Fetch S_COUNTRIES prepared.');

  m_s2v_countries.map;

  dbms_output.put_line('- Fetch V_COUNTRIES prepared.');

  m_v2o_countries.map;

  dbms_output.put_line('- Fetch O_COUNTRIES prepared.');

  SELECT COUNT(*)
  INTO l_countries_count
  FROM o_countries;

  dbms_output.put_line('- O_COUNTRIES row count = ' ||
     l_countries_count);
  dbms_output.put_line('-');

  dbms_output.put_line('+- PROCEDURE m_o2w_countries.map test 1 start:');
  dbms_output.put_line('|  * Load fresh data to WHS.D_COUNTRY *');
  dbms_output.put_line('|');

  dbms_output.put_line
     ('| Test  1: Pass : NO PARAMETERS');
  dbms_output.put_line('|');
  dbms_output.put_line
     ('| Expected Result: Inserts a row into MAPPING_HISTORIES');
  dbms_output.put_line
     ('| Expected Result: Inserts 240 rows into D_COUNTRY');
  dbms_output.put_line('|');

  m_o2w_countries.map;

  SELECT MAX(run_no)
  INTO l_run_no
  FROM mapping_histories
  WHERE mapping_library = 'M_O2W_COUNTRIES';

  SELECT COUNT(*)
  INTO l_mapping_histories_count
  FROM mapping_histories
  WHERE run_no = l_run_no;

  dbms_output.put_line('| Result: MAPPING_HISTORIES row count = ' ||
     l_mapping_histories_count);
  dbms_output.put_line('|         MAPPING_HISTORIES data :');

  SELECT '| [' || run_no || ', ' || mapping_library || ', ' ||
     TO_CHAR(start_tsp, 'DD-MON-YYYY HH24:MI:SS') ||
     ', ' || NVL(TO_CHAR(end_tsp, 'DD-MON-YYYY HH24:MI:SS'), '*NULL*') ||
     ']'
  INTO l_row_data
  FROM mapping_histories
  WHERE run_no = l_run_no;

  dbms_output.put_line(l_row_data);

  SELECT COUNT(*)
  INTO l_d_countries_count
  FROM d_country;

  dbms_output.put_line('|         D_COUNTRY row count = ' ||
     l_d_countries_count);
  dbms_output.put_line('|');

  dbms_output.put_line('| +----------+');
  IF l_mapping_histories_count = 1
     AND
     l_d_countries_count = 240
  THEN
     dbms_output.put_line('| |  PASSED  |');
  ELSE
     dbms_output.put_line('| |  FAILED  |');
     l_passed := FALSE;
  END IF;
  dbms_output.put_line('| +----------+');

  dbms_output.put_line('|');
  dbms_output.put_line('+- PROCEDURE m_o2w_countries.map test 1 end:');
  dbms_output.put_line('-');

  DELETE FROM d_country
  WHERE iso2_code = 'GB';

  UPDATE d_country
  SET iso2_code = 'XX'
  WHERE iso2_code = 'AF';

  UPDATE d_country
  SET iso3_code = 'XXX'
  WHERE iso2_code = 'AL';

  UPDATE d_country
  SET un3_number = 'XXX'
  WHERE iso2_code = 'DZ';

  UPDATE d_country
  SET start_date = '01-FEB-2006'
  WHERE iso2_code = 'AS';

  UPDATE d_country
  SET end_date = '01-FEB-2006'
  WHERE iso2_code = 'AD';

  COMMIT;

  dbms_output.put_line('+- PROCEDURE m_o2w_countries.map test 2 start:');
  dbms_output.put_line('|  * Load and update data to WHS.D_COUNTRY *');
  dbms_output.put_line('|');

  dbms_output.put_line
     ('| Test  1: Pass : NO PARAMETERS');
  dbms_output.put_line('|');
  dbms_output.put_line
     ('| Expected Result: Inserts a row into MAPPING_HISTORIES');
  dbms_output.put_line
     ('| Expected Result: Inserts 1 row into D_COUNTRY');
  dbms_output.put_line
     ('| Expected Result: Updates 5 rows in D_COUNTRY');
  dbms_output.put_line('|');

  SELECT COUNT(*)
  INTO l_init_d_countries_count
  FROM d_country;

  dbms_output.put_line('|         Initial D_COUNTRY row count = ' ||
     l_init_d_countries_count);
  dbms_output.put_line('|         Rows to update: = ');

  FOR r_row IN
  (
     SELECT name
           ,iso2_code
           ,iso3_code
           ,un3_number
           ,start_date
           ,end_date
     FROM d_country
     WHERE iso2_code IN ('XX', 'AL', 'DZ', 'AS', 'AD')
  )
  LOOP
     dbms_output.put_line('| [' || r_row.name || ', ' || r_row.iso2_code ||
        ', ' || r_row.iso3_code || ', ' || r_row.un3_number || ', ' ||
        r_row.start_date || ', ' || r_row.end_date || ']');
  END LOOP;

  dbms_output.put_line('|');

  m_o2w_countries.map;

  SELECT MAX(run_no)
  INTO l_run_no
  FROM mapping_histories
  WHERE mapping_library = 'M_V2O_COUNTRIES';

  SELECT COUNT(*)
  INTO l_mapping_histories_count
  FROM mapping_histories
  WHERE run_no = l_run_no;

  dbms_output.put_line('| Result: MAPPING_HISTORIES row count = ' ||
     l_mapping_histories_count);
  dbms_output.put_line('|         MAPPING_HISTORIES data :');

  SELECT '| [' || run_no || ', ' || mapping_library || ', ' ||
     TO_CHAR(start_tsp, 'DD-MON-YYYY HH24:MI:SS') ||
     ', ' || NVL(TO_CHAR(end_tsp, 'DD-MON-YYYY HH24:MI:SS'), '*NULL*') ||
     ']'
  INTO l_row_data
  FROM mapping_histories
  WHERE run_no = l_run_no;

  dbms_output.put_line(l_row_data);

  SELECT COUNT(*)
  INTO l_final_d_countries_count
  FROM o_countries;

  dbms_output.put_line('|         Final O_COUNTRIES row count = ' ||
     l_final_d_countries_count);
  dbms_output.put_line('|         Rows updated: = ');

  FOR r_row IN
  (
     SELECT name
           ,iso2_code
           ,iso3_code
           ,un3_number
           ,start_date
           ,end_date
     FROM d_country
     WHERE iso2_code IN ('AF', 'AL', 'DZ', 'AS', 'AD')
  )
  LOOP
     dbms_output.put_line('| [' || r_row.name || ', ' || r_row.iso2_code ||
        ', ' || r_row.iso3_code || ', ' || r_row.un3_number || ', ' ||
        r_row.start_date || ', ' || r_row.end_date || ']');

     IF r_row.iso2_code = 'AF'
     THEN
        l_iso2_code := r_row.iso2_code;
     END IF;

     IF r_row.iso2_code = 'AL'
     THEN
        l_iso3_code := r_row.iso3_code;
     END IF;

     IF r_row.iso2_code = 'DZ'
     THEN
        l_un3_number := r_row.un3_number;
     END IF;

     IF r_row.iso2_code = 'AS'
     THEN
        l_start_date := r_row.start_date;
     END IF;

     IF r_row.iso2_code = 'AD'
     THEN
        l_end_date := r_row.end_date;
     END IF;
  END LOOP;

  dbms_output.put_line('|');

  dbms_output.put_line('| +----------+');
  IF l_mapping_histories_count = 1
     AND
     l_final_d_countries_count - l_init_d_countries_count = 1
     AND
     l_iso2_code = 'AF'
     AND
     l_iso3_code = 'ALB'
     AND
     l_un3_number = '012'
     AND
     l_start_date = '01-JAN-1000'
     AND
     l_end_date = '31-DEC-3000'
  THEN
     dbms_output.put_line('| |  PASSED  |');
  ELSE
     dbms_output.put_line('| |  FAILED  |');
     l_passed := FALSE;
  END IF;
  dbms_output.put_line('| +----------+');

  dbms_output.put_line('|');
  dbms_output.put_line('+- PROCEDURE m_o2w_countries.map test 2 end:');
  dbms_output.put_line('-');

  dbms_output.put_line('-');
  dbms_output.put_line('- Overall result:');
  dbms_output.put_line('-');
  dbms_output.put_line('- +----------+');
  IF l_passed = TRUE
  THEN
     dbms_output.put_line('- |  PASSED  |');
  ELSE
     dbms_output.put_line('- |  FAILED  |');
  END IF;
  dbms_output.put_line('- +----------+');
  dbms_output.put_line('-');

END;
/

posted by Cat Play Crew at 1:56 am

0 Comments:

Post a Comment

<< Home