CREATE OR REPLACE PACKAGE CMXBG_EXAMPLE
AS
PROCEDURE UPDATE_TABLE(
     IN_ROWID_TABLE_OBJECT  IN  CMXLB.CMX_ROWID      
    ,IN_USER_NAME           IN  CMXLB.CMX_USER_NAME  
    ,IN_ROWID_JOB           IN  CMXLB.CMX_ROWID      
    ,OUT_ERR_MSG            OUT VARCHAR              
    ,OUT_ERR_CODE           OUT INT                  
);
END CMXBG_EXAMPLE;
/
CREATE OR REPLACE PACKAGE BODY CMXBG_EXAMPLE
AS
PROCEDURE UPDATE_TABLE(
			IN_ROWID_TABLE_OBJECT IN CMXLB.CMX_ROWID
			,IN_USER_NAME IN CMXLB.CMX_USER_NAME
			,IN_ROWID_JOB IN CMXLB.CMX_ROWID
			,OUT_ERR_MSG OUT VARCHAR
			,OUT_ERR_CODE OUT INT ) is

   BEGIN
      DECLARE
         CUTOFF_DATE        DATE;
         RECORD_COUNT       INT;
         RUN_STATUS         INT;
         STATUS_MESSAGE     VARCHAR2 (2000);
         START_DATE         DATE   := SYSDATE;
         MRM_ROWID_TABLE    CMXLB.CMX_ROWID;
         OBJ_FUNC_TYPE      CHAR (1);
         JOB_ID             CHAR (14);
         SQL_STMT           VARCHAR2 (2000);
         TABLE_NAME         VARCHAR2(30);
         RET_CODE           INT;
         REGISTER_JOB_ERR   EXCEPTION;
      BEGIN
         SQL_STMT :=
               'ALTER SESSION SET NLS_DATE_FORMAT=''DD MON YYYY HH24:MI:SS''';

         EXECUTE IMMEDIATE SQL_STMT;
         CMXLB.DEBUG_PRINT ('START OF CUSTOM BATCH JOB...');
         OBJ_FUNC_TYPE := 'A';

         SELECT ROWID_TABLE
           INTO MRM_ROWID_TABLE
           FROM C_REPOS_TABLE_OBJECT
          WHERE ROWID_TABLE_OBJECT = IN_ROWID_TABLE_OBJECT;

         SELECT START_RUN_DATE
           INTO CUTOFF_DATE
           FROM C_REPOS_JOB_CONTROL
          WHERE ROWID_JOB = IN_ROWID_JOB;
         IF CUTOFF_DATE IS NULL THEN
            CUTOFF_DATE := SYSDATE - 7;
         END IF;

         SELECT TABLE_NAME
           INTO TABLE_NAME
           FROM C_REPOS_TABLE RT, C_REPOS_TABLE_OBJECT RTO
         WHERE RTO.ROWID_TABLE_OBJECT = IN_ROWID_TABLE_OBJECT
           AND RTO.ROWID_TABLE = RT.ROWID_TABLE;

         -- THE REAL WORK!
         SQL_STMT :=
               'UPDATE ' || TABLE_NAME || ' SET ZIP4 = ''0000'', LAST_UPDATE_DATE = '''
            || CUTOFF_DATE
            || ''''
            || ' WHERE ZIP4 IS NULL';
         CMXLB.DEBUG_PRINT (SQL_STMT);
         EXECUTE IMMEDIATE SQL_STMT;
         RECORD_COUNT := SQL%ROWCOUNT;
         COMMIT;
         -- For testing, sleep to make the procedure take longer
         -- dbms_lock.sleep(5);
         -- Set zero or many metrics about the job
         CMXUT.SET_METRIC_VALUE (IN_ROWID_JOB, 1, RECORD_COUNT,
          OUT_ERR_CODE, OUT_ERR_MSG);
         COMMIT;
         IF RECORD_COUNT <= 0 THEN
            OUT_ERR_MSG := 'FAILED TO UPDATE RECORDS.';
            OUT_ERR_CODE := -1;
         ELSE
             IF OUT_ERR_CODE >= 0 THEN
                OUT_ERR_MSG := 'COMPLETED SUCCESSFULLY.';
             END IF;
             -- Else keep success code and msg from set_metric_value
         END IF;
      EXCEPTION
         WHEN OTHERS
         THEN
            OUT_ERR_CODE := SQLCODE;
            OUT_ERR_MSG := SUBSTR (SQLERRM, 1, 200);
      END;
   END;
END CMXBG_EXAMPLE;
/