Country Dimension - m_o2w_countries_b.sql

-- ------------------------------------------------------------------------- -- -- -- -- Title: m_o2w_countries_b.sql -- -- Author: Steve Roach -- -- Date: 30-SEP-2006 -- -- Version: 01.00 -- -- -- -- Description: Container for all procedures and functions relating to -- -- mapping data for the Countries Dimension from ODS to WHS. -- -- -- -- History: -- -- -- -- Date Ver Author Description -- -- --------- ----- ------ -------------------------------------------------- -- -- 30-SEP-06 01.00 SRR First release -- -- -- -- ------------------------------------------------------------------------- -- -- Package body definition. CREATE OR REPLACE PACKAGE BODY m_o2w_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_O2W_COUNTRIES', -- run_no = local variable to capture the run number. ctl_gen.initialise_mapping('M_O2W_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_o2w_countries.map(1) Error: [' || SQLERRM || '].'); END map; -- Overload 1 -- map Overload 2 -- Map ODS.COUNTRIES to WHS.D_COUNTRIES. PROCEDURE map ( p_run_no IN NUMBER ) IS BEGIN -- Insert into ODS.COUNTRIES any country in VAL.V_COUNTRIES that is not -- already there. -- dbms_output.put_line('map(2): Inserting into D_COUNTRY...'); INSERT INTO d_country ( id ,name ,iso2_code ,iso3_code ,un3_number ,start_date ,end_date ,run_no ,ins_tsp ) SELECT d_country_s.NEXTVAL ,name ,NVL(iso2_code, '--') ,NVL(iso3_code, '---') ,NVL(un3_number, '---') ,start_date ,end_date ,p_run_no ,SYSDATE FROM o_countries WHERE name NOT IN ( SELECT name FROM d_country ); -- dbms_output.put_line('map(2): Inserting into D_COUNTRY...DONE'); COMMIT; -- Update and rows in D_COUNTRIES that differ in detail from COUNTRIES. -- dbms_output.put_line('map(2): Updating D_COUNTRY...'); UPDATE d_country dc SET ( iso2_code ,iso3_code ,un3_number ,start_date ,end_date ,run_no ,ins_tsp ) = ( SELECT NVL(oc.iso2_code, '--') ,NVL(oc.iso3_code, '---') ,NVL(oc.un3_number, '---') ,oc.start_date ,oc.end_date ,p_run_no ,SYSDATE FROM o_countries oc WHERE oc.name = dc.name ) WHERE ( dc.name IN ( SELECT dc.name FROM d_country dc ,o_countries oc WHERE dc.name = oc.name AND ( NVL(oc.iso2_code, '--') != dc.iso2_code OR NVL(oc.iso3_code, '---') != dc.iso3_code OR NVL(oc.un3_number, '--') != dc.un3_number OR oc.start_date != dc.start_date OR oc.end_date != dc.end_date ) ) ); -- dbms_output.put_line('map(2): Updating D_COUNTRY...DONE'); COMMIT; EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20001, 'm_o2w_countries.map(2) Error: [' || SQLERRM || '].'); END map; -- Overload 2 END m_o2w_countries; / SHOW ERRORS

Country Dimension - m_o2w_countries_h.sql

-- ------------------------------------------------------------------------- -- -- -- -- Title: m_o2w_countries_h.sql -- -- Author: Steve Roach -- -- Date: 30-SEP-2006 -- -- Version: 01.00 -- -- -- -- Description: Container for all procedures and functions relating to -- -- mapping data for the Countries Dimension from ODS to WHS. -- -- -- -- Procedures: -- -- ------------------------------------------------------------------------ -- -- map - Overload 1 -- -- -- -- Wrapper to do mapping with housekeeping. -- -- ------------------------------------------------------------------------ -- -- map - Overload 2 -- -- -- -- Map ODS.COUNTRIES to WHS.D_COUNTRIES. -- -- -- -- p_run_no IN NUMBER - Run number of this map run. -- -- ------------------------------------------------------------------------- -- -- History: -- -- -- -- Date Ver Author Description -- -- --------- ----- ------ -------------------------------------------------- -- -- 30-SEP-06 01.00 SRR First release -- -- -- -- ------------------------------------------------------------------------- -- -- Package header definition. CREATE OR REPLACE PACKAGE m_o2w_countries AS PROCEDURE map; -- Overload 1. PROCEDURE map -- Overload 2. ( p_run_no IN NUMBER ); END m_o2w_countries; / SHOW ERRORS

Country Dimension - d_country_s.sql

-- ------------------------------------------------------------------------- -- -- -- -- Title: d_country_s.sql -- -- Author: Steve Roach -- -- Date: 30-SEP-2006 -- -- Version: 01.00 -- -- -- -- Description: Create sequence D_COUNTRY_S. -- -- -- -- History: -- -- -- -- Date Ver Author Description -- -- --------- ----- ------ -------------------------------------------------- -- -- 30-SEP-06 01.00 SRR First release -- -- -- -- ------------------------------------------------------------------------- -- DROP SEQUENCE d_country_s / CREATE SEQUENCE d_country_s START WITH 1 INCREMENT BY 1 NOCACHE /

Sequence Create Script Template

-- ------------------------------------------------------------------------- -- -- -- -- Title: [sequence]_s.sql -- -- Author: Steve Roach -- -- Date: DD-MON-YYYY -- -- Version: 01.00 -- -- -- -- Description: Create sequence [SEQUENCE]_S. -- -- -- -- History: -- -- -- -- Date Ver Author Description -- -- --------- ----- ------ -------------------------------------------------- -- -- DD-MON-YY 01.00 SRR First release -- -- -- -- ------------------------------------------------------------------------- -- DROP SEQUENCE [sequence]_s / CREATE SEQUENCE [sequence]_s START WITH 1 INCREMENT BY 1 NOCACHE /

Country Dimension - m_o2w_countries_a.sql

