load_etl_employee_body.sql

CREATE OR REPLACE PACKAGE BODY ISZMETL.etl_employee IS
  PROCEDURE main( p_params param_list DEFAULT NULL ) IS
    v_params   obj_param := iszmk.obj_param( p_plist => p_params );
    v_logger   obj_logger;
    v_stmt     VARCHAR2( 100 );
  BEGIN
    /*** AUTOMATICALLY GENERATED PROCEDURE ***/
    /***        DO NOT CHANGE IT           ***/

    v_start_date   := sysdate;
    v_cnt          := 0;
    v_batch_id     := v_params.get_val( 'BATCH_ID' );
    v_run_type     := v_params.get_val( 'RUN_TYPE' );

    -------------------------------
    -- Step: Register procedure run
    -------------------------------
    v_logger       :=
      NEW obj_logger( p_batch_id   => v_batch_id
                    , p_prc_name   => v_prc_name
                    , p_prc_desc   => v_prc_desc
                    , p_date       => v_start_date
                    , p_src_id     => v_src_id
                    , p_run_type   => v_run_type
                    , p_params     => v_params.to_str( )
                     );

    -- execute load steps defined in package header
    FOR step IN v_load_steps.first .. v_load_steps.last
    LOOP
      IF v_logger.step_required( v_load_steps( step ) )
      THEN
        -- call load steps
        v_stmt   := 'CALL ' || v_prc_name || '.' || v_load_steps( step ) || '( :step )';
        v_params.set_val( 'current_step', v_load_steps( step ) );

        EXECUTE IMMEDIATE v_stmt USING IN OUT v_params;

        v_logger.save_checkpoint( v_load_steps( step ) );
      END IF;
    END LOOP;

    -------------------------------
    -- close log master record
    -------------------------------
    v_logger.log_finish( p_rowcnt => v_cnt );
    COMMIT;
  EXCEPTION
    WHEN OTHERS
    THEN
      v_err   := sqlcode;
      v_msg   := sqlerrm;
      ROLLBACK;
      v_logger.log_abort( p_rowcnt => v_cnt, p_status => 'E', p_notes => v_msg );
      RAISE;
  END;


  PROCEDURE main( p_input_params VARCHAR2 ) IS
  BEGIN
    /*** AUTOMATICALLY GENERATED PROCEDURE ***/
    main( obj_param.parse( p_input_params ) );
  END;


  PROCEDURE step_tmp_employee( p_params IN OUT obj_param ) IS
    v_logger   obj_logger := NEW obj_logger( );
  BEGIN
    -------------------------
    -- truncate src table
    -------------------------
    iszmk.prc_trunc_tab( v_src_sch, 'TMP_SCOTT_EMP' );

    -----------------------------------------
    -- start tranzaction log registration
    -----------------------------------------
    v_logger.m_trans_start( 'TMP_SCOTT_EMP', v_src_sch, 'INSERT' );

    -----------------------------------
    -- Load the temp table based from scott.emp
    -----------------------------------
    INSERT INTO tmp_scott_emp( empno
                             , ename
                             , job
                             , mgr
                             , hiredate
                             , sal
                             , comm
                             , deptno
                              )
      SELECT empno
           , ename
           , job
           , mgr
           , hiredate
           , sal
           , comm
           , deptno
        FROM scott.emp;

    -------------------------------
    -- register finished insert statement
    -------------------------------
    v_cnt   := SQL%ROWCOUNT;
    COMMIT;
    v_logger.m_trans_end( p_rowcnt => v_cnt );
  END;


  PROCEDURE step_src_employee( p_params IN OUT obj_param ) IS
    v_logger   obj_logger := NEW obj_logger( );
  BEGIN
    -------------------------
    -- truncate src table
    -------------------------
    iszmk.prc_trunc_tab( v_src_sch, v_src_table );

    -----------------------------------------
    -- start tranzaction log registration
    -----------------------------------------
    v_logger.m_trans_start( v_src_table, v_src_sch, 'INSERT' );

    -----------------------------------
    -- Load the source table based on XXX
    -----------------------------------
    INSERT INTO src_employee( log_id
                            , empno
                            , ename
                            , job
                            , mgr
                            , hiredate
                            , sal
                            , comm
                            , deptno
                             )
      SELECT v_logger.log_id
           , decode( empno, 7788, NULL, empno )
           , ename
           , job
           , mgr
           , hiredate
           , sal
           , comm
           , deptno
        FROM iszmetl.tmp_scott_emp
       LOG ERRORS INTO err_employee ( v_logger.log_id ) REJECT LIMIT UNLIMITED;

    -------------------------------
    -- register finished insert statement
    -------------------------------
    v_cnt   := SQL%ROWCOUNT;
    COMMIT;
    v_logger.m_trans_end( p_rowcnt => v_cnt, p_errtab => 'ERR_EMPLOYEE' );
    -------------------------------
    -- start historical load from source table
    -------------------------------
    iszmk.prc_hist_load( v_dm_sch, v_dm_table );
  END;


  PROCEDURE test( p_params param_list DEFAULT NULL ) IS
    v_params   obj_param := iszmk.obj_param( p_plist => p_params );
  BEGIN
    NULL; -- NOT IMPLEMENTED
  END;
