Archiving - A generic solution - Part 4

-------------------------------------------------------- -- DDL script for HR_ARCHIVE_TRIGGERS -------------------------------------------------- 9_ddl_cre_archive_triggers.sql

--------------------------------------------------------
--  DDL for Trigger TRG_UPDT_ARCHIVE_ERROR_LOG
--------------------------------------------------------
CREATE OR REPLACE TRIGGER TRG_UPDT_ARCHIVE_ERROR_LOG 
BEFORE UPDATE
ON ARCHIVE_ERROR_LOG
FOR EACH ROW
DECLARE
BEGIN
   :new.UPDTD_BY := USER;
   :new.UPDTD_DT := SYSDATE; 
END;
/
ALTER TRIGGER TRG_UPDT_ARCHIVE_ERROR_LOG ENABLE;
--------------------------------------------------------
--  DDL for Trigger TRG_UPDT_ARCHIVE_LOG_EXCEPTIONS
--------------------------------------------------------
CREATE OR REPLACE TRIGGER TRG_UPDT_ARCHIVE_LOG_EXCEPTIONS 
BEFORE UPDATE
ON ARCHIVE_LOG_EXCEPTIONS
FOR EACH ROW
DECLARE
BEGIN
   :new.UPDTD_BY := USER;
   :new.UPDTD_DT := SYSDATE; 
END;
/
ALTER TRIGGER TRG_UPDT_ARCHIVE_LOG_EXCEPTIONS ENABLE;
--------------------------------------------------------
--  DDL for Trigger TRG_UPDT_ARCHIVE_LOG_MAIN
--------------------------------------------------------
CREATE OR REPLACE TRIGGER TRG_UPDT_ARCHIVE_LOG_MAIN 
BEFORE UPDATE
ON ARCHIVE_LOG_MAIN
FOR EACH ROW
DECLARE
BEGIN
   :new.UPDTD_BY := USER;
   :new.UPDTD_DT := SYSDATE; 
END;
/
ALTER TRIGGER TRG_UPDT_ARCHIVE_LOG_MAIN ENABLE;
--------------------------------------------------------
--  DDL for Trigger TRG_UPDT_ARCHIVE_PK_DTLS
--------------------------------------------------------
CREATE OR REPLACE TRIGGER TRG_UPDT_ARCHIVE_PK_DTLS 
BEFORE UPDATE
ON ARCHIVE_PK_DTLS
FOR EACH ROW
DECLARE
BEGIN
   :new.UPDTD_BY := USER;
   :new.UPDTD_DT := SYSDATE; 
END;
/
ALTER TRIGGER TRG_UPDT_ARCHIVE_PK_DTLS ENABLE;

-------------------------------------------------------- -- DDL for Function FXN_STR_TO_COLUMNS -------------------------------------------------- 10_ddl_cre_archive_functions.sql

--------------------------------------------------------
--  DDL for Function FXN_STR_TO_COLUMNS
--------------------------------------------------------
CREATE OR REPLACE EDITIONABLE FUNCTION FXN_STR_TO_COLUMNS (
    ------------------------------------------------------------------------------------------------
    -- Program Name        :  FXN_STR_TO_COLUMNS
    -- Description         :  Split a delimited string to an array of columns
    -------------------------------------------------------------------------------------------------
    v_in_str IN VARCHAR2,
    v_delim  IN VARCHAR2)
  RETURN TYPE_REF_STR
AS
  v_tmp_str VARCHAR2(32000);
  v_new_str VARCHAR2(32000);
  v_str     VARCHAR2(32000);
  i         NUMBER := 0;
  v_col TYPE_REF_STR;
BEGIN
  v_col := TYPE_REF_STR(NULL);
  v_str := v_in_str;
  LOOP --{
    i         := i                                   + 1;
    v_tmp_str := SUBSTR(v_str,1,INSTR(v_str,v_delim) -1 );
    v_col.EXTEND;
    v_col(i)     := NVL(v_tmp_str,v_str);
    v_new_str    := SUBSTR(v_str,INSTR(v_str,v_delim) + LENGTH(v_delim));
    v_str        := v_new_str;
    IF v_tmp_str IS NULL THEN
      EXIT;
    END IF;
  END LOOP; --}
v_col.TRIM;
RETURN v_col;
EXCEPTION
WHEN OTHERS THEN
  Raise_Application_Error(-20234, 'FXN_STR_TO_COLUMNS - Split string function error: ' || SQLERRM);
END FXN_STR_TO_COLUMNS;
/
SHOW ERRORS

-------------------------------------------------------- -- DDL for Package PKG_ARCHIVE -------------------------------------------------------- 11_ddl_cre_archive_packages.sql

create or replace PACKAGE PKG_ARCHIVE AS

/* TODO enter package declarations (types, exceptions, methods etc) here */

SUCCESS  CONSTANT NUMBER := 0;
ERROR  CONSTANT NUMBER := -1;

ARCHIVE_EXCEPTION   EXCEPTION;
COMMIT_CNT     CONSTANT INTEGER := 5000;

YES  CONSTANT VARCHAR2(1) := 'Y';
NO   CONSTANT VARCHAR2(1) := 'N';

ARCHIVE_COMPLETE  CONSTANT VARCHAR2(20) := 'COMPLETE';
ARCHIVE_PARTIAL   CONSTANT VARCHAR2(20) := 'PARTIAL';
ARCHIVE_PENDING  CONSTANT VARCHAR2(20) := 'PENDING';

MAIN_SCHEMA     CONSTANT     VARCHAR2(100) := 'HR';
ARCHIVE_SCHEMA  CONSTANT     VARCHAR2(100) := 'HR_ARCHIVE';

END PKG_ARCHIVE;
/