-- ------------------------------------------------------------------------- -- -- -- -- Title: m_o2w_countries_a.sql -- -- Author: Steve Roach -- -- Date: 30-SEP-2006 -- -- Version: 01.00 -- -- -- -- Description: Recreate all objects for package M_O2W_COUNTRIES. -- -- -- -- History: -- -- -- -- Date Ver Author Description -- -- --------- ----- ------ -------------------------------------------------- -- -- 30-SEP-06 01.00 SRR First release -- -- -- -- ------------------------------------------------------------------------- -- @m_o2w_countries_h.sql @m_o2w_countries_b.sql

Country Dimension - Process M_O2W_COUNTRIES Scripts

Scripts that support this object:

• m_o2w_countries_a.sql
• m_o2w_countries_h.sql
• m_o2w_countries_b.sql

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; /

Country Dimension - m_v2o_countries_b.sql

-- ------------------------------------------------------------------------- -- -- -- -- Title: m_v2o_countries_b.sql -- -- Author: Steve Roach -- -- Date: 30-SEP-2006 -- -- Version: 01.00 -- -- -- -- Description: Container for all procedures and functions relating to -- -- mapping data for the Countries Dimension from VAL to ODS. -- -- -- -- History: -- -- -- -- Date Ver Author Description -- -- --------- ----- ------ -------------------------------------------------- -- -- 30-SEP-06 01.00 SRR First release -- -- -- -- ------------------------------------------------------------------------- -- -- Package body definition. CREATE OR REPLACE PACKAGE BODY m_v2o_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_V2O_COUNTRIES', -- run_no = local variable to capture the run number. ctl_gen.initialise_mapping('M_V2O_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_v2o_countries.map(1) Error: [' || SQLERRM || '].'); END map; -- Overload 1 -- map Overload 2 -- Map VAL.V_COUNTRIES to ODS.O_COUNTRIES. PROCEDURE map ( p_run_no IN NUMBER ) IS BEGIN -- Insert into ODS.O_COUNTRIES any country in VAL.V_COUNTRIES that is not -- already there. INSERT INTO o_countries ( name ,iso2_code ,iso3_code ,un3_number ,start_date ,end_date ,run_no ,ins_tsp ) SELECT name ,iso2_code ,iso3_code ,un3_number ,'01-JAN-1000' ,'31-DEC-3000' ,p_run_no ,SYSDATE FROM v_countries WHERE name NOT IN ( SELECT name FROM o_countries ); COMMIT; EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20001, 'm_v2o_countries.map(2) Error: [' || SQLERRM || '].'); END map; -- Overload 2 END m_v2o_countries; / SHOW ERRORS

Country Dimension - m_v2o_countries_h.sql

-- ------------------------------------------------------------------------- -- -- -- -- Title: m_v2o_countries_h.sql -- -- Author: Steve Roach -- -- Date: 26-SEP-2006 -- -- Version: 01.00 -- -- -- -- Description: Container for all procedures and functions relating to -- -- mapping data for the Countries Dimension from VAL to ODS. -- -- -- -- Procedures: -- -- ------------------------------------------------------------------------ -- -- map - Overload 1 -- -- -- -- Wrapper to do mapping with housekeeping. -- -- ------------------------------------------------------------------------ -- -- map - Overload 2 -- -- -- -- Map VAL.V_COUNTRIES to ODS.COUNTRIES. -- -- -- -- p_run_no IN NUMBER - Run number of this map run. -- -- ------------------------------------------------------------------------- -- -- History: -- -- -- -- Date Ver Author Description -- -- --------- ----- ------ -------------------------------------------------- -- -- 26-SEP-06 01.00 SRR First release -- -- -- -- ------------------------------------------------------------------------- -- -- Package header definition. CREATE OR REPLACE PACKAGE m_v2o_countries AS PROCEDURE map; -- Overload 1. PROCEDURE map -- Overload 2. ( p_run_no IN NUMBER ); END m_v2o_countries; / SHOW ERRORS

Country Dimension - m_v2o_countries_a.sql

-- ------------------------------------------------------------------------- -- -- -- -- Title: m_v2o_countries_a.sql -- -- Author: Steve Roach -- -- Date: 26-SEP-2006 -- -- Version: 01.00 -- -- -- -- Description: Recreate all objects for package M_V2O_COUNTRIES. -- -- -- -- History: -- -- -- -- Date Ver Author Description -- -- --------- ----- ------ -------------------------------------------------- -- -- 26-SEP-06 01.00 SRR First release -- -- -- -- ------------------------------------------------------------------------- -- @m_v2o_countries_h.sql @m_v2o_countries_b.sql

Country Dimension - Process M_V2O_COUNTRIES Scripts

Scripts that support this object:

• m_v2o_countries_a.sql
• m_v2o_countries_h.sql
• m_v2o_countries_b.sql

Country Dimension - o_countries_c.sql

-- ------------------------------------------------------------------------- -- -- -- -- Title: o_countries_c.sql -- -- Author: Steve Roach -- -- Date: 25-SEP-2006 -- -- Version: 01.00 -- -- -- -- Description: Create constraints for table ODS.O_COUNTRIES. -- -- -- -- History: -- -- -- -- Date Ver Author Description -- -- --------- ----- ------ -------------------------------------------------- -- -- 26-SEP-06 01.00 SRR First release -- -- -- -- ------------------------------------------------------------------------- -- ALTER TABLE o_countries DROP CONSTRAINT o_countries_pk / ALTER TABLE o_countries ADD CONSTRAINT o_countries_pk PRIMARY KEY ( name ,start_date ) USING INDEX o_countries_pk /

Country Dimension - o_countries_i.sql

-- ------------------------------------------------------------------------- -- -- -- -- Title: o_countries_i.sql -- -- Author: Steve Roach -- -- Date: 25-SEP-2006 -- -- Version: 01.00 -- -- -- -- Description: Create indexes for table ODS.O_COUNTRIES. -- -- -- -- History: -- -- -- -- Date Ver Author Description -- -- --------- ----- ------ -------------------------------------------------- -- -- 25-SEP-06 01.00 SRR First release -- -- -- -- ------------------------------------------------------------------------- -- DROP INDEX o_countries_pk / CREATE UNIQUE INDEX o_countries_pk ON o_countries ( name ,start_date ) /

