Humble Trader

Saturday, September 30, 2006

Country Dimension - m_v2o_countries_test.sql 

SET SERVEROUTPUT ON SIZE 1000000;

DECLARE
 l_run_no                  NUMBER;
 l_mapping_histories_count NUMBER;
 l_v_countries_count       NUMBER;
 l_o_countries_count       NUMBER;
 l_init_o_countries_count  NUMBER;
 l_final_o_countries_count NUMBER;
 l_row_data                VARCHAR2(4000);
 l_passed                  BOOLEAN := TRUE;
BEGIN
 -- Initialisation code.
 DELETE FROM o_countries;

 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('- and m_s2v_countries.map.');
 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.');

 SELECT COUNT(*)
 INTO l_v_countries_count
 FROM v_countries;

 dbms_output.put_line('- V_COUNTRIES row count = ' ||
    l_v_countries_count);
 dbms_output.put_line('-');

 dbms_output.put_line('+- PROCEDURE m_v2o_countries.map test 1 start:');
 dbms_output.put_line('|  * Load fresh data to ODS.O_COUNTRIES *');
 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 O_COUNTRIES');
 dbms_output.put_line('|');

 m_v2o_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_o_countries_count
 FROM o_countries;

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

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

 DELETE FROM o_countries
 WHERE iso2_code = 'GB';

 COMMIT;

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

 dbms_output.put_line('+- PROCEDURE m_v2o_countries.map test 2 start:');
 dbms_output.put_line('|  * Load update data to ODS.O_COUNTRIES *');
 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 O_COUNTRIES');
 dbms_output.put_line('|');

 SELECT COUNT(*)
 INTO l_init_o_countries_count
 FROM o_countries;

 dbms_output.put_line('|         Initial O_COUNTRIES row count = ' ||
    l_init_o_countries_count);
 dbms_output.put_line('|');

 m_v2o_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_o_countries_count
 FROM o_countries;

 dbms_output.put_line('|         Final O_COUNTRIES row count = ' ||
    l_final_o_countries_count);
 dbms_output.put_line('|');

 dbms_output.put_line('| +----------+');
 IF l_mapping_histories_count = 1
    AND
    l_final_o_countries_count - l_init_o_countries_count = 1
 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_v2o_countries.map test 2 end:');
 dbms_output.put_line('-');

 dbms_output.put_line('+- PROCEDURE m_v2o_countries.map test 3 start:');
 dbms_output.put_line('|  * Load no data to ODS.O_COUNTRIES *');
 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 0 rows into O_COUNTRIES');
 dbms_output.put_line('|');

 SELECT COUNT(*)
 INTO l_init_o_countries_count
 FROM o_countries;

 dbms_output.put_line('|         Initial O_COUNTRIES row count = ' ||
    l_init_o_countries_count);
 dbms_output.put_line('|');

 m_v2o_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_o_countries_count
 FROM o_countries;

 dbms_output.put_line('|         Final O_COUNTRIES row count = ' ||
    l_final_o_countries_count);
 dbms_output.put_line('|');

 dbms_output.put_line('| +----------+');
 IF l_mapping_histories_count = 1
    AND
    l_final_o_countries_count - l_init_o_countries_count = 0
 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_v2o_countries.map test 3 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 8:05 pm

0 Comments:

Post a Comment

<< Home