Country Dimension - m_s2v_countries_test.sql

SET SERVEROUTPUT ON SIZE 1000000; DECLARE l_run_no NUMBER; l_mapping_histories_count NUMBER; l_s_countries_count NUMBER; l_v_countries_count NUMBER; l_row_data VARCHAR2(4000); l_passed BOOLEAN := TRUE; BEGIN -- Initialisation code. dbms_output.put_line('-'); dbms_output.put_line('- Prepare a S_COUNTRIES dataset by running'); dbms_output.put_line('- get_html.fetch_html, get_html.parse_html ' || 'and m_s2s_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.'); commit; SELECT COUNT(*) INTO l_s_countries_count FROM s_countries; dbms_output.put_line('- S_COUNTRIES row count = ' || l_s_countries_count); dbms_output.put_line('-'); dbms_output.put_line('+- PROCEDURE m_s2v_countries.map test start:'); 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 V_COUNTRIES'); dbms_output.put_line('|'); m_s2v_countries.map; SELECT MAX(run_no) INTO l_run_no FROM mapping_histories WHERE mapping_library = 'M_S2V_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_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('| +----------+'); IF l_mapping_histories_count = 1 AND l_v_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_s2v_countries.map test end:'); 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; /