Humble Trader

Wednesday, November 15, 2006

Warehouse Libraries - Number Functions

Links to posts for this sub-system:

posted by Cat Play Crew at 11:16 am | 0 comments

Warehouse Libraries - Number Functions Implementation Notes

  • Upload to CTL:
    • lib_number.sql
  • Run the following in order:
    • CTL script: lib_number.sql
    • CTL command: EXEC lib_util.create_grants;
    • STA command: EXEC lib_util.create_synonyms;
    • VAL command: EXEC lib_util.create_synonyms;
    • ODS command: EXEC lib_util.create_synonyms;
  • Run the test script; CTL: lib_number_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: lib_number_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: lib_number_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: lib_number_test.sql. NOTE: Best run from the SQL Command Line.
    • Each test outputs a result and, at the end, a pass/fail summary.

posted by Cat Play Crew at 11:13 am | 0 comments

Warehouse Libraries - Number Functions Configuration Items

This is a list of links to posts that comprise the configuration of the sub-system in the order that they must be implemented:

posted by Cat Play Crew at 11:10 am | 0 comments

Warehouse Libraries - Package CTL.LIB_NUMBER Specification

  • Package: lib_number
  • Description: A set of library functions and procedures to support number manipulation.
    • Function: cardinal_num
      • Description: Return the cardinal string equivalent of the input number.
      • Parameters:
        • p_number:
          • Datatype: NUMBER
          • Direction: IN
          • Description: The number.
      • Return:
        • Datatype: VARCHAR2
        • Description: Cardinal.
      • Action:
        • Get the last digit
        • Create a cardinal string based on the last digit.
        • Return the cardinal string.

posted by Cat Play Crew at 11:06 am | 0 comments

Warehouse Libraries - Number Functions Technical Specification

A number of objects will be built to support this sub-system. Objects are normally located in specific schemas depending on their role:

  • CTL: Control information.
  • STA: Staged (raw) data.
  • VAL: Validated (in terms of datatypes, column widths, etc.) data.
  • ODS: Operational Data Store.
  • WHS: The data warehouse.
CTL:

posted by Cat Play Crew at 11:03 am | 0 comments

Warehouse Libraries - Number Functions Functional Specification

This sub-system is a library collection of various procedures and functions that support number manipulation.

posted by Cat Play Crew at 11:02 am | 0 comments

Sunday, October 08, 2006

Serialised Bulk Operations

This is a demonstration of how to serialise bulk operations.

Problem:

A bulk operation takes data from a table and inserts or updates another, i.e. bulk insert or bulk update. The source table can, itself, receive updates or inserts from other processes so, in order to know what has been processed and what has not, a flag column is added to the source. This flag takes the values 'Y' (processed) and 'N' (not processed). The bulk operation identifies rows set to 'N', processes them and then flips the flag to 'Y' to indicated that it is done.

It is possible that, while the bulk process is running, other processes change or add data to the source. Should the bulk process be run by another session while one is already running, there is a chance that transactional integrity breaks.

Also, the source can contain a lot of data so performance needs to be considered.

Solution:

This solution uses SELECT ... FOR UPDATE to ensure transactional integrity, and BULK COLLECT ... LIMIT and FORALL for performance.

Example Procedure:

  • This is presented as an anonymous block but can be implemented in a package procedure.
  • Create an INDEX BY table type based on the primary key of the source table.
  • Instantate an INDEX BY table based on the type.
  • Create a local variable to hold the LIMIT (i.e. the number of rows processed in each run of the loop). This would be provisionally set to 1000 and later changed in the light of any tuning information.
  • Create a cursor to fetch the primary keys of the source into the INDEX BY table where the flag is 'N' using FOR UPDATE. This locks the rows to be processed.
  • Open the cursor. This applies the lock.
  • Loop. Start of cursor loop - this is required to loop on the LIMIT clause in the FETCH.
    • Fetch the cursor BULK COLLECT into the INDEX BY table with the LIMIT clause.
    • *** Do the bulk process *** - At this point the primary bulk process is executed. The target table is inserted into or updated based on the set of primary keys in the INDEX BY table.
    • Update the source flags. Use FORALL to update the flags based on the set of primary keys in the INDEX BY table.
    • Exit the loop when there are no more rows fetched by the cursor.
  • End; Loop. Start of cursor loop...
  • Commit work.
  • Close the cursor.