Country Dimension - o_countries_t.sql

-- ------------------------------------------------------------------------- -- -- -- -- Title: o_countries_t.sql -- -- Author: Steve Roach -- -- Date: 26-SEP-2006 -- -- Version: 01.00 -- -- -- -- Description: Create table ODS.O_COUNTRIES. -- -- -- -- History: -- -- -- -- Date Ver Author Description -- -- --------- ----- ------ -------------------------------------------------- -- -- 26-SEP-06 01.00 SRR First release -- -- -- -- ------------------------------------------------------------------------- -- DROP TABLE o_countries CASCADE CONSTRAINTS / CREATE TABLE o_countries ( name VARCHAR2(100) NOT NULL ,iso2_code VARCHAR2(2) NULL ,iso3_code VARCHAR2(3) NULL ,un3_number VARCHAR2(3) NULL ,start_date DATE NOT NULL ,end_date DATE NOT NULL ,run_no NUMBER NOT NULL ,ins_tsp DATE NOT NULL ) / COMMENT ON TABLE o_countries IS 'Names of countries and their various codes as defined by the ISO.' / COMMENT ON COLUMN o_countries.name IS 'Full name of the country.' / COMMENT ON COLUMN o_countries.iso2_code IS '2 character ISO code.' / COMMENT ON COLUMN o_countries.iso3_code IS '3 character ISO code.' / COMMENT ON COLUMN o_countries.un2_number IS '3 digit numeric UN code.' / COMMENT ON COLUMN o_countries.start_date IS 'Date that the Country came into being.' / COMMENT ON COLUMN o_countries.end_date IS 'Date that the Country ceased.' / COMMENT ON COLUMN o_countries.run_no IS 'The Run Number that created this row.' / COMMENT ON COLUMN o_countries.ins_tsp IS 'Insert timestamp.' /

Country Dimension - o_countries_a.sql

-- ------------------------------------------------------------------------- -- -- -- -- Title: o_countries_a.sql -- -- Author: Steve Roach -- -- Date: 25-SEP-2006 -- -- Version: 01.00 -- -- -- -- Description: Recreate all objects for table ODS.O_COUNTRIES. -- -- -- -- History: -- -- -- -- Date Ver Author Description -- -- --------- ----- ------ -------------------------------------------------- -- -- 25-SEP-06 01.00 SRR First release -- -- -- -- ------------------------------------------------------------------------- -- @o_countries_t.sql @o_countries_i.sql @o_countries_c.sql

Country Dimension - Table O_COUNTRIES Script List

Scripts that support this object:

• o_countries_a.sql
• o_countries_t.sql
• o_countries_i.sql
• o_countries_c.sql

Country Dimension - Process M_S2V_COUNTRIES Specification

• Package: m_s2v_countries
  
• Description: Container for all procedures and functions relating to mapping data for the Countries Dimension from STA to VAL.
• Internal Function: validate_country_name
  
• Description: Function to return a validated country name or throw an exception if invalid.
  
• Parameters:
• p_country_name:
• Datatype: VARCHAR2
  
• Direction: IN
  
• Description: The unvalidated country name.
• RETURN:
• Datatype: VARCHAR2
  
• Description: The validated country name.
• Action:
• Replace all Special Control Entity Characters, and strip leading and trailing spaces from the unvalidated country name.
• If the validated country name is longer than 100 characters...
• Throw an exception.
  
• End; If the validated country name is longer than 100 characters.
• Return the validated country code.
  
• Internal Function: validate_iso2
  
• Description: Function to return a validated ISO2 code or throw an exception if invalid.
  
• Parameters:
• p_iso2:
• Datatype: VARCHAR2
  
• Direction: IN
  
• Description: The unvalidated ISO2 code.
• RETURN:
• Datatype: VARCHAR2
  
• Description: The validated ISO2 code.
• Action:
• Replace trailing asterix's, and strip leading and trailing spaces from the unvalidated ISO2 code.
• If the validated ISO2 code is longer than 2 characters...
• Throw an exception.
  
• End; If the validated ISO2 code is longer than 2 characters.
• Return the validated ISO2 code.
  
• Internal Function: validate_iso3
  
• Description: Function to return a validated ISO3 code or throw an exception if invalid.
  
• Parameters:
• p_iso3:
• Datatype: VARCHAR2
  
• Direction: IN
  
• Description: The unvalidated ISO3 code.
• RETURN:
• Datatype: VARCHAR2
  
• Description: The validated ISO3 code.
• Action:
• Replace trailing asterix's, and strip leading and trailing spaces from the unvalidated ISO3 code.
• If the validated ISO3 code is longer than 3 characters...
• Throw an exception.
  
• End; If the validated ISO3 code is longer than 3 characters.
• Return the validated ISO3 code.
  
• Internal Function: validate_un3
  
• Description: Function to return a validated UN3 code or throw an exception if invalid.
  
• Parameters:
• p_un3:
• Datatype: VARCHAR2
  
• Direction: IN
  
• Description: The unvalidated UN3 code.
• RETURN:
• Datatype: VARCHAR2
  
• Description: The validated UN3 code.
• Action:
• Sstrip leading and trailing spaces from the unvalidated UN3 code.
• If the validated UN3 code is longer than 3 characters...
• Throw an exception.
  
• End; If the validated UN3 code is longer than 3 characters.
• Return the validated UN3 code.
  
• Procedure: map (Overload 1)
  
• Description: Wrapper to do mapping with housekeeping.
  
• Action:
• Run initialise mapping with parameters p_mapping = 'M_S2V_COUNTRIES', run_no = local variable to capture the run number.
  
• Run map (overload 2) with parameter p_run_no = the captured run number.
• Run finalise mapping with parameter run_no = the captured run number.
  
• Procedure: map (Overload 2)
  
• Description: Map STA.S_COUNTRIES to VAL.V_COUNTRIES.
  
