-- ------------------------------------------------------------------------- --
-- --
-- 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
|
0 Comments:
Post a Comment
<< Home