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

God's Word for the day

Wisdom and Folly

A wooden beam firmly bonded into a building is not loosened by an earthquake. So the mind firmly resolved after due reflection will not be afraid in a crisis. A mind settled on an intelligent thought is like stucco decoration that makes a wall smooth. Fences set on a high place will not stand firm against the wind; So a timid mind with a fool's resolve Will not stand firm against any fear.

Sirach 22:16-18

Gospel teachings of Jesus

Again, the kingdom of heaven is like a net

  that was thrown into the sea and caught fish of every kind.

When it was full they drew it ashore and put the good into baskets

  but threw out the bad.

So it will be at the end of the age.

  The angels will come out and separate the evil from the righteous

And throw them into the furnace of fire, where there will be weeping and gnashing of teeth.

Mathew 13:47-50