• Parameters:
• p_run_no:
• Datatype: NUMBER
  
• Direction: IN
  
• Description: The run number for this run.
  
• Action:
• Delete all data from V_COUNTRIES - discarding the previous run.
• For each row in S_COUNTRIES...
• Validate country name using validate_country_name with parameter; p_country_name = r_country.name.
• Validate ISO2 code using validate_iso2 with parameter; p_iso2 = r_country.iso2_code
• Validate ISO3 code using validate_iso3 with parameter; p_iso3 = r_country.iso3_code
• Validate UN3 number using validate_un3 with parameter; p_un3 = r_country.un3_number
• Create a new v_countries row with the validated data.
  
• End; For each row in S_COUNTRIES.

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; /

Country Dimension - Implementation Notes

• Upload to CTL:
• country_dimension_ctl_d.sql
  

• Upload to STA:
• s_countries_a.sql
• s_countries_t.sql
• s_countries_i.sql
• s_countries_c.sql
• m_s2s_countries_a.sql
• m_s2s_countries_h.sql
• m_s2s_countries_b.sql
• m_s2v_countries_a.sql
• m_s2v_countries_h.sql
• m_s2v_countries_b.sql
  

• Upload to VAL:
• v_countries_a.sql
• v_countries_t.sql
• v_countries_i.sql
• v_countries_c.sql
• m_v2o_countries_a.sql
• m_v2o_countries_h.sql
• m_v2o_countries_b.sql

• Upload to ODS:
• o_countries_a.sql
• o_countries_t.sql
• o_countries_i.sql
• o_countries_c.sql
• m_o2w_countries_a.sql
• m_o2w_countries_h.sql
• m_o2w_countries_b.sql
  

• Upload to WHS:
• d_countries_a.sql
• d_countries_t.sql
• d_countries_i.sql
• d_countries_c.sql
• d_countries_s.sql

• Run the following in order:
• CTL script: country_dimension_ctl_d.sql
• STA script: s_countries_a.sql
• STA script: m_s2s_countries_a.sql
• VAL script: v_countries_a.sql
• VAL command: EXEC lib_util.create_grants;
• STA command: EXEC lib_util.create_synonyms;
• STA script: m_s2v_countries_a.sql
• ODS script: o_countries_a.sql
• ODS command: EXEC lib_util.create_grants;
• VAL command: EXEC lib_util.create_synonyms;
• VAL script: m_v2o_countries_a.sql
• WHS script: d_country_a.sql
• WHS script: d_country_s.sql
• WHS command: EXEC lib_util.create_grants;
• VAL command: EXEC lib_util.create_grants;
• ODS command: EXEC lib_util.create_synonyms;
• ODS script: m_o2w_countries_a.sql
• STA command: EXEC lib_util.create_grants;
• VAL command: EXEC lib_util.create_synonyms;
• ODS command: EXEC lib_util.create_synonyms;

• Run the test script; STA: m_s2s_countries_test.sql. NOTE: Best run from the SQL Command Line.
  
• Each test outputs a result and, at the end, a pass/fail summary.
• Run the test script; STA: m_s2v_countries_test.sql. NOTE: Best run from the SQL Command Line.
  
• Each test outputs a result and, at the end, a pass/fail summary.
• Run the test script; VAL: m_v2o_countries_test.sql. NOTE: Best run from the SQL Command Line.
  
• Each test outputs a result and, at the end, a pass/fail summary.
• Run the test script; ODS: m_o2w_countries_test.sql. NOTE: Best run from the SQL Command Line.
  
• Each test outputs a result and, at the end, a pass/fail summary.

Get HTML - Data STA.HTML_SPECIAL_ENTITY_CODES Specification

• Target: html_special_entity_codes
• Row:
  
• special_entity_code: 'AMP'
  
• character: '&'
  
• name: AMP maps to '&'
• Row:
  
• special_entity_code: '#39'
  
• character: '`'
  
• name: #39 maps to '`'
  

Get HTML - html_special_entity_codes_d.sql

-- ------------------------------------------------------------------------- -- -- -- -- Title: html_special_entity_codes_d.sql -- -- Author: Steve Roach -- -- Date: 23-SEP-2006 -- -- Version: 01.00 -- -- -- -- Description: Create seed data for table HTML_SPECIAL_ENTITY_CODES. -- -- -- -- History: -- -- -- -- Date Ver Author Description -- -- --------- ----- ------ -------------------------------------------------- -- -- 23-SEP-06 01.00 SRR First release -- -- -- -- ------------------------------------------------------------------------- -- DELETE FROM html_special_entity_codes / COMMIT / INSERT INTO html_special_entity_codes ( special_entity_code ,character ,description ) VALUES ( 'AMP' ,'&' ,'AMP maps to ''&''' ) / INSERT INTO html_special_entity_codes ( special_entity_code ,character ,description ) VALUES ( '#39' ,CHR(39) ,'#39 maps to ''`''' ) / COMMIT /

Get HTML - html_special_entity_codes_c.sql

-- ------------------------------------------------------------------------- -- -- -- -- Title: html_special_entity_codes_c.sql -- -- Author: Steve Roach -- -- Date: 17-SEP-2006 -- -- Version: 01.00 -- -- -- -- Description: Create constraints for table HTML_SPECIAL_ENTITY_CODES. -- -- -- -- History: -- -- -- -- Date Ver Author Description -- -- --------- ----- ------ -------------------------------------------------- -- -- 17-SEP-06 01.00 SRR First release -- -- -- -- ------------------------------------------------------------------------- -- ALTER TABLE html_special_entity_codes DROP CONSTRAINT html_special_entity_codes_pk / ALTER TABLE html_special_entity_codes ADD CONSTRAINT html_special_entity_codes_pk PRIMARY KEY ( special_entity_code ) USING INDEX html_special_entity_codes_pk /

Get HTML - html_special_entity_codes_i.sql

