Migrating OWB to ODI: What about the POST-MAPPING?

In my previous post I looked at solutions for migrating an OWB pre-mapping component to ODI. It turns out a table function was the best solution in my case. But what about the post-mapping, can it be migrated in a similar fashion. After some testing I think that theoretically it should  be possible, it would not be a practical solution though. So for the post-mapping I will go down a different road.

PROCEDURE end_run_step(p_step_id   NUMBER
                      ,p_message   VARCHAR2 DEFAULT NULL)
IS
   PRAGMA AUTONOMOUS_TRANSACTION;
   l_run_number  dwh_runs.run_nr%TYPE;
BEGIN
   UPDATE dwh_run_steps drp
      SET drp.enddate   = SYSDATE
         ,drp.MESSAGE   = p_message
    WHERE id = p_step_id;

   COMMIT;
END;

Again, I would like to prevent using an ODI package, and I want to make sure my post-mapping is the last step in the mapping. Using the table function approach could work, but it will be very hard to ensure it is the last action in the mapping, as well as guaranteeing that the procedure will only run once.
Leaving the code in the Physical Layer End Mapping Command will ensure it is the last step and it will only run one. What about the 2 parameters though? The easiest way to transfer these values from the pre-mapping (where the step_id is generated) is via a public database package variable. We need to change the the DWH_RUNS package, or create a new one, to achieve this. I will assume that modifying the code is no problem and change the package like this:

CREATE OR REPLACE PACKAGE demo.dwh_runs_mgr
AS
   g_step_id  NUMBER;

   FUNCTION start_run
      RETURN NUMBER;

   PROCEDURE end_run(p_run_status VARCHAR2);

   FUNCTION start_run_step(p_mapping_name      VARCHAR2
                          ,p_run_nr        OUT dwh_runs.run_nr%TYPE
                          ,p_previous_scn  OUT dwh_runs.current_scn%TYPE
                          ,p_current_scn   OUT dwh_runs.previous_scn%TYPE)
      RETURN NUMBER;

   PROCEDURE end_run_step(p_step_id   NUMBER
                         ,p_message   VARCHAR2 DEFAULT NULL);
END;

We also need assign a value to the global variable:

FUNCTION start_run_step(p_mapping_name      VARCHAR2
                       ,p_run_nr        OUT dwh_runs.run_nr%TYPE
                       ,p_previous_scn  OUT dwh_runs.current_scn%TYPE
                       ,p_current_scn   OUT dwh_runs.previous_scn%TYPE)
   RETURN NUMBER
IS
   PRAGMA AUTONOMOUS_TRANSACTION;
   l_step_id  dwh_run_steps.id%TYPE;
BEGIN
   BEGIN
      SELECT run_nr
            ,previous_scn
            ,current_scn
        INTO p_run_nr
            ,p_previous_scn
            ,p_current_scn
        FROM dwh_runs
       WHERE run_status = 'R';
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         raise_application_error(-20000
                                ,'No active run');
   END;

   INSERT INTO dwh_run_steps(drn_run_nr
                            ,mapping_name
                            ,startdate)
        VALUES (p_run_nr
               ,p_mapping_name
               ,SYSDATE)
     RETURNING id
          INTO l_step_id;

   COMMIT;

   g_step_id   := l_step_id;
   RETURN l_step_id;
END;

Now all we have to do is add the code to the End Mapping Command and we will have a working mapping:

DECLARE
   p_step_id  NUMERIC := dwh_runs.g_step_id;
   p_message  VARCHAR2(1024);
BEGIN
   "DWH_RUNS_MGR"."END_RUN_STEP"(p_step_id
                                ,p_message);
END;

Don’t forget to set the Technology for the End Mapping Command.