Here is a (non-workng) stub:

-- Anonymous block:
DECLARE
  -- Create an INDEX BY table type based on the primary key of the source
  -- table.
  TYPE x_type IS TABLE OF source_table.id%TYPE INDEX BY BINARY_INTEGER;

  -- Instantate an INDEX BY table based on the type.
  x_table x_type;

  -- Create a local variable to hold the LIMIT (i.e. the number of rows
  --processed in each run of the loop).
  l_limit        NUMBER := 1000;

  -- Create a local to count any errors.
  l_errors       NUMBER;

  -- Create a cursor to fetch the primary keys of the source into the
  -- INDEX BY table where the flag is 'N' using FOR UPDATE. This locks
  -- the rows to be processed.
  CURSOR r_cursor IS
     SELECT id
     FROM source_table
     WHERE flag = 'N'
     FOR UPDATE;

BEGIN
  -- Open the cursor. This applies the lock.
  OPEN r_cursor;

  -- Loop. Start of cursor loop - this is required to loop on the LIMIT
  -- clause in the FETCH.
  LOOP
     -- Fetch the cursor BULK COLLECT into the INDEX BY table with the
     -- LIMIT clause.
     FETCH r_cursor BULK COLLECT INTO x_table LIMIT l_limit;

     -- *** Do the bulk process ***
     -- At this point the primary bulk process is executed. The target
     -- table is inserted into or updated based on the set of primary keys
     -- in the INDEX BY table. The SAVE EXCEPTIONS clause ensures that all
     -- bulk errors are kept.
     FORALL l_i IN x_table.FIRST .. x_table.LAST SAVE EXCEPTIONS
        INSERT INTO target_table
        SELECT *
        FROM source_table
        WHERE id = x_table(l_i);

     -- Update the source flags. Use FORALL to update the flags based on
     -- the set of primary keys in the INDEX BY table.
     FORALL l_i IN x_table.FIRST .. x_table.LAST
        UPDATE source_table
        SET flag = 'Y'
        WHERE id = x_tabla(l_i);

     -- Exit the loop when there are no more rows fetched by the cursor.
     EXIT WHEN r_ids%NOTFOUND;

  -- End; Loop. Start of cursor loop...
  END LOOP;

  -- Commit work.
  COMMIT;

  -- Close the cursor.
  CLOSE r_ids;

-- Bulk exception handler.
EXCEPTION
  WHEN others THEN

     -- Commit successful work.
     COMMIT;

     -- Count the errors.
     l_errors := SQL%BULK_EXCEPTIONS.COUNT;

     FOR l_i IN 1 .. l_errors
     LOOP
        dbms_output.put_line('Error(id=' ||
           x_table(SQL%BULK_EXCEPTIONS(l_i).ERROR_INDEX) || ') = ' ||
           SQLERRM(-SQL%BULK_EXCEPTIONS(l_i).ERROR_CODE));
     END LOOP;
END;
/


Testing:

There are two test scripts to be run in different sessions - these simulate different processes operating on the same object at the same time. The first script contains a delay statement. This 5 second period is to give enough time to switch sessions and start the second script:

test_script_a.sql

DROP TABLE test
/

CREATE table test
(
  id          NUMBER
 ,flag        VARCHAR2(1) NOT NULL
 ,sid         NUMBER
 ,fetch_loop  NUMBER
)
/

INSERT INTO test
(
  id
 ,flag
 ,sid
 ,fetch_loop
)
VALUES
(
  1
 ,'N'
 ,NULL
 ,NULL
)
/

INSERT INTO test
(
  id
 ,flag
 ,sid
 ,fetch_loop
)
VALUES
(
  2
 ,'N'
 ,NULL
 ,NULL
)
/

INSERT INTO test
(
  id
 ,flag
 ,sid
 ,fetch_loop
)
VALUES
(
  3
 ,'N'
 ,NULL
 ,NULL
)
/