-- ------------------------------------------------------------------------- -- -- -- -- Title: html_special_entity_codes_i.sql -- -- Author: Steve Roach -- -- Date: 17-SEP-2006 -- -- Version: 01.00 -- -- -- -- Description: Create indexes for table HTML_SPECIAL_ENTITY_CODES. -- -- -- -- History: -- -- -- -- Date Ver Author Description -- -- --------- ----- ------ -------------------------------------------------- -- -- 17-SEP-06 01.00 SRR First release -- -- -- -- ------------------------------------------------------------------------- -- DROP INDEX html_special_entity_codes_pk / CREATE UNIQUE INDEX html_special_entity_codes_pk ON html_special_entity_codes ( special_entity_code ) /

Get HTML - html_special_entity_codes_t.sql

-- ------------------------------------------------------------------------- -- -- -- -- Title: html_special_entity_codes_t.sql -- -- Author: Steve Roach -- -- Date: 17-SEP-2006 -- -- Version: 01.00 -- -- -- -- Description: Create table HTML_SPECIAL_ENTITY_CODES. -- -- -- -- History: -- -- -- -- Date Ver Author Description -- -- --------- ----- ------ -------------------------------------------------- -- -- 17-SEP-06 01.00 SRR First release -- -- -- -- ------------------------------------------------------------------------- -- DROP TABLE html_special_entity_codes CASCADE CONSTRAINTS / CREATE TABLE html_special_entity_codes ( special_entity_code VARCHAR2(6) NOT NULL ,character VARCHAR2(1) NOT NULL ,description VARChAR2(100) NOT NULL ) / COMMENT ON TABLE html_special_entity_codes IS 'A conversion table that matches the special entity codes to the characters they represent.' / COMMENT ON COLUMN html_special_entity_codes.special_entity_code IS 'The common special entity code without the ampersand and semi-colon - case sensitive.' / COMMENT ON COLUMN html_special_entity_codes.character IS 'The character represented by the code.' / COMMENT ON COLUMN html_special_entity_codes.description IS 'A description of the code.' /

Get HTML - html_special_entity_codes_a.sql

-- ------------------------------------------------------------------------- -- -- -- -- Title: html_special_entity_codes_a.sql -- -- Author: Steve Roach -- -- Date: 17-SEP-2006 -- -- Version: 01.00 -- -- -- -- Description: Recreate all objects for table HTML_SPECIAL_ENTITY_CODES. -- -- -- -- History: -- -- -- -- Date Ver Author Description -- -- --------- ----- ------ -------------------------------------------------- -- -- 17-SEP-06 01.00 SRR First release -- -- -- -- ------------------------------------------------------------------------- -- @html_special_entity_codes_t.sql @html_special_entity_codes_i.sql @html_special_entity_codes_c.sql

Get HTML - Table HTML_SPECIAL_ENTITY_CODES

Scripts that support this object:

• html_special_entity_codes_a.sql
• html_special_entity_codes_t.sql
• html_special_entity_codes_i.sql
• html_special_entity_codes_c.sql

Control Repository - mapping_histories_c.sql

-- ------------------------------------------------------------------------- -- -- -- -- Title: mapping_histories_c.sql -- -- Author: Steve Roach -- -- Date: 23-AUG-2006 -- -- Version: 01.00 -- -- -- -- Description: Create constraints for table MAPPING_HISTORIES. -- -- -- -- History: -- -- -- -- Date Ver Author Description -- -- --------- ----- ------ -------------------------------------------------- -- -- 23-AUG-06 01.00 SRR First release -- -- -- -- ------------------------------------------------------------------------- -- ALTER TABLE mapping_histories DROP CONSTRAINT mapping_histories_pk / ALTER TABLE mapping_histories DROP CONSTRAINT mapping_histories_f1 / ALTER TABLE mapping_histories ADD CONSTRAINT mapping_histories_pk PRIMARY KEY ( run_no ) USING INDEX mapping_histories_pk / ALTER TABLE mapping_histories ADD CONSTRAINT mapping_histories_f1 FOREIGN KEY ( mapping_library ) REFERENCES mapping_libraries(mapping_name) /

Control Repository - mapping_histories_i.sql

-- ------------------------------------------------------------------------- -- -- -- -- Title: mapping_histories_i.sql -- -- Author: Steve Roach -- -- Date: 23-AUG-2006 -- -- Version: 01.00 -- -- -- -- Description: Create indexes for table MAPPING_HISTORIES. -- -- -- -- History: -- -- -- -- Date Ver Author Description -- -- --------- ----- ------ -------------------------------------------------- -- -- 23-AUG-06 01.00 SRR First release -- -- -- -- ------------------------------------------------------------------------- -- DROP INDEX mapping_histories_pk / CREATE UNIQUE INDEX mapping_histories_pk ON mapping_histories ( run_no ) /

Control Repository - mapping_histories_t.sql

-- ------------------------------------------------------------------------- -- -- -- -- Title: mapping_histories_t.sql -- -- Author: Steve Roach -- -- Date: 23-AUG-2006 -- -- Version: 01.00 -- -- -- -- Description: Create table MAPPING_HISTORIES. -- -- -- -- History: -- -- -- -- Date Ver Author Description -- -- --------- ----- ------ -------------------------------------------------- -- -- 23-AUG-06 01.00 SRR First release -- -- -- -- ------------------------------------------------------------------------- -- DROP TABLE mapping_histories CASCADE CONSTRAINTS / CREATE TABLE mapping_histories ( run_no NUMBER NOT NULL ,mapping_library VARCHAR2(30) NOT NULL ,start_tsp DATE NOT NULL ,end_tsp DATE NULL ) / COMMENT ON TABLE mapping_histories IS 'A history of mapping runs.' / COMMENT ON COLUMN mapping_histories.run_no IS 'Primary key. Generated from RUN_NO_S.' / COMMENT ON COLUMN mapping_histories.mapping_library IS 'Foreign key from MAPPING_LIBRARIES.MAPPING_NAME.' / COMMENT ON COLUMN mapping_histories.start_tsp IS 'Timestamp of mapping run start.' / COMMENT ON COLUMN mapping_histories.end_tsp IS 'Timestamp of mapping run end.' /