END;
/

load_etl_employee_head.sql

CREATE OR REPLACE PACKAGE ISZMETL.etl_employee IS
  /**
  * Project:         Test Project (<a href="http://www.starschema.net">Starschema</a>)<br/>
  * Description:     Employee load...<br/>
  * DB impact:       YES<br/>
  * Commit inside:   YES<br/>
  * Rollback inside: YES<br/>
  * @author          Foldi Tamas (Starschema Kft)
  * @headcom
  */

  v_prc_name     CONSTANT VARCHAR2( 50 ) := 'ETL_EMPLOYEE'; /* current procedure's name */
  v_prc_desc     CONSTANT VARCHAR2( 50 ) := 'Load employees from SCOTT'; /* current procedure's ddescription */
  v_src_sch      CONSTANT VARCHAR2( 30 ) := 'ISZMETL'; /* ETL schema's name */
  v_src_table    CONSTANT VARCHAR2( 30 ) := 'SRC_EMPLOYEE'; /*Source table name*/
  v_dm_sch       CONSTANT VARCHAR2( 30 ) := 'ISZM'; /* schema where the DM table is located*/
  v_dm_table     CONSTANT VARCHAR2( 30 ) := 'DIM_EMPLOYEE'; /*DM table name*/
  v_tab_load     CONSTANT VARCHAR2( 15 ) := 'UPSERT'; /* load type */
  v_src_id       CONSTANT NUMBER( 10 ) := 0; /*Source ID from sys_dta_src*/

  /** these steps are executed by the main function */
  v_load_steps   CONSTANT typ_str_arr := NEW typ_str_arr( 'step_tmp_employee', 'step_src_employee' );

  v_start_date            DATE; /* procedure start date*/
  v_batch_id              VARCHAR2( 50 ); /* identifier of given overnight batch */
  v_run_type              VARCHAR2( 50 ); /* Normal execution or Re-run */
  v_cnt                   NUMBER( 15 ); /* count of updated rows */
  v_err                   NUMBER; /*Error*/
  v_msg                   VARCHAR2( 32000 ); /*Message*/
  v_log_id                NUMBER( 10 ); /* remove me */

  /**
  * Executes the load procedure
  *
  * @param p_input_params   Procedure parameters (key=value pairs)
  * @throws others if load fails
  */
  PROCEDURE main( p_input_params VARCHAR2 );

  /**
  * Executes the load procedure
  *
  * @param p_params   Procedure parameters (omissible)
  * @throws others if load fails
  */
  PROCEDURE main( p_params param_list DEFAULT NULL );

  /**
  * Executes built-in tests
  *
  * @param p_params   Procedure parameters (omissible)
  * @throws others if load fails
  */
  PROCEDURE test( p_params param_list DEFAULT NULL );

  /**
  * Loads data from SCOTT.EMP to tmp_scott_emp
  *
  * @param p_params   Procedure parameters
  * @throws others if step fails
  */
  PROCEDURE step_tmp_employee( p_params IN OUT obj_param );
  
  /**
  * Loads data from tmp_scott_emp to SRC_EMPLOYEE
  *
  * @param p_params   Procedure parameters
  * @throws others if step fails
  */
  PROCEDURE step_src_employee( p_params IN OUT obj_param );
  
END etl_employee;
/