INSERT INTO test
(
  id
 ,flag
 ,sid
 ,fetch_loop
)
VALUES
(
  4
 ,'N'
 ,NULL
 ,NULL
)
/

INSERT INTO test
(
  id
 ,flag
 ,sid
 ,fetch_loop
)
VALUES
(
  5
 ,'N'
 ,NULL
 ,NULL
)
/

INSERT INTO test
(
  id
 ,flag
 ,sid
 ,fetch_loop
)
VALUES
(
  6
 ,'N'
 ,NULL
 ,NULL
)
/

INSERT INTO test
(
  id
 ,flag
 ,sid
 ,fetch_loop
)
VALUES
(
  7
 ,'Y'
 ,NULL
 ,NULL
)
/

INSERT INTO test
(
  id
 ,flag
 ,sid
 ,fetch_loop
)
VALUES
(
  8
 ,'Y'
 ,NULL
 ,NULL
)
/

COMMIT
/

SELECT *
FROM test
ORDER BY id
/

PROMPT Run test_script_b.sql in another session now.

DECLARE
  TYPE id_type IS TABLE OF test.id%TYPE INDEX BY BINARY_INTEGER;

  l_sid          NUMBER;
  l_fetch_loop   NUMBER := 0;
  l_limit        NUMBER := 3;

  id_tab id_type;

  CURSOR r_ids IS
     SELECT id
     FROM test
     WHERE flag = 'N'
     FOR UPDATE;

BEGIN
  SELECT sid
  INTO l_sid
  FROM v$session
  WHERE audsid = SYS_CONTEXT('userenv', 'sessionid');

  OPEN r_ids;

  dbms_lock.sleep(5);

  LOOP
     l_fetch_loop := l_fetch_loop + 1;

     FETCH r_ids BULK COLLECT INTO id_tab LIMIT l_limit;

     FORALL l_i IN id_tab.FIRST .. id_tab.LAST
        UPDATE test
        SET flag = 'Y'
           ,sid = l_sid
           ,fetch_loop = l_fetch_loop
        WHERE id = id_tab(l_i);

     EXIT WHEN r_ids%NOTFOUND;
  END LOOP;

  COMMIT;

  CLOSE r_ids;
END;
/




test_script_b.sql

SET SERVEROUTPUT ON SIZE 1000000

PROMPT -
PROMPT - Initialising...

UPDATE test
SET flag = 'N'
WHERE id IN (7, 8)
/

PROMPT - Done
PROMPT -

PROMPT - Current state of table:

SELECT *
FROM test
ORDER BY id
/

DECLARE
   TYPE id_type IS TABLE OF test.id%TYPE INDEX BY BINARY_INTEGER;

   l_sid                    NUMBER;
   l_fetch_loop             NUMBER := 0;
   l_count_other_fl_1       NUMBER;
   l_count_other_fl_2       NUMBER;
   l_count_this_fl_1        NUMBER;
   l_passed                 BOOLEAN := TRUE;
   l_errors                 NUMBER;
   l_limit                  NUMBER := 3;

   id_tab id_type;

   CURSOR r_ids IS
      SELECT id
      FROM test
      WHERE flag = 'N'
      FOR UPDATE;

   CURSOR r_err IS
      SELECT id
      FROM test
      WHERE flag = 'Y'
      FOR UPDATE;