Control Repository - mapping_histories_a.sql

-- ------------------------------------------------------------------------- -- -- -- -- Title: mapping_histories_a.sql -- -- Author: Steve Roach -- -- Date: 23-AUG-2006 -- -- Version: 01.00 -- -- -- -- Description: Recreate all objects for table MAPPING_HISTORIES. -- -- -- -- History: -- -- -- -- Date Ver Author Description -- -- --------- ----- ------ -------------------------------------------------- -- -- 23-AUG-06 01.00 SRR First release -- -- -- -- ------------------------------------------------------------------------- -- @mapping_histories_t.sql @mapping_histories_i.sql @mapping_histories_c.sql

Control Repository - mapping_libraries_c.sql

-- ------------------------------------------------------------------------- -- -- -- -- Title: mapping_libraries_c.sql -- -- Author: Steve Roach -- -- Date: 23-AUG-2006 -- -- Version: 01.00 -- -- -- -- Description: Create constraints for table MAPPING_LIBRARIES. -- -- -- -- History: -- -- -- -- Date Ver Author Description -- -- --------- ----- ------ -------------------------------------------------- -- -- 23-AUG-06 01.00 SRR First release -- -- -- -- ------------------------------------------------------------------------- -- ALTER TABLE mapping_libraries DROP CONSTRAINT mapping_libraries_pk / ALTER TABLE mapping_libraries DROP CONSTRAINT mapping_libraries_f1 / ALTER TABLE mapping_libraries DROP CONSTRAINT mapping_libraries_f2 / ALTER TABLE mapping_libraries ADD CONSTRAINT mapping_libraries_pk PRIMARY KEY ( mapping_name ) USING INDEX mapping_libraries_pk / ALTER TABLE mapping_libraries ADD CONSTRAINT mapping_libraries_f1 FOREIGN KEY ( rep_from ) REFERENCES repositories(name) / ALTER TABLE mapping_libraries ADD CONSTRAINT mapping_libraries_f2 FOREIGN KEY ( rep_to ) REFERENCES repositories(name) /

Control Repository - mapping_libraries_i.sql

-- ------------------------------------------------------------------------- -- -- -- -- Title: mapping_libraries_i.sql -- -- Author: Steve Roach -- -- Date: 23-AUG-2006 -- -- Version: 01.00 -- -- -- -- Description: Create indexes for table MAPPING_LIBRARIES. -- -- -- -- History: -- -- -- -- Date Ver Author Description -- -- --------- ----- ------ -------------------------------------------------- -- -- 23-AUG-06 01.00 SRR First release -- -- -- -- ------------------------------------------------------------------------- -- DROP INDEX mapping_libraries_pk / CREATE UNIQUE INDEX mapping_libraries_pk ON mapping_libraries ( mapping_name ) /

Control Repository - mapping_libraries_t.sql

-- ------------------------------------------------------------------------- -- -- -- -- Title: mapping_libraries_t.sql -- -- Author: Steve Roach -- -- Date: 23-AUG-2006 -- -- Version: 01.00 -- -- -- -- Description: Create table MAPPING_LIBRARIES. -- -- -- -- History: -- -- -- -- Date Ver Author Description -- -- --------- ----- ------ -------------------------------------------------- -- -- 23-AUG-06 01.00 SRR First release -- -- -- -- ------------------------------------------------------------------------- -- DROP TABLE mapping_libraries CASCADE CONSTRAINTS / CREATE TABLE mapping_libraries ( mapping_name VARCHAR2(30) NOT NULL ,rep_from VARCHAR2(50) NOT NULL ,rep_to VARCHAR2(50) NOT NULL ) / COMMENT ON TABLE mapping_libraries IS 'A library of all available mappings.' / COMMENT ON COLUMN mapping_libraries.mapping_name IS 'Primary Key. Name of the mapping.' / COMMENT ON COLUMN mapping_libraries.rep_from IS 'The FROM repository for data movement. Foreign key from REPOSITORIES.NAME.' / COMMENT ON COLUMN mapping_libraries.rep_to IS 'The TO repository for data movement. Foreign key from REPOSITORIES.NAME.' /

Control Repository - mapping_libraries_a.sql

-- ------------------------------------------------------------------------- -- -- -- -- Title: mapping_libraries_a.sql -- -- Author: Steve Roach -- -- Date: 23-AUG-2006 -- -- Version: 01.00 -- -- -- -- Description: Recreate all objects for table MAPPING_LIBRARIES. -- -- -- -- History: -- -- -- -- Date Ver Author Description -- -- --------- ----- ------ -------------------------------------------------- -- -- 23-AUG-06 01.00 SRR First release -- -- -- -- ------------------------------------------------------------------------- -- @mapping_libraries_t.sql @mapping_libraries_i.sql @mapping_libraries_c.sql

Control Repository - repositories_d.sql

-- ------------------------------------------------------------------------- -- -- -- -- Title: repositories_d.sql -- -- Author: Steve Roach -- -- Date: 23-AUG-2006 -- -- Version: 01.00 -- -- -- -- Description: Create seed data for table REPOSITORIES. -- -- -- -- History: -- -- -- -- Date Ver Author Description -- -- --------- ----- ------ -------------------------------------------------- -- -- 23-AUG-06 01.00 SRR First release -- -- -- -- ------------------------------------------------------------------------- -- DELETE FROM repositories / COMMIT / INSERT INTO repositories ( name ) VALUES ( 'CONTROL' ) / INSERT INTO repositories ( name ) VALUES ( 'EXTERNAL FILE' ) / INSERT INTO repositories ( name ) VALUES ( 'EXTERNAL TABLE' ) / INSERT INTO repositories ( name ) VALUES ( 'EXTERNAL_HTML' ) / INSERT INTO repositories ( name ) VALUES ( 'ODS' ) / INSERT INTO repositories ( name ) VALUES ( 'STAGING' ) / INSERT INTO repositories ( name ) VALUES ( 'VALIDATION' ) / INSERT INTO repositories ( name ) VALUES ( 'WAREHOUSE' ) / COMMIT /

