Country Dimension - m_s2s_countries_b.sql

-- ------------------------------------------------------------------------- -- -- -- -- Title: m_s2s_countries_b.sql -- -- Author: Steve Roach -- -- Date: 07-SEP-2006 -- -- Version: 01.00 -- -- -- -- Description: Container for all procedures and functions relating to -- -- extracting data for the countries dimension and putting it -- -- into the STA schema. -- -- -- -- History: -- -- -- -- Date Ver Author Description -- -- --------- ----- ------ -------------------------------------------------- -- -- 07-SEP-06 01.00 SRR First release -- -- -- -- ------------------------------------------------------------------------- -- -- Package body definition. CREATE OR REPLACE PACKAGE BODY m_s2s_countries AS -- map Overload 1 -- Wrapper to do mapping with housekeeping. PROCEDURE map IS l_run_no NUMBER; BEGIN -- Run initialise mapping with parameters p_mapping = 'M_S2S_COUNTRIES', -- run_no = local variable to capture the run number. ctl_gen.initialise_mapping('M_S2S_COUNTRIES', l_run_no); -- Run map (overload 2) with parameter p_run_no = the captured run -- number. map(l_run_no); -- Run finalise mapping with parameter run_no = the captured run number. ctl_gen.finalise_mapping(l_run_no); COMMIT; EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20001, 'm_s2s_countries.map(1) Error: [' || SQLERRM || '].'); END map; -- Overload 1 -- map Overload 2 -- Map PARSED_HTML to VAL.COUNTRIES. PROCEDURE map ( p_run_no IN NUMBER ) IS l_i NUMBER; l_j NUMBER; -- Initialise the data sequence counter to zero. l_data_seq NUMBER := 0; l_sql_stmt VARCHAR2(32767); l_fields NUMBER; l_data_tab extract_html_data.data_list; BEGIN -- Delete all data from S_COUNTRIES - discarding the previous run. DELETE FROM s_countries; -- Use extract_html_data.extract_table with the parameters p_table_num = -- 4, p_web_page = 'COUNTRIES' to capture the raw HTML from the most -- recent fetch and parse into a PL/SQL table. l_data_tab := extract_html_data.extract_table(4, 'COUNTRIES'); -- For each row in the PL/SQL table - excluding the first which contains -- column header information... FOR l_i IN 2..l_data_tab.COUNT LOOP -- dbms_output.put_line(l_i || ': ' || l_data_tab(l_i)); -- Increment the data sequence counter. l_data_seq := l_data_seq + 1; -- Count the number of fields in the PL/SQL table row. There are a -- maximum of eight but null data in any particular row drops -- the field completely. l_fields := lib_string.count_chr(l_data_tab(l_i), '''') / 2; -- If there are less than eight fields... IF l_fields < 8 THEN -- For each missing field... FOR l_j IN 1..(8 - l_fields) LOOP -- Generate missing fields as 'NULL'. l_data_tab(l_i) := l_data_tab(l_i) || ',NULL'; END LOOP; END IF; -- Create an insert statement containing the corrected PL/SQL row -- and audit data. l_sql_stmt := 'INSERT INTO s_countries(run_no' || ', data_seq' || ', name' || ', iso2_code' || ', iso3_code' || ', un3_code' || ', windows_country_region' || ', windows_code' || ', mac_name' || ', mac_code' || ', ins_tsp) VALUES (' || p_run_no || ', ' || l_data_seq || ', ' || l_data_tab(l_i) || ', TO_DATE(''' || SYSDATE || ''',''DD-MON-YY''))'; -- dbms_output.put_line(l_sql_stmt); -- Run the insert statement to insert the data into S_COUNTRIES. EXECUTE IMMEDIATE l_sql_stmt; END LOOP; EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20001, 'm_s2s_countries.map(2) Error: [' || SQLERRM || '].'); END map; -- Overload 2 END m_s2s_countries; / SHOW ERRORS