BEGIN
   dbms_output.put_line('+- Test 1 start:');
   dbms_output.put_line('|  Serialised transaction:');
   dbms_output.put_line('|');
   dbms_output.put_line('| Expected result: 6 rows updated to ''Y'' by ' ||
      'other process, 3 by fetch loop');
   dbms_output.put_line('|                  1 and 3 by fetch loop 2.');
   dbms_output.put_line('|                  2 rows updated to ''Y'' by ' ||
      'this process by fetch loop 1.');
   dbms_output.put_line('|');

   SELECT sid
   INTO l_sid
   FROM v$session
   WHERE audsid = SYS_CONTEXT('userenv', 'sessionid');

   OPEN r_ids;

   LOOP
      l_fetch_loop := l_fetch_loop + 1;

      FETCH r_ids BULK COLLECT INTO id_tab LIMIT l_limit;

      FORALL l_i IN id_tab.FIRST .. id_tab.LAST
         UPDATE test
         SET flag = 'Y'
            ,sid = l_sid
            ,fetch_loop = l_fetch_loop
         WHERE id = id_tab(l_i);

      EXIT WHEN r_ids%NOTFOUND;
   END LOOP;

   COMMIT;

   CLOSE r_ids;

   SELECT COUNT(*)
   INTO l_count_other_fl_1
   FROM test
   WHERE flag = 'Y'
     AND sid != l_sid
     AND fetch_loop = 1;

   dbms_output.put_line('| Result: Updated to ''Y'' by other process by ' ||
      'fetch loop 1: ' || l_count_other_fl_1);

   SELECT COUNT(*)
   INTO l_count_other_fl_2
   FROM test
   WHERE flag = 'Y'
     AND sid != l_sid
     AND fetch_loop = 2;

   dbms_output.put_line('|         Updated to ''Y'' by other process by ' ||
      'fetch loop 2: ' || l_count_other_fl_2);

   SELECT COUNT(*)
   INTO l_count_this_fl_1
   FROM test
   WHERE flag = 'Y'
     AND sid = l_sid
     AND fetch_loop = 1;

   dbms_output.put_line('|         Updated to ''Y'' by this process by ' ||
      'fetch loop 1: ' || l_count_this_fl_1);
   dbms_output.put_line('|');

   dbms_output.put_line('| +----------+');

   IF l_count_other_fl_1 = 3
      AND
      l_count_other_fl_2 = 3
      AND
      l_count_this_fl_1 = 2
   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('+- Test 1 end.');

   dbms_output.put_line('-');

   dbms_output.put_line('+- Test 2 start:');
   dbms_output.put_line('|  Error handling:');
   dbms_output.put_line('|');
   dbms_output.put_line('| Expected result: 8 failed updates.');
   dbms_output.put_line('|');

   OPEN r_err;

   l_fetch_loop := 0;

   DELETE FROM test
   WHERE id IN (2, 6);

   BEGIN
      LOOP
         l_fetch_loop := l_fetch_loop + 1;

         FETCH r_err BULK COLLECT INTO id_tab;

         FORALL l_i IN id_tab.FIRST .. id_tab.LAST SAVE EXCEPTIONS
            UPDATE test
            SET flag = NULL
               ,sid = l_sid
               ,fetch_loop = l_fetch_loop
            WHERE id = id_tab(l_i);

         EXIT WHEN r_err%NOTFOUND;
      END LOOP;

      COMMIT;
   EXCEPTION
      WHEN others THEN
         COMMIT;

         l_errors := SQL%BULK_EXCEPTIONS.COUNT;

         dbms_output.put_line('| Result: Failures: ' || l_errors);
         dbms_output.put_line('|');

         FOR l_i IN 1 .. l_errors
         LOOP
            dbms_output.put_line('| Error(id=' ||
               id_tab(SQL%BULK_EXCEPTIONS(l_i).ERROR_INDEX) || ') = ' ||
               SQLERRM(-SQL%BULK_EXCEPTIONS(l_i).ERROR_CODE));
         END LOOP;

         dbms_output.put_line('|');
   END;

   CLOSE r_err;

   dbms_output.put_line('| +----------+');

   IF l_errors = 6
   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('+- Test 1 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;
/

PROMPT - Final state of table:

SELECT *
FROM test
ORDER BY id
/

DROP TABLE test
/


posted by Cat Play Crew at 7:15 pm | 0 comments

PL/SQL Developer

This post contains links to other posts that make up a library of PL/SQL scripts and techniques:

posted by Cat Play Crew at 7:12 pm | 0 comments

Sunday, October 01, 2006

Country Dimension - Process M_O2W_COUNTRIES Specification

  • Package: m_o2w_countries
  • Description: Container for all procedures and functions relating to mapping data for the Countries Dimension from ODS to WHS.
    • Procedure: map (Overload 1)
      • Description: Wrapper to do mapping with housekeeping.
      • Parameters: None.
      • Action:
        • Run initialise mapping with parameters p_mapping = 'M_O2W_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 ODS.COUNTRIES to WHS.D_COUNTRIES.
      • Parameters:
        • p_run_no:
          • Datatype: NUMBER
          • Direction: IN
          • Description: Run number of this map run.
        • Action:
          • Insert into ODS.COUNTRIES any country in VAL.V_COUNTRIES that is not already there.
          • Update and rows in D_COUNTRIES that differ in detail from COUNTRIES.
          • Commit work.

posted by Cat Play Crew at 8:35 pm | 0 comments

Country Dimension - Process M_V2O_COUNTRIES Specification

  • Package: m_v2o_countries
  • Description: Container for all procedures and functions relating to mapping data for the Countries Dimension from VAL to ODS.
    • Procedure: map (Overload 1)
      • Description: Wrapper to do mapping with housekeeping.
      • Action:
        • Run initialise mapping with parameters p_mapping = 'M_V2O_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 VAL.V_COUNTRIES to ODS.COUNTRIES.
      • Parameters:
        • p_run_no:
          • Datatype: NUMBER
          • Direction: IN
          • Description: The run number for this run.
      • Action:
        • Insert into ODS.COUNTRIES any country in VAL.V_COUNTRIES that is not already there.
        • Commit work.

posted by Cat Play Crew at 8:17 pm | 0 comments

Country Dimension - m_o2w_countries_test.sql 

SET SERVEROUTPUT ON SIZE 1000000;

DECLARE
  l_run_no                  NUMBER;
  l_mapping_histories_count NUMBER;
  l_countries_count         NUMBER;
  l_d_countries_count       NUMBER;
  l_init_d_countries_count  NUMBER;
  l_final_d_countries_count NUMBER;
  l_iso2_code               VARCHAR2(2);
  l_iso3_code               VARCHAR2(3);
  l_un3_number              VARCHAR2(3);
  l_start_date              DATE;
  l_end_date                DATE;
  l_row_data                VARCHAR2(4000);
  l_passed                  BOOLEAN := TRUE;
BEGIN
  -- Initialisation code.
  DELETE FROM d_country;

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

  m_v2o_countries.map;

  dbms_output.put_line('- Fetch O_COUNTRIES prepared.');

  SELECT COUNT(*)
  INTO l_countries_count
  FROM o_countries;

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

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

  m_o2w_countries.map;

  SELECT MAX(run_no)
  INTO l_run_no
  FROM mapping_histories
  WHERE mapping_library = 'M_O2W_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_d_countries_count
  FROM d_country;

  dbms_output.put_line('|         D_COUNTRY row count = ' ||
     l_d_countries_count);
  dbms_output.put_line('|');

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

  DELETE FROM d_country
  WHERE iso2_code = 'GB';

  UPDATE d_country
  SET iso2_code = 'XX'
  WHERE iso2_code = 'AF';

  UPDATE d_country
  SET iso3_code = 'XXX'
  WHERE iso2_code = 'AL';

  UPDATE d_country
  SET un3_number = 'XXX'
  WHERE iso2_code = 'DZ';

  UPDATE d_country
  SET start_date = '01-FEB-2006'
  WHERE iso2_code = 'AS';

  UPDATE d_country
  SET end_date = '01-FEB-2006'
  WHERE iso2_code = 'AD';

  COMMIT;

  dbms_output.put_line('+- PROCEDURE m_o2w_countries.map test 2 start:');
  dbms_output.put_line('|  * Load and update data to WHS.D_COUNTRY *');
  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 D_COUNTRY');
  dbms_output.put_line
     ('| Expected Result: Updates 5 rows in D_COUNTRY');
  dbms_output.put_line('|');

  SELECT COUNT(*)
  INTO l_init_d_countries_count
  FROM d_country;

  dbms_output.put_line('|         Initial D_COUNTRY row count = ' ||
     l_init_d_countries_count);
  dbms_output.put_line('|         Rows to update: = ');

  FOR r_row IN
  (
     SELECT name
           ,iso2_code
           ,iso3_code
           ,un3_number
           ,start_date
           ,end_date
     FROM d_country
     WHERE iso2_code IN ('XX', 'AL', 'DZ', 'AS', 'AD')
  )
  LOOP
     dbms_output.put_line('| [' || r_row.name || ', ' || r_row.iso2_code ||
        ', ' || r_row.iso3_code || ', ' || r_row.un3_number || ', ' ||
        r_row.start_date || ', ' || r_row.end_date || ']');
  END LOOP;

  dbms_output.put_line('|');

  m_o2w_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_d_countries_count
  FROM o_countries;

  dbms_output.put_line('|         Final O_COUNTRIES row count = ' ||
     l_final_d_countries_count);
  dbms_output.put_line('|         Rows updated: = ');

  FOR r_row IN
  (
     SELECT name
           ,iso2_code
           ,iso3_code
           ,un3_number
           ,start_date
           ,end_date
     FROM d_country
     WHERE iso2_code IN ('AF', 'AL', 'DZ', 'AS', 'AD')
  )
  LOOP
     dbms_output.put_line('| [' || r_row.name || ', ' || r_row.iso2_code ||
        ', ' || r_row.iso3_code || ', ' || r_row.un3_number || ', ' ||
        r_row.start_date || ', ' || r_row.end_date || ']');

     IF r_row.iso2_code = 'AF'
     THEN
        l_iso2_code := r_row.iso2_code;
     END IF;

     IF r_row.iso2_code = 'AL'
     THEN
        l_iso3_code := r_row.iso3_code;
     END IF;

     IF r_row.iso2_code = 'DZ'
     THEN
        l_un3_number := r_row.un3_number;
     END IF;

     IF r_row.iso2_code = 'AS'
     THEN
        l_start_date := r_row.start_date;
     END IF;

     IF r_row.iso2_code = 'AD'
     THEN
        l_end_date := r_row.end_date;
     END IF;
  END LOOP;

  dbms_output.put_line('|');

  dbms_output.put_line('| +----------+');
  IF l_mapping_histories_count = 1
     AND
     l_final_d_countries_count - l_init_d_countries_count = 1
     AND
     l_iso2_code = 'AF'
     AND
     l_iso3_code = 'ALB'
     AND
     l_un3_number = '012'
     AND
     l_start_date = '01-JAN-1000'
     AND
     l_end_date = '31-DEC-3000'
  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_o2w_countries.map test 2 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 1:56 am | 0 comments

Saturday, September 30, 2006

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

posted by Cat Play Crew at 9:30 pm | 0 comments

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

posted by Cat Play Crew at 9:29 pm | 0 comments

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
/

posted by Cat Play Crew at 9:06 pm | 0 comments

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
/

posted by Cat Play Crew at 9:00 pm | 0 comments

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

posted by Cat Play Crew at 8:31 pm | 0 comments

Country Dimension - Process M_O2W_COUNTRIES Scripts

Scripts that support this object:

posted by Cat Play Crew at 8:29 pm | 0 comments

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

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

posted by Cat Play Crew at 8:02 pm | 0 comments

Wednesday, September 27, 2006

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

posted by Cat Play Crew at 12:50 am | 0 comments

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

posted by Cat Play Crew at 12:48 am | 0 comments

Country Dimension - Process M_V2O_COUNTRIES Scripts

Scripts that support this object:

posted by Cat Play Crew at 12:47 am | 0 comments

Tuesday, September 26, 2006

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
/

posted by Cat Play Crew at 4:00 am | 0 comments

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

posted by Cat Play Crew at 3:57 am | 0 comments

Monday, September 25, 2006

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.'
/

posted by Cat Play Crew at 11:40 pm | 0 comments

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

posted by Cat Play Crew at 11:38 pm | 0 comments

Country Dimension - Table O_COUNTRIES Script List

Scripts that support this object:

posted by Cat Play Crew at 11:36 pm | 0 comments

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.

posted by Cat Play Crew at 10:44 pm | 0 comments

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

posted by Cat Play Crew at 7:00 am | 0 comments