Control Repository - repositories_c.sql

-- ------------------------------------------------------------------------- -- -- -- -- Title: repositories_c.sql -- -- Author: Steve Roach -- -- Date: 23-AUG-2006 -- -- Version: 01.00 -- -- -- -- Description: Create constraints for table REPOSITORIES. -- -- -- -- History: -- -- -- -- Date Ver Author Description -- -- --------- ----- ------ -------------------------------------------------- -- -- 23-AUG-06 01.00 SRR First release -- -- -- -- ------------------------------------------------------------------------- -- ALTER TABLE repositories DROP CONSTRAINT repositories_pk / ALTER TABLE repositories ADD CONSTRAINT repositories_pk PRIMARY KEY ( name ) USING INDEX repositories_pk /

Control Repository - repositories_i.sql

-- ------------------------------------------------------------------------- -- -- -- -- Title: repositories_i.sql -- -- Author: Steve Roach -- -- Date: 23-AUG-2006 -- -- Version: 01.00 -- -- -- -- Description: Create indexes for table REPOSITORIES. -- -- -- -- History: -- -- -- -- Date Ver Author Description -- -- --------- ----- ------ -------------------------------------------------- -- -- 23-AUG-06 01.00 SRR First release -- -- -- -- ------------------------------------------------------------------------- -- DROP INDEX repositories_pk / CREATE UNIQUE INDEX repositories_pk ON repositories ( name ) /

Control Repository - repositories_t.sql

-- ------------------------------------------------------------------------- -- -- -- -- Title: repositories_t.sql -- -- Author: Steve Roach -- -- Date: 23-AUG-2006 -- -- Version: 01.00 -- -- -- -- Description: Create table REPOSITORIES. -- -- -- -- History: -- -- -- -- Date Ver Author Description -- -- --------- ----- ------ -------------------------------------------------- -- -- 23-AUG-06 01.00 SRR First release -- -- -- -- ------------------------------------------------------------------------- -- DROP TABLE repositories CASCADE CONSTRAINTS / CREATE TABLE repositories ( name VARCHAR2(50) NOT NULL ) / COMMENT ON TABLE repositories IS 'Contains repository references.' / COMMENT ON COLUMN repositories.name IS 'Name of repository.' /

Control Repository - repositories_a.sql

-- ------------------------------------------------------------------------- -- -- -- -- Title: repositories_a.sql -- -- Author: Steve Roach -- -- Date: 23-AUG-2006 -- -- Version: 01.00 -- -- -- -- Description: Recreate all objects for table REPOSITORIES. -- -- -- -- History: -- -- -- -- Date Ver Author Description -- -- --------- ----- ------ -------------------------------------------------- -- -- 25-JUL-06 01.00 SRR First release -- -- -- -- ------------------------------------------------------------------------- -- @repositories_t.sql @repositories_i.sql @repositories_c.sql

Full Build

To build the data warehouse described here do the following in the specified order:

• Create the schemas; CTL, STA, VAL, ODS and WHS.
• Warehouse Libraries - Utilities
• Warehouse Libraries - Number Functions
  
• Warehouse Libraries - String Functions
• Warehouse Libraries - Date Functions
  
• Control Repository
• Get HTML
• Extract HTML Data
• Country Dimension
  

Get HTML - Table HTML_SPECIAL_ENTITY_CODES Specification

• Name: html_special_entity_codes
• Description: A conversion table that matches the special entity codes to the characters they represent.
  
• Columns:
• Name: special_entity_code
  
• Description: The common special entity code without the ampersand and semi-colon - case sensitive.
  
• Datatype:VARCHAR2(6)
  
• Null: No
  
• Unique: Yes
  
• Part of PK: Yes
  
• Name: character
  
• Description: The character represented by the code.
  
• Datatype: VARCHAR2(1)
  
• Null: No
  
• Unique: No
  
• Part of PK: No
• Name: description
  
• Description: A description of the code.
  
• Datatype: VARCHAR2(100)
  
• Null: No
  
• Unique: No
  
• Part of PK: No

Country Dimension - m_s2v_countries_b.sql

-- ------------------------------------------------------------------------- -- -- -- -- Title: m_s2v_countries_b.sql -- -- Author: Steve Roach -- -- Date: 15-SEP-2006 -- -- Version: 01.00 -- -- -- -- Description: Container for all procedures and functions relating to -- -- mapping data for the Countries Dimension from STA to VAL. -- -- -- -- History: -- -- -- -- Date Ver Author Description -- -- --------- ----- ------ -------------------------------------------------- -- -- 16-SEP-06 01.00 SRR First release -- -- -- -- ------------------------------------------------------------------------- -- -- Package body definition. CREATE OR REPLACE PACKAGE BODY m_s2v_countries AS -- validate_country_name -- Function to return a validated country name or throw an exception if -- invalid. FUNCTION validate_country_name(p_country_name VARCHAR2) RETURN VARCHAR2 IS l_valid_country VARCHAR2(4000); e_country_name_too_long EXCEPTION; BEGIN -- Replace all Special Control Entity Characters, and strip leading and -- trailing spaces from the unvalidated country name. l_valid_country := LTRIM(RTRIM(get_html.replace_scec(p_country_name))); -- If the validated country name is longer than 100 characters... IF LENGTH(l_valid_country) > 100 THEN -- Throw an exception. RAISE e_country_name_too_long; END IF; -- Return the validated country code. RETURN l_valid_country; EXCEPTION WHEN e_country_name_too_long THEN RAISE_APPLICATION_ERROR(-20001, 'm_s2v_countries.validate_country_name: Error: ' || 'Country Name too long; l_valid_country = [' || l_valid_country || '], length = [' || LENGTH(l_valid_country) || '].'); WHEN others THEN RAISE_APPLICATION_ERROR(-20001, 'm_s2v_countries.validate_country_name: Error: [' || SQLERRM || ']. p_country_name = [' || p_country_name || '].'); END validate_country_name; -- validate_iso2 -- Function to return a validated ISO2 code or throw an exception if -- invalid. FUNCTION validate_iso2(p_iso2 VARCHAR2) RETURN VARCHAR2 IS l_valid_iso2 VARCHAR2(4000); e_iso2_code_too_long EXCEPTION; BEGIN -- Replace trailing asterix's, and strip leading and trailing spaces from -- the unvalidated ISO2 code. l_valid_iso2 := RTRIM(LTRIM(RTRIM(p_iso2)), '*'); -- If the validated ISO2 code is longer than 2 characters... IF LENGTH(l_valid_iso2) > 2 THEN -- Throw an exception. RAISE e_iso2_code_too_long; END IF; -- Return the validated ISO2 code. RETURN l_valid_iso2; EXCEPTION WHEN e_iso2_code_too_long THEN RAISE_APPLICATION_ERROR(-20001, 'm_s2v_countries.validate_iso2: Error: ' || 'ISO2 Code too long; l_valid_iso2 = [' || l_valid_iso2 || '], length = [' || LENGTH(l_valid_iso2) || '].'); WHEN others THEN RAISE_APPLICATION_ERROR(-20001, 'm_s2v_countries.validate_iso2: Error: [' || SQLERRM || ']. p_iso2 = [' || p_iso2 || '].'); END validate_iso2; -- validate_iso3 -- Function to return a validated ISO3 code or throw an exception if -- invalid. FUNCTION validate_iso3(p_iso3 VARCHAR2) RETURN VARCHAR2 IS l_valid_iso3 VARCHAR2(4000); e_iso3_code_too_long EXCEPTION; BEGIN -- Replace trailing asterix's, and strip leading and trailing spaces from -- the unvalidated ISO3 code. l_valid_iso3 := RTRIM(LTRIM(RTRIM(p_iso3)), '*'); -- If the validated ISO3 code is longer than 3 characters... IF LENGTH(l_valid_iso3) > 3 THEN -- Throw an exception. RAISE e_iso3_code_too_long; END IF; -- Return the validated ISO3 code. RETURN l_valid_iso3; EXCEPTION WHEN e_iso3_code_too_long THEN RAISE_APPLICATION_ERROR(-20001, 'm_s2v_countries.validate_iso3: Error: ' || 'ISO2 Code too long; l_valid_iso3 = [' || l_valid_iso3 || '], length = [' || LENGTH(l_valid_iso3) || '].'); WHEN others THEN RAISE_APPLICATION_ERROR(-20001, 'm_s2v_countries.validate_iso3: Error: [' || SQLERRM || ']. p_iso3 = [' || p_iso3 || '].'); END validate_iso3; -- validate_un3 -- Function to return a validated UN3 code or throw an exception if -- invalid. FUNCTION validate_un3(p_un3 VARCHAR2) RETURN VARCHAR2 IS l_valid_un3 VARCHAR2(4000); e_un3_code_too_long EXCEPTION; BEGIN -- Strip leading and trailing spaces from the unvalidated UN3 code. l_valid_un3 := LTRIM(RTRIM(p_un3)); -- If the validated UN3 code is longer than 3 characters... IF LENGTH(l_valid_un3) > 3 THEN -- Throw an exception. RAISE e_un3_code_too_long; END IF; -- Return the validated UN3 code. RETURN l_valid_un3; EXCEPTION WHEN e_un3_code_too_long THEN RAISE_APPLICATION_ERROR(-20001, 'm_s2v_countries.validate_un3: Error: ' || 'UN3 Code too long; l_valid_un3 = [' || l_valid_un3 || '], length = [' || LENGTH(l_valid_un3) || '].'); WHEN others THEN RAISE_APPLICATION_ERROR(-20001, 'm_s2v_countries.validate_un3: Error: [' || SQLERRM || ']. p_un3 = [' || p_un3 || '].'); END validate_un3; -- 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_S2V_COUNTRIES', -- run_no = local variable to capture the run number. ctl_gen.initialise_mapping('M_S2V_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_s2v_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_valid_country_name VARCHAR2(100); l_valid_iso2 VARCHAR2(2); l_valid_iso3 VARCHAR2(3); l_valid_un3 VARCHAR2(3); BEGIN -- Delete all data from V_COUNTRIES - discarding the previous run. DELETE FROM v_countries; -- For each row in S_COUNTRIES... FOR r_country IN ( SELECT * FROM s_countries ORDER BY name ) LOOP -- dbms_output.put_line('r_country.name = ' || r_country.name); -- Validate country name using validate_country_name with parameter; -- p_country_name = r_country.name. l_valid_country_name := validate_country_name(r_country.name); -- Validate ISO2 code using validate_iso2 with parameter; -- p_iso2 = r_country.iso2_code l_valid_iso2 := validate_iso2(r_country.iso2_code); -- Validate ISO3 code using validate_iso3 with parameter; -- p_iso3 = r_country.iso3_code l_valid_iso3 := validate_iso3(r_country.iso3_code); -- Validate UN3 number using validate_un3 with parameter; -- p_un3 = r_country.un3_number l_valid_un3 := validate_un3(r_country.un3_code); -- dbms_output.put_line('l_valid_country_name = ' || -- l_valid_country_name); -- dbms_output.put_line('l_valid_iso2 = ' || -- l_valid_iso2); -- dbms_output.put_line('l_valid_iso3 = ' || -- l_valid_iso3); -- dbms_output.put_line('l_valid_un3 = ' || -- l_valid_un3); -- Create a new v_countries row with the validated data. INSERT INTO v_countries ( name ,iso2_code ,iso3_code ,un3_number ,run_no ,ins_tsp ) VALUES ( l_valid_country_name ,l_valid_iso2 ,l_valid_iso3 ,l_valid_un3 ,p_run_no ,SYSDATE ); END LOOP; EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20001, 'm_s2v_countries.map(2) Error: [' || SQLERRM || '].'); END map; -- Overload 2 END m_s2v_countries; / SHOW ERRORS