This post contains the scripts for the replication schema.
We first create a container database for the replication module objects. You can refer the link below.
Below scripts to be run as SYSTEM or a DBA user.
CREATE TABLESPACE REPLICA
DATAFILE 'E:\install\21cXE\oradata\XE\XEPDB2\replica.dbf'
SIZE 50485760 AUTOEXTEND ON
NEXT 50485760 MAXSIZE 309715200
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 524288
SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE INDX
DATAFILE 'E:\install\21cXE\oradata\XE\XEPDB2\INDX.dbf'
SIZE 50485760 AUTOEXTEND ON
NEXT 50485760 MAXSIZE 309715200
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 524288
SEGMENT SPACE MANAGEMENT AUTO;
--DROP USER REPLICA CASCADE;
CREATE USER REPLICA IDENTIFIED BY REPLICA;
GRANT CREATE SESSION TO REPLICA;
GRANT CONNECT TO REPLICA;
GRANT CREATE TABLE TO REPLICA;
GRANT CREATE PROCEDURE TO REPLICA;
GRANT UNLIMITED TABLESPACE TO REPLICA;
GRANT CREATE ANY TYPE TO REPLICA;
GRANT CREATE ANY SEQUENCE TO REPLICA;
ALTER USER REPLICA DEFAULT TABLESPACE REPLICA QUOTA UNLIMITED ON REPLICA;
ALTER USER REPLICA TEMPORARY TABLESPACE TEMP;
GRANT CREATE VIEW, ALTER SESSION, CREATE SEQUENCE TO REPLICA;
GRANT CREATE SYNONYM, CREATE DATABASE LINK, RESOURCE , UNLIMITED TABLESPACE TO REPLICA;
GRANT ALTER ANY TABLE TO REPLICA; -- to shrink space after table delete in that schema
--------------------------------------------------------
-- DDL for Type TAB_COL_DTLS
--------------------------------------------------------
CREATE OR REPLACE TYPE TAB_COL_DTLS AS TABLE OF VARCHAR2(100);
/
--------------------------------------------------------
-- DDL for Type TYPE_REF_STR
--------------------------------------------------------
CREATE OR REPLACE TYPE TYPE_REF_STR AS TABLE OF VARCHAR2(100);
/
--------------------------------------------------------
-- DDL for Type TYP_REC_TABLE_KEY_COLS
--------------------------------------------------------
CREATE OR REPLACE TYPE TYP_REC_TABLE_KEY_COLS
AS OBJECT
(
TABLE_NAME VARCHAR2(30),
COLUMN_1 VARCHAR2(30),
COLUMN_2 VARCHAR2(30),
COLUMN_3 VARCHAR2(30),
COLUMN_4 VARCHAR2(30),
COLUMN_5 VARCHAR2(30)
);
/
--------------------------------------------------------
-- DDL for Type TYP_REF_DATE
--------------------------------------------------------
CREATE OR REPLACE TYPE TYP_REF_DATE AS TABLE OF DATE;
/
--------------------------------------------------------
-- DDL for Type TYP_REF_NUMBER
--------------------------------------------------------
CREATE OR REPLACE TYPE TYP_REF_NUMBER AS TABLE OF NUMBER;
/
--------------------------------------------------------
-- DDL for Type TYP_REF_STR
--------------------------------------------------------
CREATE OR REPLACE TYPE TYP_REF_STR AS TABLE OF VARCHAR2(4000);
/
--------------------------------------------------------
-- DDL for Type TYP_TBL_TABLE_KEY_COLS
--------------------------------------------------------
CREATE OR REPLACE TYPE TYP_TBL_TABLE_KEY_COLS IS TABLE OF TYP_REC_TABLE_KEY_COLS;
/
--------------------------------------------------------
-- DDL for Sequence ARCHIVE_LOG_RULE_ID
--------------------------------------------------------
CREATE SEQUENCE ARCHIVE_LOG_RULE_ID MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 100001 CACHE 200 NOORDER NOCYCLE NOKEEP NOSCALE GLOBAL ;
--------------------------------------------------------
-- DDL for Sequence SEQ_ARCHIVE_BATCH_ID
--------------------------------------------------------
CREATE SEQUENCE SEQ_ARCHIVE_BATCH_ID MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 107801 CACHE 200 NOORDER NOCYCLE NOKEEP NOSCALE GLOBAL ;
--------------------------------------------------------SS
-- DDL for Sequence SEQ_ARCHIVE_LOG_DETAILS_ID
--------------------------------------------------------
CREATE SEQUENCE SEQ_ARCHIVE_LOG_DETAILS_ID MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1151601 CACHE 200 NOORDER NOCYCLE NOKEEP NOSCALE GLOBAL ;
--------------------------------------------------------
-- DDL for Sequence SEQ_ARCHIVE_LOG_EXCEPTIONS_ID
--------------------------------------------------------
CREATE SEQUENCE SEQ_ARCHIVE_LOG_EXCEPTIONS_ID MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 135801 CACHE 200 NOORDER NOCYCLE NOKEEP NOSCALE GLOBAL ;
--------------------------------------------------------
-- DDL for Sequence SEQ_ARCHIVE_LOG_HEADER_ID
--------------------------------------------------------
CREATE SEQUENCE SEQ_ARCHIVE_LOG_HEADER_ID MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 108001 CACHE 200 NOORDER NOCYCLE NOKEEP NOSCALE GLOBAL ;
--------------------------------------------------------
-- DDL for Sequence SEQ_ARCHIVE_LOG_ID
--------------------------------------------------------
CREATE SEQUENCE SEQ_ARCHIVE_LOG_ID MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 132001 CACHE 1000 NOORDER CYCLE NOKEEP NOSCALE GLOBAL ;
--------------------------------------------------------
-- DDL for Sequence SEQ_TEMP_ARCHIVE_ERROR_ID
--------------------------------------------------------
CREATE SEQUENCE SEQ_TEMP_ARCHIVE_ERROR_ID MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 100001 CACHE 200 NOORDER NOCYCLE NOKEEP NOSCALE GLOBAL ;
--------------------------------------------------------
-- DDL for Table ARCHIVE_CNTRL
--------------------------------------------------------
CREATE TABLE ARCHIVE_CNTRL
( ARCHIVE_BTN VARCHAR2(10)
) TABLESPACE REPLICA;
--------------------------------------------------------
-- DDL for Table ARCHIVE_ERROR_LOG
--------------------------------------------------------
CREATE TABLE ARCHIVE_ERROR_LOG
( NUM_ARCHIVE_LOG_ID NUMBER,
TXT_PROGRAM_NAME VARCHAR2(100),
TXT_TABLE_NAME VARCHAR2(30),
TXT_REMARKS VARCHAR2(4000),
TXT_SQLCODE VARCHAR2(20),
TXT_SQLERRM VARCHAR2(4000),
DAT_ARCHIVE_DATE DATE,
CRTD_BY VARCHAR2(100) DEFAULT USER,
CRTD_DT DATE DEFAULT SYSDATE,
UPDTD_BY VARCHAR2(100),
UPDTD_DT DATE
) TABLESPACE REPLICA;
--------------------------------------------------------
-- DDL for Table ARCHIVE_LOG_EXCEPTIONS
--------------------------------------------------------
CREATE TABLE ARCHIVE_LOG_EXCEPTIONS
( NUM_ARCHIVE_LOG_EXCEPTIONS_ID NUMBER(20,0),
NUM_ARCHIVE_LOG_HEADER_ID NUMBER(20,0),
TXT_TABLE_NM VARCHAR2(100),
NUM_EXCEPTION_ERR_CODE NUMBER,
TXT_EXCEPTION_ERR_DESC VARCHAR2(4000),
TXT_REMARKS VARCHAR2(500),
CRTD_BY VARCHAR2(100) DEFAULT USER,
CRTD_DT DATE DEFAULT SYSDATE,
UPDTD_BY VARCHAR2(100),
UPDTD_DT DATE
) TABLESPACE REPLICA;
--------------------------------------------------------
-- DDL for Table ARCHIVE_LOG_HEADER
--------------------------------------------------------
CREATE TABLE ARCHIVE_LOG_HEADER
( NUM_ARCHIVE_LOG_HEADER_ID NUMBER(20,0),
NUM_ARCHIVE_BATCH_ID NUMBER(20,0),
TXT_RULE_CD VARCHAR2(20),
TXT_TABLE_NAME VARCHAR2(100),
TXT_DELETE_TABLE VARCHAR2(100),
TXT_TBL_OWNER VARCHAR2(100),
TXT_TBL_ARCHIVAL_STATUS VARCHAR2(20),
NUM_RECORDS_TO_ARCHIVE NUMBER(10,0),
NUM_RECORDS_ARCHIVED NUMBER(10,0),
NUM_RECORDS_FAILED_ARCHIVAL NUMBER(10,0),
NUM_RECORDS_PENDING_ARCHIVAL NUMBER(10,0),
CRTD_BY VARCHAR2(100) DEFAULT 'SYSTEM',
CRTD_DT DATE DEFAULT SYSDATE,
UPDTD_BY VARCHAR2(100) DEFAULT 'SYSTEM',
UPDTD_DT DATE DEFAULT SYSDATE
) TABLESPACE REPLICA;
--------------------------------------------------------
-- DDL for Table ARCHIVE_LOG_MAIN
--------------------------------------------------------
CREATE TABLE ARCHIVE_LOG_MAIN
( NUM_ARCHIVE_BATCH_ID NUMBER(20,0),
DAT_ARCHIVE_DATE DATE,
TXT_ARCHIVAL_STATUS VARCHAR2(20),
CRTD_BY VARCHAR2(100) DEFAULT 'SYSTEM',
CRTD_DT DATE DEFAULT SYSDATE,
UPDTD_BY VARCHAR2(100) DEFAULT 'SYSTEM',
UPDTD_DT DATE DEFAULT SYSDATE
) TABLESPACE REPLICA;
--------------------------------------------------------
-- DDL for Table ARCHIVE_PK_DTLS
--------------------------------------------------------
CREATE TABLE ARCHIVE_PK_DTLS
( NUM_ARCHIVE_BATCH_ID NUMBER(20,0),
NUM_ARCHIVE_LOG_HEADER_ID NUMBER(20,0),
TXT_TBL_NM VARCHAR2(50),
TXT_PK_COL_NMS VARCHAR2(500),
TXT_PK_COL_VALS VARCHAR2(1000),
TXT_PK_COL_DTYPS VARCHAR2(1000),
TXT_ARCH_STAT VARCHAR2(20),
CRTD_BY VARCHAR2(100) DEFAULT 'SYSTEM',
CRTD_DT DATE DEFAULT SYSDATE,
UPDTD_BY VARCHAR2(100) DEFAULT 'SYSTEM',
UPDTD_DT DATE DEFAULT SYSDATE
) TABLESPACE REPLICA;
--------------------------------------------------------
-- DDL for Table ARCHIVE_RULES
--------------------------------------------------------
CREATE TABLE ARCHIVE_RULES
( NUM_RULE_ID NUMBER,
TXT_RULE_DESC VARCHAR2(1000),
TXT_RULE_QUERY VARCHAR2(4000),
TXT_ACTIVE_FLG VARCHAR2(1) DEFAULT 'Y',
TXT_ARCHIVE_TABLE VARCHAR2(100),
TXT_DELETE_TABLE VARCHAR2(1000),
TXT_TBL_OWNER VARCHAR2(100),
CRTD_BY VARCHAR2(100) DEFAULT 'SYSTEM',
CRTD_DT DATE DEFAULT SYSDATE,
UPDTD_BY VARCHAR2(100) DEFAULT 'SYSTEM',
UPDTD_DT DATE DEFAULT SYSDATE
) TABLESPACE REPLICA;
--------------------------------------------------------
-- DDL for Table CURR_TRDE_DT
--------------------------------------------------------
CREATE TABLE CURR_TRDE_DT
( TRDE_DT DATE
) TABLESPACE REPLICA;
--------------------------------------------------------
-- DDL for Table REPL_DTLS
--------------------------------------------------------
CREATE TABLE REPL_DTLS
( NUM_ARCHIVE_BATCH_ID NUMBER(20,0),
NUM_ARCHIVE_LOG_HEADER_ID NUMBER(20,0),
TXT_TBL_NM VARCHAR2(50),
TXT_PK_COL_NMS VARCHAR2(500),
TXT_PK_COL_VALS VARCHAR2(1000),
TXT_PK_COL_DTYPS VARCHAR2(1000),
TXT_DELETE_TABLE VARCHAR2(100),
TXT_DEL_TABLE_ID VARCHAR2(100),
TXT_REPL_STAT VARCHAR2(20),
CRTD_BY VARCHAR2(100) DEFAULT 'SYSTEM',
CRTD_DT DATE DEFAULT SYSDATE,
UPDTD_BY VARCHAR2(100) DEFAULT 'SYSTEM',
UPDTD_DT DATE DEFAULT SYSDATE
) TABLESPACE REPLICA;
--------------------------------------------------------
-- DDL for Table CUSTOMER
--------------------------------------------------------
CREATE TABLE CUSTOMER
( CUST_ID NUMBER,
CUST_NAME VARCHAR2(100),
PHONE_NUMBER VARCHAR2(20),
EMAIL VARCHAR2(30),
ADDR_LINE_1 VARCHAR2(500),
ADDR_LINE_2 VARCHAR2(500),
ADDR_LINE_3 VARCHAR2(500),
COUNTRY_ID CHAR(2),
REGION_ID NUMBER,
GENDER VARCHAR2(20),
ARCHIVE_LOG_HEADER_ID NUMBER(20,0),
ARCHIVE_DATE DATE
) TABLESPACE REPLICA;
--------------------------Below is list of tables to be replicated------------------------------
--------------------------------------------------------
-- DDL for Table DAILY_TRANSACTIONS
--------------------------------------------------------
CREATE TABLE DAILY_TRANSACTIONS
( TRANS_ID NUMBER,
TRANS_DATE DATE,
CUST_ID NUMBER,
TRANS_AMT NUMBER,
TRANS_CRNCY VARCHAR2(3),
TRANS_IND VARCHAR2(10),
ACCT_NO VARCHAR2(50),
BNK_NAME VARCHAR2(100),
BNK_LOCATION VARCHAR2(100),
BANK_IFSC VARCHAR2(100),
ARCHIVE_LOG_HEADER_ID NUMBER(20,0),
ARCHIVE_DATE DATE
) TABLESPACE REPLICA;
--------------------------------------------------------
-- DDL for Table EMPLOYEES
--------------------------------------------------------
CREATE TABLE EMPLOYEES
( EMPLOYEE_ID NUMBER(6,0),
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(25),
EMAIL VARCHAR2(25),
PHONE_NUMBER VARCHAR2(20),
HIRE_DATE DATE,
JOB_ID VARCHAR2(10),
SALARY NUMBER(8,2),
COMMISSION_PCT NUMBER(2,2),
MANAGER_ID NUMBER(6,0),
DEPARTMENT_ID NUMBER(4,0),
ARCHIVE_LOG_HEADER_ID NUMBER(20,0),
ARCHIVE_DATE DATE
)
TABLESPACE REPLICA;
--------------------------------------------------------
-- DDL for Table JOB_HISTORY
--------------------------------------------------------
CREATE TABLE JOB_HISTORY
( EMPLOYEE_ID NUMBER(6,0),
START_DATE DATE,
END_DATE DATE,
JOB_ID VARCHAR2(10),
DEPARTMENT_ID NUMBER(4,0),
ARCHIVE_DATE DATE,
ARCHIVE_LOG_HEADER_ID NUMBER(20,0)
) TABLESPACE REPLICA;
--------------------------------------------------------
-- DDL for Index IDX_ARCHIVE_ERROR_LOG
--------------------------------------------------------
CREATE INDEX IDX_ARCHIVE_ERROR_LOG ON ARCHIVE_ERROR_LOG (TXT_TABLE_NAME, DAT_ARCHIVE_DATE)
TABLESPACE INDX ;
--------------------------------------------------------
-- DDL for Index PK_ARCHIVE_BATCH_ID
--------------------------------------------------------
CREATE UNIQUE INDEX PK_ARCHIVE_BATCH_ID ON ARCHIVE_LOG_MAIN (NUM_ARCHIVE_BATCH_ID)
TABLESPACE INDX ;
--------------------------------------------------------
-- DDL for Index PK_ARCHIVE_LOG_HEADER_ID
--------------------------------------------------------
CREATE UNIQUE INDEX PK_ARCHIVE_LOG_HEADER_ID ON ARCHIVE_LOG_HEADER (NUM_ARCHIVE_LOG_HEADER_ID)
TABLESPACE INDX ;
--------------------------------------------------------
-- DDL for Index PK_ARCHIVE_LOG_ID
--------------------------------------------------------
CREATE UNIQUE INDEX PK_ARCHIVE_LOG_ID ON ARCHIVE_ERROR_LOG (NUM_ARCHIVE_LOG_ID)
TABLESPACE INDX ;
--------------------------------------------------------
-- DDL for Index PK_EXCEPTION_DETAILS_ID
--------------------------------------------------------
CREATE UNIQUE INDEX PK_EXCEPTION_DETAILS_ID ON ARCHIVE_LOG_EXCEPTIONS (NUM_ARCHIVE_LOG_EXCEPTIONS_ID)
TABLESPACE INDX ;
--------------------------------------------------------
-- DDL for Index PK_RULE_ID
--------------------------------------------------------
CREATE UNIQUE INDEX PK_RULE_ID ON ARCHIVE_RULES (NUM_RULE_ID, TXT_ARCHIVE_TABLE)
TABLESPACE INDX ;
--------------------------------------------------------
-- DDL for Index EMP_EMP_ID_PK
--------------------------------------------------------
CREATE UNIQUE INDEX EMP_EMP_ID_PK ON EMPLOYEES (EMPLOYEE_ID)
TABLESPACE INDX ;
--------------------------------------------------------
-- DDL for Index JHIST_EMP_ID_ST_DATE_PK
--------------------------------------------------------
CREATE UNIQUE INDEX JHIST_EMP_ID_ST_DATE_PK ON JOB_HISTORY (EMPLOYEE_ID, START_DATE)
TABLESPACE INDX ;
--------------------------------------------------------
-- Constraints for Table ARCHIVE_ERROR_LOG
--------------------------------------------------------
ALTER TABLE REPLICA.ARCHIVE_ERROR_LOG ADD CONSTRAINT PK_ARCHIVE_LOG_ID PRIMARY KEY (NUM_ARCHIVE_LOG_ID)
USING INDEX REPLICA.PK_ARCHIVE_LOG_ID ENABLE;
--------------------------------------------------------
-- Constraints for Table ARCHIVE_LOG_EXCEPTIONS
--------------------------------------------------------
ALTER TABLE REPLICA.ARCHIVE_LOG_EXCEPTIONS ADD CONSTRAINT PK_EXCEPTION_DETAILS_ID PRIMARY KEY (NUM_ARCHIVE_LOG_EXCEPTIONS_ID)
USING INDEX REPLICA.PK_EXCEPTION_DETAILS_ID ENABLE;
--------------------------------------------------------
-- Constraints for Table ARCHIVE_LOG_MAIN
--------------------------------------------------------
ALTER TABLE REPLICA.ARCHIVE_LOG_MAIN ADD CONSTRAINT PK_ARCHIVE_BATCH_ID PRIMARY KEY (NUM_ARCHIVE_BATCH_ID)
USING INDEX REPLICA.PK_ARCHIVE_BATCH_ID ENABLE;
-------------------Constraints on replication table objects---------------------------
--------------------------------------------------------
-- Constraints for Table EMPLOYEES
--------------------------------------------------------
ALTER TABLE REPLICA.EMPLOYEES MODIFY (LAST_NAME CONSTRAINT EMP_LAST_NAME_NN NOT NULL ENABLE);
ALTER TABLE REPLICA.EMPLOYEES MODIFY (EMAIL CONSTRAINT EMP_EMAIL_NN NOT NULL ENABLE);
ALTER TABLE REPLICA.EMPLOYEES MODIFY (HIRE_DATE CONSTRAINT EMP_HIRE_DATE_NN NOT NULL ENABLE);
ALTER TABLE REPLICA.EMPLOYEES MODIFY (JOB_ID CONSTRAINT EMP_JOB_NN NOT NULL ENABLE);
ALTER TABLE REPLICA.EMPLOYEES ADD CONSTRAINT EMP_EMP_ID_PK PRIMARY KEY (EMPLOYEE_ID)
USING INDEX REPLICA.EMP_EMP_ID_PK ENABLE;
--------------------------------------------------------
-- Constraints for Table JOB_HISTORY
--------------------------------------------------------
ALTER TABLE REPLICA.JOB_HISTORY MODIFY (EMPLOYEE_ID CONSTRAINT JHIST_EMPLOYEE_NN NOT NULL ENABLE);
ALTER TABLE REPLICA.JOB_HISTORY MODIFY (START_DATE CONSTRAINT JHIST_START_DATE_NN NOT NULL ENABLE);
ALTER TABLE REPLICA.JOB_HISTORY MODIFY (END_DATE CONSTRAINT JHIST_END_DATE_NN NOT NULL ENABLE);
ALTER TABLE REPLICA.JOB_HISTORY MODIFY (JOB_ID CONSTRAINT JHIST_JOB_NN NOT NULL ENABLE);
ALTER TABLE REPLICA.JOB_HISTORY ADD CONSTRAINT JHIST_EMP_ID_ST_DATE_PK PRIMARY KEY (EMPLOYEE_ID, START_DATE)
USING INDEX REPLICA.JHIST_EMP_ID_ST_DATE_PK ENABLE;
--------------------------------------------------------
-- 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
--------------------------------------------------------
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;
/
--------------------------------------------------------
-- DDL for Package PKG_ARCHIVE
--------------------------------------------------------
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 := 500;
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';
BATCH_FAILED CONSTANT VARCHAR2(20) := 'FAILED';
BATCH_SUCCESS CONSTANT VARCHAR2(20) := 'SUCCESS';
REPL_PENDING CONSTANT VARCHAR2(10) := 'PENDING';
ARCHIVE_SCHEMA CONSTANT VARCHAR2(100) := 'HR_ARCHIVE';
RUN_MODE CONSTANT VARCHAR2(10) := 'REPLICATE'; -- REPLICATE --ARCHIVE
END PKG_ARCHIVE;
/
ALTER SESSION SET CONTAINER=XEPDB2;
GRANT EXECUTE ON DBMS_LOCK TO REPLICA;
--------------------------------------------------------
-- DDL for Procedure PR_ARCHIVE_ERROR_LOG
--------------------------------------------------------
set define off;
CREATE OR REPLACE PROCEDURE PR_ARCHIVE_ERROR_LOG (
v_program_name IN VARCHAR2,
v_table_name IN VARCHAR2,
v_remarks IN VARCHAR2,
v_sqlcode IN VARCHAR2,
v_sqlerrm IN VARCHAR2,
v_log_date IN DATE,
v_log_user IN VARCHAR2,
v_error_code OUT NUMBER )
AS
------------------------------------------------------------------------------------------------
-- Program Name : PR_ARCHIVE_ERROR_LOG
-- Description : Insert into error log
-------------------------------------------------------------------------------------------------
PRAGMA AUTONOMOUS_TRANSACTION;
v_err_desc VARCHAR2(500);
BEGIN
INSERT
INTO ARCHIVE_ERROR_LOG
(
NUM_ARCHIVE_LOG_ID,
TXT_PROGRAM_NAME,
TXT_TABLE_NAME,
TXT_REMARKS,
TXT_SQLCODE,
TXT_SQLERRM,
DAT_ARCHIVE_DATE
)
VALUES
(
SEQ_ARCHIVE_LOG_ID.nextval,
v_program_name,
v_table_name,
v_remarks,
v_sqlcode,
v_sqlerrm,
v_log_date
);
v_error_code := PKG_ARCHIVE.ERROR;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
v_err_desc := 'ERROR IN ' || v_program_name || ' at : ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
v_error_code := SQLCODE;
PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, v_error_code);
END PR_ARCHIVE_ERROR_LOG;
/
--------------------------------------------------------
-- DDL for Procedure PR_INSERT_ARCHIVE_LOG_MAIN
--------------------------------------------------------
set define off;
CREATE OR REPLACE PROCEDURE PR_INSERT_ARCHIVE_LOG_MAIN (
------------------------------------------------------------------------------------------------
-- Program Name : PR_INSERT_ARCHIVE_LOG_MAIN
-- Description : Data Archiving Main
-------------------------------------------------------------------------------------------------
p_archive_batch_id OUT NUMBER,
p_error_code OUT NUMBER )
AS
v_program_name VARCHAR2(100) := 'PR_INSERT_ARCHIVE_LOG_MAIN';
v_table_name VARCHAR2(100) := NULL;
v_err_desc VARCHAR2(500);
BEGIN
p_error_code := PKG_ARCHIVE.SUCCESS;
--Procedure will insert entry into ARCHIVE_LOG_MAIN table and pass archive_batch_id as the output
SELECT SEQ_ARCHIVE_BATCH_ID.NEXTVAL
INTO p_archive_batch_id
FROM DUAL;
INSERT
INTO ARCHIVE_LOG_MAIN
(
NUM_ARCHIVE_BATCH_ID,
DAT_ARCHIVE_DATE,
TXT_ARCHIVAL_STATUS
)
VALUES
(
p_archive_batch_id,
SYSDATE,
PKG_ARCHIVE.ARCHIVE_PENDING
);
dbms_output.put_line('PR_INSERT_ARCHIVE_LOG_MAIN p_archive_batch_id ' || p_archive_batch_id);
RETURN ;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
v_err_desc := 'ERROR IN ' || v_program_name || ' at : ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
p_error_code := SQLCODE;
PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, p_error_code);
END PR_INSERT_ARCHIVE_LOG_MAIN;
/
--------------------------------------------------------
-- DDL for Procedure PR_ARCHIVE_LOG_EXCEPTIONS
--------------------------------------------------------
set define off;
CREATE OR REPLACE PROCEDURE PR_ARCHIVE_LOG_EXCEPTIONS (
------------------------------------------------------------------------------------------------
-- Program Name : PR_ARCHIVE_LOG_EXCEPTIONS
-- Description : Insert Exception Data into Exception table For insert failed records
-------------------------------------------------------------------------------------------------
p_archive_log_header_id IN VARCHAR2,
p_tbl_nm IN VARCHAR2,
p_remarks IN VARCHAR2,
p_sqlcode IN VARCHAR2,
p_sqlerrm IN VARCHAR2 )
AS
v_program_name VARCHAR2(100) := 'PR_ARCHIVE_LOG_EXCEPTIONS';
v_table_name VARCHAR2(100) := NULL;
v_err_desc VARCHAR2(500);
v_error_code NUMBER;
v_key_column_arr TYPE_REF_STR;
v_arr_cntr NUMBER := 0;
i_archive_log_exceptions_id NUMBER;
BEGIN
SELECT SEQ_ARCHIVE_LOG_EXCEPTIONS_ID.NEXTVAL
INTO i_archive_log_exceptions_id
FROM DUAL;
INSERT
INTO ARCHIVE_LOG_EXCEPTIONS
(
NUM_ARCHIVE_LOG_EXCEPTIONS_ID,
NUM_ARCHIVE_LOG_HEADER_ID,
TXT_TABLE_NM,
NUM_EXCEPTION_ERR_CODE,
TXT_EXCEPTION_ERR_DESC,
TXT_REMARKS
)
VALUES
(
SEQ_ARCHIVE_LOG_EXCEPTIONS_ID.nextval,
p_archive_log_header_id,
p_tbl_nm,
p_sqlcode,
p_sqlerrm,
p_remarks
);
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
v_err_desc := 'ERROR IN ' || v_program_name || ' at : ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
v_error_code := SQLCODE;
PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, v_error_code);
END PR_ARCHIVE_LOG_EXCEPTIONS;
/
--------------------------------------------------------
-- DDL for Procedure PR_ARCHIVE_TABLE_INSRT
--------------------------------------------------------
set define off;
CREATE OR REPLACE PROCEDURE PR_ARCHIVE_TABLE_INSRT (
p_archive_log_header_id NUMBER,
p_table_name VARCHAR2,
p_table_owner VARCHAR2,
p_error_code IN OUT NUMBER)
AS
------------------------------------------------------------------------------------------------
-- Program Name : PR_ARCHIVE_TABLE_INSRT
-- Description : Insert data into archive table
-------------------------------------------------------------------------------------------------
TYPE cur_typ
IS
REF
CURSOR;
CUR cur_typ;
v_program_name VARCHAR2(100) := 'PR_ARCHIVE_TABLE_INSRT';
v_table_name VARCHAR2(100) := p_table_name;
v_owner VARCHAR2(100) := p_table_owner;
v_arch_owner VARCHAR2(100) := PKG_ARCHIVE.ARCHIVE_SCHEMA;
v_err_desc VARCHAR2(500);
v_rowid_arr TYP_REF_STR := TYP_REF_STR();
v_sql VARCHAR2(4000);
v_tbl_archive_status VARCHAR2(20) := pkg_archive.archive_complete;
tab_col_nm TYPE_REF_STR;
tab_col_val TYPE_REF_STR;
tab_col_dtyp TYPE_REF_STR;
i INTEGER := 0;
j INTEGER := 0;
v_sql_insrt VARCHAR2(32000);
v_sql_delet VARCHAR2(32000);
v_sql_clmn VARCHAR2(32000);
v_sql_clmn_sel VARCHAR2(32000);
v_archive_log_hdr_id CONSTANT VARCHAR2(100) := 'ARCHIVE_LOG_HEADER_ID';
v_archive_date CONSTANT VARCHAR2(100) := 'ARCHIVE_DATE';
i_commit_cnt NUMBER := 0;
CURSOR C1
IS
SELECT TXT_TBL_NM,
TXT_PK_COL_NMS,
TXT_PK_COL_VALS,
TXT_PK_COL_DTYPS
FROM ARCHIVE_PK_DTLS
WHERE TXT_TBL_NM = p_table_name
AND TXT_ARCH_STAT = PKG_ARCHIVE.ARCHIVE_PENDING;
CURSOR C2
IS
SELECT COLUMN_NAME,
DATA_TYPE
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = p_table_name
AND OWNER = v_arch_owner
AND COLUMN_NAME NOT IN ('ARCHIVE_LOG_HEADER_ID','ARCHIVE_DATE');
BEGIN
p_error_code := PKG_ARCHIVE.SUCCESS;
i := 0;
FOR rec IN C1
LOOP --{
i := i+1;
j := j+1;
v_sql := 'SELECT rowid FROM ';
v_sql := v_sql || v_owner || '.' || rec.TXT_TBL_NM || ' WHERE 1=1 ';
tab_col_nm := fxn_str_to_columns(rec.TXT_PK_COL_NMS,',');
tab_col_val := fxn_str_to_columns(rec.TXT_PK_COL_VALS,',');
tab_col_dtyp := fxn_str_to_columns(rec.TXT_PK_COL_DTYPS,',');
FOR i IN 1..tab_col_nm.COUNT
LOOP --{
IF tab_col_dtyp(i) = 'NUMBER' THEN
v_sql := v_sql || ' AND ' || tab_col_nm(i) || ' = ' || tab_col_val(i);
ELSIF tab_col_dtyp(i) = 'VARCHAR2' THEN
v_sql := v_sql || ' AND ' || tab_col_nm(i) || ' = ''' || tab_col_val(i) || '''';
ELSIF tab_col_dtyp(i) = 'DATE' THEN
-- tab_col_val(i) := TRUNC(tab_col_val(i));
v_sql := v_sql || ' AND ' || tab_col_nm(i) || ' = TO_DATE(''' || tab_col_val(i) || ''',''DD-MON-RRRR HH24:MI:SS'') ';
ELSE
v_sql := v_sql || ' AND ' || tab_col_nm(i) || ' = ''' || tab_col_val(i) || '''';
END IF;
END LOOP;
--USE 4TH ORDER DYNAMIC QUERY
v_rowid_arr.EXTEND;
dbms_output.put_line('v_sql ' || v_sql);
EXECUTE IMMEDIATE v_sql INTO v_rowid_arr(j);-- USING p_table_name, tab_col_nm(1), tab_col_val(1) ;
END LOOP; --}
FOR rec IN C2
LOOP --{
v_sql_clmn := v_sql_clmn || rec.COLUMN_NAME || ',';
END LOOP;
v_sql_insrt := 'INSERT INTO ' || v_arch_owner || '.' || p_table_name || '(';
v_sql_insrt := v_sql_insrt || v_sql_clmn || v_archive_log_hdr_id || ',' || v_archive_date || ')';
v_sql_insrt := v_sql_insrt || 'SELECT ' || v_sql_clmn || ' :p_archive_log_header_id,:SYSDT';
v_sql_insrt := v_sql_insrt || ' FROM ' || v_owner || '.' || p_table_name;
v_sql_insrt := v_sql_insrt || ' WHERE rowid = :p_rowid';
v_sql_delet := 'DELETE FROM ' || v_owner || '.' || p_table_name;
v_sql_delet := v_sql_delet || ' WHERE rowid = :p_rowid';
dbms_output.put_line('v_sql_insrt ' || v_sql_insrt);
dbms_output.put_line('v_sql_delet ' || v_sql_delet);
FOR i IN 1 .. v_rowid_arr.COUNT
LOOP --{
i_commit_cnt := i_commit_cnt + 1;
BEGIN
EXECUTE IMMEDIATE v_sql_insrt USING p_archive_log_header_id, SYSDATE, v_rowid_arr(i);
EXCEPTION
WHEN OTHERS
THEN
v_err_desc := 'Error inserting archive record for ' || p_table_name;
PR_ARCHIVE_LOG_EXCEPTIONS(p_archive_log_header_id,p_table_name,v_err_desc,SQLCODE, SQLERRM);
CONTINUE;
END;
EXECUTE IMMEDIATE v_sql_delet USING v_rowid_arr(i); --exception to be handled for delete. Here if delete fails, insert into archvive table should also be deleted.
IF i_commit_cnt = PKG_ARCHIVE.COMMIT_CNT
THEN
COMMIT;
i_commit_cnt := 0;
END IF;
END LOOP; --}
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
v_err_desc := 'ERROR IN ' || v_program_name || ' at : ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
p_error_code := SQLCODE;
PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, p_error_code);
END;
/
--------------------------------------------------------
-- DDL for Procedure PR_ARCHIVE_TABLE_DATA
--------------------------------------------------------
set define off;
CREATE OR REPLACE PROCEDURE PR_ARCHIVE_TABLE_DATA (
------------------------------------------------------------------------------------------------
-- Program Name : PR_ARCHIVE_TABLE_DATA
-- Description : Archive data for the required table
-------------------------------------------------------------------------------------------------
p_archive_batch_id NUMBER,
p_archive_log_header_id NUMBER,
p_table_name VARCHAR2,
p_table_owner VARCHAR2,
p_error_code IN OUT NUMBER )
AS
v_program_name VARCHAR2(100) := 'PR_ARCHIVE_TABLE_DATA';
v_table_name VARCHAR2(100) := NULL;
ARCHIVE_EXCEPTION EXCEPTION;
v_err_desc VARCHAR2(500);
BEGIN
p_error_code := PKG_ARCHIVE.SUCCESS;
PR_ARCHIVE_TABLE_INSRT(p_archive_log_header_id, p_table_name,p_table_owner, p_error_code); -- TO CHANGE NAME
IF p_error_code = PKG_ARCHIVE.SUCCESS THEN
UPDATE ARCHIVE_PK_DTLS
SET TXT_ARCH_STAT = PKG_ARCHIVE.ARCHIVE_COMPLETE
WHERE NUM_ARCHIVE_BATCH_ID = p_archive_batch_id
AND NUM_ARCHIVE_LOG_HEADER_ID = p_archive_log_header_id
AND TXT_TBL_NM = p_table_name;
ELSE
v_err_desc := 'ERROR IN PR_ARCHIVE_TABLE_INSRT. Error code : '|| p_error_code;
RAISE ARCHIVE_EXCEPTION;
END IF;
EXCEPTION
WHEN ARCHIVE_EXCEPTION
THEN
ROLLBACK;
PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, p_error_code);
WHEN OTHERS THEN
ROLLBACK;
v_err_desc := 'ERROR IN ' || v_program_name || ' at : ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
p_error_code := SQLCODE;
PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, p_error_code);
END PR_ARCHIVE_TABLE_DATA;
/
--------------------------------------------------------
-- DDL for Procedure PR_REPL_TABLE_INSRT
--------------------------------------------------------
set define off;
CREATE OR REPLACE PROCEDURE PR_REPL_TABLE_INSRT (
p_archive_log_header_id NUMBER,
p_table_name VARCHAR2,
p_del_tbl VARCHAR2,
p_table_owner VARCHAR2,
p_error_code IN OUT NUMBER)
AS
------------------------------------------------------------------------------------------------
-- Program Name : PR_ARCHIVE_TABLE_INSRT
-- Description : Insert data into archive table
-------------------------------------------------------------------------------------------------
TYPE cur_typ
IS
REF
CURSOR;
CUR cur_typ;
v_program_name VARCHAR2(100) := 'PR_REPL_TABLE_INSRT';
v_table_name VARCHAR2(100) := p_table_name;
v_owner VARCHAR2(100) := p_table_owner;
v_arch_owner VARCHAR2(100) := PKG_ARCHIVE.ARCHIVE_SCHEMA;
v_err_desc VARCHAR2(500);
v_rowid_arr TYP_REF_STR := TYP_REF_STR();
v_repl_rowid_arr TYP_REF_STR := TYP_REF_STR();
v_sql VARCHAR2(4000);
v_sql_repl_del VARCHAR2(4000);
v_repl_del VARCHAR2(4000);
v_tbl_archive_status VARCHAR2(20) := pkg_archive.archive_complete;
tab_col_nm TYPE_REF_STR;
tab_col_val TYPE_REF_STR;
tab_col_dtyp TYPE_REF_STR;
i INTEGER := 0;
j INTEGER := 0;
v_sql_insrt VARCHAR2(32000);
v_sql_delet VARCHAR2(32000);
v_sql_clmn VARCHAR2(32000);
v_sql_clmn_sel VARCHAR2(32000);
v_archive_log_hdr_id CONSTANT VARCHAR2(100) := 'ARCHIVE_LOG_HEADER_ID';
v_archive_date CONSTANT VARCHAR2(100) := 'ARCHIVE_DATE';
i_commit_cnt NUMBER := 0;
CURSOR C1
IS
SELECT TXT_TBL_NM,
TXT_PK_COL_NMS,
TXT_PK_COL_VALS,
TXT_PK_COL_DTYPS
FROM REPL_DTLS
WHERE TXT_TBL_NM = p_table_name
AND TXT_REPL_STAT = PKG_ARCHIVE.ARCHIVE_PENDING;
CURSOR C2
IS
SELECT COLUMN_NAME,
DATA_TYPE
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = p_table_name
AND OWNER = v_arch_owner
AND COLUMN_NAME NOT IN ('ARCHIVE_LOG_HEADER_ID','ARCHIVE_DATE');
BEGIN
p_error_code := PKG_ARCHIVE.SUCCESS;
i := 0;
FOR rec IN C1
LOOP --{
i := i+1;
j := j+1;
v_sql := 'SELECT rowid FROM ';
v_sql := v_sql || v_owner || '.' || rec.TXT_TBL_NM || ' WHERE 1=1 ';
tab_col_nm := fxn_str_to_columns(rec.TXT_PK_COL_NMS,',');
tab_col_val := fxn_str_to_columns(rec.TXT_PK_COL_VALS,',');
tab_col_dtyp := fxn_str_to_columns(rec.TXT_PK_COL_DTYPS,',');
FOR i IN 1..tab_col_nm.COUNT
LOOP --{
IF tab_col_dtyp(i) = 'NUMBER' THEN
v_sql := v_sql || ' AND ' || tab_col_nm(i) || ' = ' || tab_col_val(i);
ELSIF tab_col_dtyp(i) = 'VARCHAR2' THEN
v_sql := v_sql || ' AND ' || tab_col_nm(i) || ' = ''' || tab_col_val(i) || '''';
ELSIF tab_col_dtyp(i) = 'DATE' THEN
-- tab_col_val(i) := TRUNC(tab_col_val(i));
v_sql := v_sql || ' AND ' || tab_col_nm(i) || ' = TO_DATE(''' || tab_col_val(i) || ''',''DD-MON-RRRR HH24:MI:SS'') ';
ELSE
v_sql := v_sql || ' AND ' || tab_col_nm(i) || ' = ''' || tab_col_val(i) || '''';
END IF;
END LOOP;
--USE 4TH ORDER DYNAMIC QUERY
v_rowid_arr.EXTEND;
dbms_output.put_line('v_sql ' || v_sql);
EXECUTE IMMEDIATE v_sql INTO v_rowid_arr(j);-- USING p_table_name, tab_col_nm(1), tab_col_val(1) ;
END LOOP; --}
i := 0;
j := 0;
FOR rec IN C1
LOOP --{
i := i+1;
j := j+1;
v_sql_repl_del := 'SELECT rowid FROM ';
v_sql_repl_del := v_sql_repl_del || v_arch_owner || '.' || rec.TXT_TBL_NM || ' WHERE 1=1 ';
tab_col_nm := fxn_str_to_columns(rec.TXT_PK_COL_NMS,',');
tab_col_val := fxn_str_to_columns(rec.TXT_PK_COL_VALS,',');
tab_col_dtyp := fxn_str_to_columns(rec.TXT_PK_COL_DTYPS,',');
FOR i IN 1..tab_col_nm.COUNT
LOOP --{
IF tab_col_dtyp(i) = 'NUMBER' THEN
v_sql_repl_del := v_sql_repl_del || ' AND ' || tab_col_nm(i) || ' = ' || tab_col_val(i);
ELSIF tab_col_dtyp(i) = 'VARCHAR2' THEN
v_sql_repl_del := v_sql_repl_del || ' AND ' || tab_col_nm(i) || ' = ''' || tab_col_val(i) || '''';
ELSIF tab_col_dtyp(i) = 'DATE' THEN
-- tab_col_val(i) := TRUNC(tab_col_val(i));
v_sql_repl_del := v_sql_repl_del || ' AND ' || tab_col_nm(i) || ' = TO_DATE(''' || tab_col_val(i) || ''',''DD-MON-RRRR HH24:MI:SS'') ';
ELSE
v_sql_repl_del := v_sql_repl_del || ' AND ' || tab_col_nm(i) || ' = ''' || tab_col_val(i) || '''';
END IF;
END LOOP;
--USE 4TH ORDER DYNAMIC QUERY
v_repl_rowid_arr.EXTEND;
-- dbms_output.put_line('v_sql_repl_del ' || v_sql_repl_del);
BEGIN
EXECUTE IMMEDIATE v_sql_repl_del INTO v_repl_rowid_arr(j);-- USING p_table_name, tab_col_nm(1), tab_col_val(1) ;
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL;
WHEN OTHERS THEN NULL;
END;
END LOOP;
FOR rec IN C2
LOOP --{
v_sql_clmn := v_sql_clmn || rec.COLUMN_NAME || ',';
END LOOP;
v_sql_insrt := 'INSERT INTO ' || v_arch_owner || '.' || p_table_name || '(';
v_sql_insrt := v_sql_insrt || v_sql_clmn || v_archive_log_hdr_id || ',' || v_archive_date || ')';
v_sql_insrt := v_sql_insrt || 'SELECT ' || v_sql_clmn || ' :p_archive_log_header_id,:SYSDT';
v_sql_insrt := v_sql_insrt || ' FROM ' || v_owner || '.' || p_table_name;
v_sql_insrt := v_sql_insrt || ' WHERE rowid = :p_rowid';
v_repl_del := 'DELETE FROM ' || v_arch_owner || '.' || p_table_name;
v_repl_del := v_repl_del || ' WHERE rowid = :p_rowid';
v_sql_delet := 'DELETE FROM ' || v_owner || '.' || p_del_tbl;
v_sql_delet := v_sql_delet || ' WHERE UNIQUE_ID = :p_rowid';
dbms_output.put_line('v_sql_insrt ' || v_sql_insrt);
dbms_output.put_line('v_sql_delet ' || v_sql_delet);
FOR i IN 1 .. v_rowid_arr.COUNT
LOOP --{
i_commit_cnt := i_commit_cnt + 1;
IF v_repl_rowid_arr.COUNT > 0
THEN
EXECUTE IMMEDIATE v_repl_del USING v_repl_rowid_arr(i); --exception to be handled for delete. Here if delete fails, insert into archvive table should also be deleted.
END IF;
BEGIN
EXECUTE IMMEDIATE v_sql_insrt USING p_archive_log_header_id, SYSDATE, v_rowid_arr(i);
EXCEPTION
WHEN OTHERS
THEN
v_err_desc := 'Error inserting archive record for ' || p_table_name;
PR_ARCHIVE_LOG_EXCEPTIONS(p_archive_log_header_id,p_table_name,v_err_desc,SQLCODE, SQLERRM);
CONTINUE;
END;
EXECUTE IMMEDIATE v_sql_delet USING v_rowid_arr(i); --exception to be handled for delete. Here if delete fails, insert into archvive table should also be deleted.
IF i_commit_cnt = PKG_ARCHIVE.COMMIT_CNT
THEN
COMMIT;
i_commit_cnt := 0;
END IF;
END LOOP; --}
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
v_err_desc := 'ERROR IN ' || v_program_name || ' at : ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
p_error_code := SQLCODE;
PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, p_error_code);
END;
/
--------------------------------------------------------
-- DDL for Procedure PR_REPL_TABLE_DATA
--------------------------------------------------------
set define off;
CREATE OR REPLACE PROCEDURE PR_REPL_TABLE_DATA (
------------------------------------------------------------------------------------------------
-- Program Name : PR_ARCHIVE_TABLE_DATA
-- Description : Archive data for the required table
-------------------------------------------------------------------------------------------------
p_archive_batch_id NUMBER,
p_archive_log_header_id NUMBER,
p_table_name VARCHAR2,
p_del_tbl VARCHAR2,
p_table_owner VARCHAR2,
p_error_code IN OUT NUMBER )
AS
v_program_name VARCHAR2(100) := 'PR_REPL_TABLE_DATA';
v_table_name VARCHAR2(100) := NULL;
ARCHIVE_EXCEPTION EXCEPTION;
v_err_desc VARCHAR2(500);
BEGIN
p_error_code := PKG_ARCHIVE.SUCCESS;
PR_REPL_TABLE_INSRT(p_archive_log_header_id, p_table_name,p_del_tbl, p_table_owner, p_error_code); -- TO CHANGE NAME
IF p_error_code = PKG_ARCHIVE.SUCCESS THEN
UPDATE REPL_DTLS
SET TXT_REPL_STAT = PKG_ARCHIVE.ARCHIVE_COMPLETE
WHERE NUM_ARCHIVE_BATCH_ID = p_archive_batch_id
AND NUM_ARCHIVE_LOG_HEADER_ID = p_archive_log_header_id
AND TXT_TBL_NM = p_table_name;
ELSE
v_err_desc := 'ERROR IN PR_ARCHIVE_TABLE_INSRT. Error code : '|| p_error_code;
RAISE ARCHIVE_EXCEPTION;
END IF;
EXCEPTION
WHEN ARCHIVE_EXCEPTION
THEN
ROLLBACK;
PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, p_error_code);
WHEN OTHERS THEN
ROLLBACK;
v_err_desc := 'ERROR IN ' || v_program_name || ' at : ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
p_error_code := SQLCODE;
PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, p_error_code);
END PR_REPL_TABLE_DATA;
/
--------------------------------------------------------
-- DDL for Procedure PR_ARCHIVE_OR_REPLICATE
--------------------------------------------------------
set define off;
CREATE OR REPLACE PROCEDURE PR_ARCHIVE_OR_REPLICATE (
------------------------------------------------------------------------------------------------
-- Program Name : PR_ARCHIVE_TABLE_DATA
-- Description : Archive data for the required table
-------------------------------------------------------------------------------------------------
p_archive_batch_id NUMBER,
p_archive_log_header_id NUMBER,
p_table_name VARCHAR2,
p_del_table VARCHAR2,
p_table_owner VARCHAR2,
p_error_code IN OUT NUMBER )
AS
v_program_name VARCHAR2(100) := 'PR_ARCHIVE_TABLE_DATA';
v_table_name VARCHAR2(100) := NULL;
ARCHIVE_EXCEPTION EXCEPTION;
v_err_desc VARCHAR2(500);
BEGIN
p_error_code := PKG_ARCHIVE.SUCCESS;
IF PKG_ARCHIVE.RUN_MODE = 'ARCHIVE'
THEN
PR_ARCHIVE_TABLE_DATA(p_archive_batch_id, p_archive_log_header_id, p_table_name,p_table_owner, p_error_code); -- TO CHANGE NAME
END IF;
IF PKG_ARCHIVE.RUN_MODE = 'REPLICATE'
THEN
PR_REPL_TABLE_DATA(p_archive_batch_id, p_archive_log_header_id, p_table_name,p_del_table, p_table_owner, p_error_code); -- TO CHANGE NAME
END IF;
EXCEPTION
WHEN ARCHIVE_EXCEPTION
THEN
ROLLBACK;
PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, p_error_code);
WHEN OTHERS THEN
ROLLBACK;
v_err_desc := 'ERROR IN ' || v_program_name || ' at : ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
p_error_code := SQLCODE;
PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, p_error_code);
END PR_ARCHIVE_OR_REPLICATE;
/
--------------------------------------------------------
-- DDL for Procedure PR_UPD_ARCHIVE_COUNT_N_STATUS
--------------------------------------------------------
set define off;
CREATE OR REPLACE PROCEDURE PR_UPD_ARCHIVE_COUNT_N_STATUS (
------------------------------------------------------------------------------------------------
-- Program Name : PR_ARCHIVE_TABLE_DATA
-- Description : Update count and status of records archived
-------------------------------------------------------------------------------------------------
p_archive_batch_id NUMBER,
p_archive_log_header_id NUMBER,
p_table_name VARCHAR2,
p_tbl_archive_status OUT VARCHAR2,
p_error_code IN OUT NUMBER )
AS
v_program_name VARCHAR2(100) := 'PR_UPD_ARCHIVE_COUNT_N_STATUS';
v_table_name VARCHAR2(100) := NULL;
v_err_desc VARCHAR2(500);
v_num_records_to_archive ARCHIVE_LOG_HEADER.NUM_RECORDS_TO_ARCHIVE%TYPE;
v_num_records_archived ARCHIVE_LOG_HEADER.NUM_RECORDS_ARCHIVED%TYPE;
v_num_records_failed_archival ARCHIVE_LOG_HEADER.NUM_RECORDS_FAILED_ARCHIVAL%TYPE;
v_num_records_pending_archival ARCHIVE_LOG_HEADER.NUM_RECORDS_PENDING_ARCHIVAL%TYPE;
BEGIN
p_error_code := PKG_ARCHIVE.SUCCESS;
--Logic to be changed to select from ARCHIVE_PK_DTLS
IF PKG_ARCHIVE.RUN_MODE = 'ARCHIVE'
THEN
SELECT COUNT(1)
INTO v_num_records_to_archive
FROM ARCHIVE_PK_DTLS
WHERE num_archive_batch_id = p_archive_batch_id
AND num_archive_log_header_id = p_archive_log_header_id
AND txt_tbl_nm = p_table_name;
--ARCHIVE_PK_DTLS
SELECT COUNT(1)
INTO v_num_records_archived
FROM ARCHIVE_PK_DTLS
WHERE num_archive_batch_id = p_archive_batch_id
AND num_archive_log_header_id = p_archive_log_header_id
AND txt_tbl_nm = p_table_name
AND txt_arch_stat = PKG_ARCHIVE.ARCHIVE_COMPLETE;
SELECT COUNT(1)
INTO v_num_records_failed_archival
FROM ARCHIVE_LOG_EXCEPTIONS
WHERE NUM_ARCHIVE_LOG_HEADER_ID = p_archive_log_header_id
AND TXT_TABLE_NM = p_table_name;
SELECT COUNT(1)
INTO v_num_records_pending_archival
FROM ARCHIVE_PK_DTLS
WHERE num_archive_batch_id = p_archive_batch_id
AND num_archive_log_header_id = p_archive_log_header_id
AND txt_tbl_nm = p_table_name
AND txt_arch_stat = PKG_ARCHIVE.ARCHIVE_PENDING;
--v_num_records_pending_archival := v_num_records_to_archive - (v_num_records_archived + v_num_records_failed_archival);
IF v_num_records_pending_archival = 0 THEN
p_tbl_archive_status := PKG_ARCHIVE.ARCHIVE_COMPLETE;
ELSE
p_tbl_archive_status := PKG_ARCHIVE.ARCHIVE_PENDING;
END IF;
UPDATE ARCHIVE_LOG_HEADER
SET NUM_RECORDS_TO_ARCHIVE = v_num_records_to_archive,
NUM_RECORDS_ARCHIVED = v_num_records_archived,
NUM_RECORDS_FAILED_ARCHIVAL = v_num_records_failed_archival,
NUM_RECORDS_PENDING_ARCHIVAL = v_num_records_pending_archival,
TXT_TBL_ARCHIVAL_STATUS = p_tbl_archive_status
WHERE NUM_ARCHIVE_LOG_HEADER_ID = p_archive_log_header_id
AND NUM_ARCHIVE_BATCH_ID = p_archive_batch_id
AND TXT_TABLE_NAME = p_table_name;
END IF;
IF PKG_ARCHIVE.RUN_MODE = 'REPLICATE'
THEN
SELECT COUNT(1)
INTO v_num_records_to_archive
FROM REPL_DTLS
WHERE num_archive_batch_id = p_archive_batch_id
AND num_archive_log_header_id = p_archive_log_header_id
AND txt_tbl_nm = p_table_name;
--ARCHIVE_PK_DTLS
SELECT COUNT(1)
INTO v_num_records_archived
FROM REPL_DTLS
WHERE num_archive_batch_id = p_archive_batch_id
AND num_archive_log_header_id = p_archive_log_header_id
AND txt_tbl_nm = p_table_name
AND txt_repl_stat = PKG_ARCHIVE.ARCHIVE_COMPLETE;
SELECT COUNT(1)
INTO v_num_records_failed_archival
FROM ARCHIVE_LOG_EXCEPTIONS
WHERE NUM_ARCHIVE_LOG_HEADER_ID = p_archive_log_header_id
AND TXT_TABLE_NM = p_table_name;
SELECT COUNT(1)
INTO v_num_records_pending_archival
FROM REPL_DTLS
WHERE num_archive_batch_id = p_archive_batch_id
AND num_archive_log_header_id = p_archive_log_header_id
AND txt_tbl_nm = p_table_name
AND txt_repl_stat = PKG_ARCHIVE.ARCHIVE_PENDING;
--v_num_records_pending_archival := v_num_records_to_archive - (v_num_records_archived + v_num_records_failed_archival);
IF v_num_records_pending_archival = 0 THEN
p_tbl_archive_status := PKG_ARCHIVE.ARCHIVE_COMPLETE;
ELSE
p_tbl_archive_status := PKG_ARCHIVE.ARCHIVE_PENDING;
END IF;
UPDATE ARCHIVE_LOG_HEADER
SET NUM_RECORDS_TO_ARCHIVE = v_num_records_to_archive,
NUM_RECORDS_ARCHIVED = v_num_records_archived,
NUM_RECORDS_FAILED_ARCHIVAL = v_num_records_failed_archival,
NUM_RECORDS_PENDING_ARCHIVAL = v_num_records_pending_archival,
TXT_TBL_ARCHIVAL_STATUS = p_tbl_archive_status
WHERE NUM_ARCHIVE_LOG_HEADER_ID = p_archive_log_header_id
AND NUM_ARCHIVE_BATCH_ID = p_archive_batch_id
AND TXT_TABLE_NAME = p_table_name;
END IF;
EXCEPTION
WHEN OTHERS THEN
v_err_desc := 'Error in ' || v_program_name || '. Error at : '|| DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
p_error_code := SQLCODE;
PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, p_error_code);
END PR_UPD_ARCHIVE_COUNT_N_STATUS;
/
--------------------------------------------------------
-- DDL for Procedure PR_UPD_ARCHIVE_BATCH_STATUS
--------------------------------------------------------
set define off;
CREATE OR REPLACE PROCEDURE PR_UPD_ARCHIVE_BATCH_STATUS (
------------------------------------------------------------------------------------------------
-- Program Name : PR_UPD_ARCHIVE_BATCH_STATUS
-- Description : Update the status of the archive batch
-------------------------------------------------------------------------------------------------
p_archive_batch_id NUMBER,
p_archive_is_complete OUT VARCHAR2,
p_error_code IN OUT NUMBER )
AS
v_program_name VARCHAR2(100) := 'PR_UPD_ARCHIVE_BATCH_STATUS';
v_table_name VARCHAR2(100) := NULL;
v_err_desc VARCHAR2(500);
BEGIN
p_error_code := PKG_ARCHIVE.SUCCESS;
p_archive_is_complete := PKG_ARCHIVE.YES;
BEGIN
SELECT PKG_ARCHIVE.NO
INTO p_archive_is_complete
FROM DUAL
WHERE EXISTS
(SELECT 1
FROM ARCHIVE_LOG_HEADER
WHERE NUM_ARCHIVE_BATCH_ID = p_archive_batch_id
AND TXT_TBL_ARCHIVAL_STATUS = PKG_ARCHIVE.ARCHIVE_PARTIAL
);
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_archive_is_complete := PKG_ARCHIVE.YES;
END;
IF p_archive_is_complete = PKG_ARCHIVE.YES THEN
UPDATE ARCHIVE_LOG_MAIN
SET TXT_ARCHIVAL_STATUS = PKG_ARCHIVE.ARCHIVE_COMPLETE
WHERE NUM_ARCHIVE_BATCH_ID = p_archive_batch_id;
END IF;
EXCEPTION
WHEN OTHERS THEN
v_err_desc := 'Error in ' || v_program_name || '. Error at : '|| DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
p_error_code := SQLCODE;
PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, p_error_code);
END PR_UPD_ARCHIVE_BATCH_STATUS;
/
--------------------------------------------------------
-- DDL for Procedure PR_ARCHIVE_PENDING
--------------------------------------------------------
set define off;
CREATE OR REPLACE PROCEDURE PR_ARCHIVE_PENDING (p_error_code OUT NUMBER )
------------------------------------------------------------------------------------------------
-- Program Name : PR_ARCHIVE_PENDING
-- Description : Data Archiving for pending archive records
-------------------------------------------------------------------------------------------------
AS
v_program_name VARCHAR2(100) := 'PR_ARCHIVE_PENDING';
v_table_name VARCHAR2(100) := NULL;
ARCHIVE_EXCEPTION EXCEPTION;
v_err_desc VARCHAR2(500);
v_error_code NUMBER := PKG_ARCHIVE.SUCCESS;
p_archive_status VARCHAR2(20);
p_batch_status VARCHAR2(20);
v_sql VARCHAR2(1000);
CURSOR C1
IS
SELECT alh.NUM_ARCHIVE_BATCH_ID,
alh.NUM_ARCHIVE_LOG_HEADER_ID,
alh.TXT_RULE_CD,
alh.TXT_TABLE_NAME,
alh.TXT_DELETE_TABLE,
alh.TXT_TBL_OWNER
FROM ARCHIVE_LOG_MAIN alm,
ARCHIVE_LOG_HEADER alh
WHERE alm.NUM_ARCHIVE_BATCH_ID = alh.NUM_ARCHIVE_BATCH_ID
AND TXT_TBL_ARCHIVAL_STATUS IN (PKG_ARCHIVE.ARCHIVE_PARTIAL,PKG_ARCHIVE.ARCHIVE_PENDING)
ORDER BY DECODE(TXT_TBL_ARCHIVAL_STATUS,PKG_ARCHIVE.ARCHIVE_PARTIAL,1,PKG_ARCHIVE.ARCHIVE_PENDING,2);
v_prev_archive_batch_id NUMBER;
BEGIN
p_error_code := PKG_ARCHIVE.SUCCESS;
p_batch_status := PKG_ARCHIVE.BATCH_SUCCESS;
dbms_output.put_line('111111111111111111111');
-- For pending Archival Rule. At a time only one rule may be pending due to previous batch getting aborted.
FOR rec IN C1
LOOP --{
v_prev_archive_batch_id := rec.NUM_ARCHIVE_BATCH_ID;
IF v_prev_archive_batch_id != rec.NUM_ARCHIVE_BATCH_ID
THEN
PR_UPD_ARCHIVE_BATCH_STATUS(v_prev_archive_batch_id, p_batch_status, p_error_code );
p_batch_status := PKG_ARCHIVE.BATCH_SUCCESS;
IF p_error_code != pkg_archive.SUCCESS -- AND p_archive_status= PKG_ARCHIVE.YES
THEN
v_err_desc := 'ERROR IN PR_UPD_ARCHIVE_BATCH_STATUS. Error code : '|| p_error_code;
RAISE ARCHIVE_EXCEPTION;
END IF;
END IF;
PR_ARCHIVE_OR_REPLICATE(rec.NUM_ARCHIVE_BATCH_ID, rec.NUM_ARCHIVE_LOG_HEADER_ID, rec.TXT_TABLE_NAME, rec.TXT_DELETE_TABLE, rec.TXT_TBL_OWNER, p_error_code );
IF p_error_code != PKG_ARCHIVE.SUCCESS THEN
ROLLBACK;
p_batch_status := PKG_ARCHIVE.BATCH_FAILED;
v_table_name := rec.TXT_TABLE_NAME;
v_err_desc := 'ERROR IN PR_ARCHIVE_TABLE_DATA. Error code : '|| p_error_code;
PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, p_error_code);
CONTINUE; -- continue to the next record to be archived.
END IF;
PR_UPD_ARCHIVE_COUNT_N_STATUS (rec.NUM_ARCHIVE_BATCH_ID, rec.NUM_ARCHIVE_LOG_HEADER_ID, rec.TXT_TABLE_NAME,p_archive_status, p_error_code);
IF p_error_code != PKG_ARCHIVE.SUCCESS THEN
-- ROLLBACK;
v_table_name := rec.TXT_TABLE_NAME;
v_err_desc := 'ERROR IN PR_UPD_ARCHIVE_COUNT_N_STATUS. Error code : '|| p_error_code;
PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, p_error_code);
CONTINUE; -- continue to the next record to be archived.
END IF;
COMMIT; --Commit archiving of one table
END LOOP; --}
--for last batch
PR_UPD_ARCHIVE_BATCH_STATUS(v_prev_archive_batch_id, p_batch_status, p_error_code );
RETURN;
EXCEPTION
WHEN ARCHIVE_EXCEPTION
THEN
ROLLBACK;
PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, p_error_code);
WHEN OTHERS THEN
ROLLBACK;
v_err_desc := 'ERROR IN ' || v_program_name || ' at : ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
p_error_code := SQLCODE;
PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, p_error_code);
END PR_ARCHIVE_PENDING;
/
--------------------------------------------------------
-- DDL for Procedure PR_INS_PK_DTLS
--------------------------------------------------------
set define off;
CREATE OR REPLACE PROCEDURE PR_INS_PK_DTLS (
p_batch_id NUMBER,
p_archive_log_hdr_id NUMBER,
p_tbl_name VARCHAR2,
p_tbl_owner VARCHAR2,
p_rowid VARCHAR2,
p_error_code OUT NUMBER
)
------------------------------------------------------------------------------------------------
-- Program Name : PR_INS_PK_DTLS
-- Description : Insert Key data to be archived into Temporary table
-------------------------------------------------------------------------------------------------
AS
v_program_name VARCHAR2(100) := 'PR_INSERT_ARCHIVE_PK_DATA';
v_table_name VARCHAR2(100) := NULL;
ARCHIVE_EXCEPTION EXCEPTION;
i_pk_count NUMBER;
v_err_desc VARCHAR2(500);
arr_pk_cons_cols tab_col_dtls;
arr_pk_cons_cols_dtyp tab_col_dtls;
v_constraint_nm ALL_CONSTRAINTS.CONSTRAINT_NAME%TYPE;
v_sql VARCHAR2(4000);
v_tbl_pk_val ARCHIVE_PK_DTLS.TXT_PK_COL_VALS%TYPE;
v_tbl_pk_nm ARCHIVE_PK_DTLS.TXT_PK_COL_NMS%TYPE;
v_tbl_pk_dtyp ARCHIVE_PK_DTLS.TXT_PK_COL_DTYPS%TYPE;
v_owner VARCHAR2(100) := p_tbl_owner;
PNDNG CONSTANT VARCHAR2(10) := PKG_ARCHIVE.ARCHIVE_PENDING;
BEGIN
--Here check if PK constraint exists. If does not exist use rowid.
SELECT COUNT(1)
INTO i_pk_count
FROM ALL_CONSTRAINTS
WHERE OWNER = v_owner
AND TABLE_NAME = p_tbl_name
AND CONSTRAINT_TYPE = 'P';
IF i_pk_count > 0
THEN
SELECT CONSTRAINT_NAME
INTO v_constraint_nm
FROM ALL_CONSTRAINTS
WHERE OWNER = v_owner
AND TABLE_NAME = p_tbl_name
AND CONSTRAINT_TYPE = 'P';
SELECT acc.COLUMN_NAME, DATA_TYPE
BULK COLLECT
INTO arr_pk_cons_cols, arr_pk_cons_cols_dtyp
FROM ALL_CONS_COLUMNS acc, ALL_TAB_COLUMNS atc
where 1=1
AND acc.TABLE_NAME = atc.TABLE_NAME
AND acc.COLUMN_NAME = atc.COLUMN_NAME
AND acc.OWNER = atc.OWNER
AND acc.OWNER = v_owner
AND acc.TABLE_NAME = p_tbl_name
AND acc.CONSTRAINT_NAME = v_constraint_nm;
ELSE
SELECT 'rowid', 'VARCHAR2'
BULK COLLECT
INTO arr_pk_cons_cols, arr_pk_cons_cols_dtyp
FROM DUAL;
END IF;
v_sql := 'SELECT ';
IF arr_pk_cons_cols.COUNT > 0
THEN
FOR i IN 1 .. arr_pk_cons_cols.COUNT -- arr_pk_cons_cols.FIRST .. arr_pk_cons_cols.LAST
LOOP --{
IF arr_pk_cons_cols_dtyp(i) = 'DATE'
THEN
v_sql := v_sql || arr_pk_cons_cols(i);
v_sql := v_sql || '|| '',''|| ';
ELSE
v_sql := v_sql || arr_pk_cons_cols(i);
v_sql := v_sql || '|| '',''|| ';
END IF;
END LOOP; --}
v_sql := RTRIM(v_sql, '|| '',''|| ');
END IF;
v_sql := v_sql || ' FROM ' || v_owner || '.' || p_tbl_name || ' WHERE ROWID = :p_rowid';
EXECUTE IMMEDIATE v_sql INTO v_tbl_pk_val USING p_rowid;
SELECT LISTAGG(COLUMN_VALUE, ',')
INTO v_tbl_pk_nm
FROM TABLE(arr_pk_cons_cols);
SELECT LISTAGG(COLUMN_VALUE, ',')
INTO v_tbl_pk_dtyp
FROM TABLE(arr_pk_cons_cols_dtyp);
INSERT INTO ARCHIVE_PK_DTLS
(
NUM_ARCHIVE_BATCH_ID,
NUM_ARCHIVE_LOG_HEADER_ID,
TXT_TBL_NM,
TXT_PK_COL_NMS,
TXT_PK_COL_VALS,
TXT_PK_COL_DTYPS,
TXT_ARCH_STAT
)
VALUES
(
p_batch_id,
p_archive_log_hdr_id,
p_tbl_name,
v_tbl_pk_nm,
v_tbl_pk_val,
v_tbl_pk_dtyp,
PNDNG
);
EXCEPTION
WHEN ARCHIVE_EXCEPTION
THEN
ROLLBACK;
PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, p_error_code);
WHEN OTHERS THEN
ROLLBACK;
v_err_desc := 'Error in ' || v_program_name || '. Error at : '|| DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
p_error_code := SQLCODE;
PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, p_error_code);
END PR_INS_PK_DTLS;
/
--------------------------------------------------------
-- DDL for Procedure PR_INSERT_ARCHIVE_PK_DATA
--------------------------------------------------------
set define off;
CREATE OR REPLACE PROCEDURE PR_INSERT_ARCHIVE_PK_DATA (
p_archive_batch_id NUMBER,
p_archive_log_hdr_id NUMBER,
p_tbl_nm VARCHAR2,
p_tbl_owner VARCHAR2,
p_ref_rowid_arr TYP_REF_STR,
p_error_code OUT NUMBER)
------------------------------------------------------------------------------------------------
-- Program Name : PR_INSERT_ARCHIVE_PK_DATA
-- Description : Insert Key data to be archived into ARCHIVE_PK_DTLS table
-------------------------------------------------------------------------------------------------
AS
v_program_name VARCHAR2(100) := 'PR_INSERT_ARCHIVE_PK_DATA';
v_table_name VARCHAR2(100) := NULL;
ARCHIVE_EXCEPTION EXCEPTION;
v_err_desc VARCHAR2(500);
BEGIN
p_error_code := PKG_ARCHIVE.SUCCESS;
FOR i IN p_ref_rowid_arr.FIRST..p_ref_rowid_arr.LAST
LOOP
dbms_output.put_line('7777777 PR_INSERT_ARCHIVE_PK_DATA in loop' || p_ref_rowid_arr(i));
PR_INS_PK_DTLS(p_archive_batch_id, p_archive_log_hdr_id, p_tbl_nm, p_tbl_owner, p_ref_rowid_arr(i),p_error_code);
IF p_error_code != PKG_ARCHIVE.SUCCESS THEN
v_err_desc := 'ERROR IN PR_INS_PK_DTLS. Error code : '|| p_error_code;
RAISE ARCHIVE_EXCEPTION;
END IF;
END LOOP;
EXCEPTION
WHEN ARCHIVE_EXCEPTION
THEN
ROLLBACK;
PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, p_error_code);
WHEN OTHERS THEN
ROLLBACK;
v_err_desc := 'ERROR IN ' || v_program_name || ' at : ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
p_error_code := SQLCODE;
PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, p_error_code);
END PR_INSERT_ARCHIVE_PK_DATA;
/
--------------------------------------------------------
-- DDL for Procedure PR_INS_REPL_DTLS
--------------------------------------------------------
set define off;
CREATE OR REPLACE PROCEDURE PR_INS_REPL_DTLS (
p_batch_id NUMBER,
p_archive_log_hdr_id NUMBER,
p_tbl_name VARCHAR2,
p_del_tbl VARCHAR2,
p_tbl_owner VARCHAR2,
p_rowid VARCHAR2,
p_error_code OUT NUMBER
)
------------------------------------------------------------------------------------------------
-- Program Name : PR_INS_PK_DTLS
-- Description : Insert Key data to be archived into Temporary table
-------------------------------------------------------------------------------------------------
AS
v_program_name VARCHAR2(100) := 'PR_INS_REPL_DTLS';
v_table_name VARCHAR2(100) := NULL;
ARCHIVE_EXCEPTION EXCEPTION;
i_pk_count NUMBER;
v_err_desc VARCHAR2(500);
arr_pk_cons_cols tab_col_dtls;
arr_pk_cons_cols_dtyp tab_col_dtls;
arr_unique_id tab_col_dtls;
v_constraint_nm ALL_CONSTRAINTS.CONSTRAINT_NAME%TYPE;
v_sql VARCHAR2(4000);
v_tbl_pk_val ARCHIVE_PK_DTLS.TXT_PK_COL_VALS%TYPE;
v_tbl_pk_nm ARCHIVE_PK_DTLS.TXT_PK_COL_NMS%TYPE;
v_tbl_pk_dtyp ARCHIVE_PK_DTLS.TXT_PK_COL_DTYPS%TYPE;
v_owner VARCHAR2(100) := p_tbl_owner;
PNDNG CONSTANT VARCHAR2(10) := PKG_ARCHIVE.ARCHIVE_PENDING;
BEGIN
--Here check if PK constraint exists. If does not exist use rowid.
SELECT COUNT(1)
INTO i_pk_count
FROM ALL_CONSTRAINTS
WHERE OWNER = v_owner
AND TABLE_NAME = p_tbl_name
AND CONSTRAINT_TYPE = 'P';
IF i_pk_count > 0
THEN
SELECT CONSTRAINT_NAME
INTO v_constraint_nm
FROM ALL_CONSTRAINTS
WHERE OWNER = v_owner
AND TABLE_NAME = p_tbl_name
AND CONSTRAINT_TYPE = 'P';
SELECT acc.COLUMN_NAME, DATA_TYPE
BULK COLLECT
INTO arr_pk_cons_cols, arr_pk_cons_cols_dtyp
FROM ALL_CONS_COLUMNS acc, ALL_TAB_COLUMNS atc
where 1=1
AND acc.TABLE_NAME = atc.TABLE_NAME
AND acc.COLUMN_NAME = atc.COLUMN_NAME
AND acc.OWNER = atc.OWNER
AND acc.OWNER = v_owner
AND acc.TABLE_NAME = p_tbl_name
AND acc.CONSTRAINT_NAME = v_constraint_nm;
ELSE
SELECT 'rowid', 'VARCHAR2'
BULK COLLECT
INTO arr_pk_cons_cols, arr_pk_cons_cols_dtyp
FROM DUAL;
END IF;
v_sql := 'SELECT ';
IF arr_pk_cons_cols.COUNT > 0
THEN
FOR i IN 1 .. arr_pk_cons_cols.COUNT -- arr_pk_cons_cols.FIRST .. arr_pk_cons_cols.LAST
LOOP --{
IF arr_pk_cons_cols_dtyp(i) = 'DATE'
THEN
v_sql := v_sql || arr_pk_cons_cols(i);
v_sql := v_sql || '|| '',''|| ';
ELSE
v_sql := v_sql || arr_pk_cons_cols(i);
v_sql := v_sql || '|| '',''|| ';
END IF;
END LOOP; --}
v_sql := RTRIM(v_sql, '|| '',''|| ');
END IF;
v_sql := v_sql || ' FROM ' || v_owner || '.' || p_tbl_name || ' WHERE ROWID = :p_rowid';
-- dbms_output.put_line('PR_INS_REPL_DTLS v_sql ' || v_sql || ' rowid ' || p_rowid);
EXECUTE IMMEDIATE v_sql INTO v_tbl_pk_val USING p_rowid;
SELECT LISTAGG(COLUMN_VALUE, ',')
INTO v_tbl_pk_nm
FROM TABLE(arr_pk_cons_cols);
SELECT LISTAGG(COLUMN_VALUE, ',')
INTO v_tbl_pk_dtyp
FROM TABLE(arr_pk_cons_cols_dtyp);
INSERT INTO REPL_DTLS
(
NUM_ARCHIVE_BATCH_ID,
NUM_ARCHIVE_LOG_HEADER_ID,
TXT_TBL_NM,
TXT_PK_COL_NMS,
TXT_PK_COL_VALS,
TXT_PK_COL_DTYPS,
TXT_DELETE_TABLE,
TXT_DEL_TABLE_ID,
TXT_REPL_STAT
)
VALUES
(
p_batch_id,
p_archive_log_hdr_id,
p_tbl_name,
v_tbl_pk_nm,
v_tbl_pk_val,
v_tbl_pk_dtyp,
p_del_tbl,
p_rowid,
PNDNG
);
EXCEPTION
WHEN ARCHIVE_EXCEPTION
THEN
ROLLBACK;
PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, p_error_code);
WHEN OTHERS THEN
ROLLBACK;
v_err_desc := 'Error in ' || v_program_name || '. Error at : '|| DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
p_error_code := SQLCODE;
PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, p_error_code);
END PR_INS_REPL_DTLS;
/
--------------------------------------------------------
-- DDL for Procedure PR_INSERT_REPL_DATA
--------------------------------------------------------
set define off;
CREATE OR REPLACE PROCEDURE PR_INSERT_REPL_DATA (
p_archive_batch_id NUMBER,
p_archive_log_hdr_id NUMBER,
p_tbl_nm VARCHAR2,
p_del_tbl VARCHAR2,
p_tbl_owner VARCHAR2,
p_ref_rowid_arr TYP_REF_STR,
p_error_code OUT NUMBER)
------------------------------------------------------------------------------------------------
-- Program Name : PR_INSERT_ARCHIVE_PK_DATA
-- Description : Insert Key data to be archived into ARCHIVE_PK_DTLS table
-------------------------------------------------------------------------------------------------
AS
v_program_name VARCHAR2(100) := 'PR_INSERT_REPL_DATA';
v_table_name VARCHAR2(100) := NULL;
ARCHIVE_EXCEPTION EXCEPTION;
v_err_desc VARCHAR2(500);
BEGIN
p_error_code := PKG_ARCHIVE.SUCCESS;
FOR i IN p_ref_rowid_arr.FIRST..p_ref_rowid_arr.LAST
LOOP
-- dbms_output.put_line('7777777 PR_INSERT_REPL_DATA in loop' || p_ref_rowid_arr(i));
PR_INS_REPL_DTLS(p_archive_batch_id, p_archive_log_hdr_id, p_tbl_nm, p_del_tbl, p_tbl_owner, p_ref_rowid_arr(i),p_error_code);
IF p_error_code != PKG_ARCHIVE.SUCCESS THEN
v_err_desc := 'ERROR IN PR_INS_REPL_DTLS. Error code : '|| p_error_code;
RAISE ARCHIVE_EXCEPTION;
END IF;
END LOOP;
EXCEPTION
WHEN ARCHIVE_EXCEPTION
THEN
ROLLBACK;
PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, p_error_code);
WHEN OTHERS THEN
ROLLBACK;
v_err_desc := 'ERROR IN ' || v_program_name || ' at : ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
p_error_code := SQLCODE;
PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, p_error_code);
END PR_INSERT_REPL_DATA;
/
--------------------------------------------------------
-- DDL for Procedure PR_ARCHIVE_RULE_PROCESS
--------------------------------------------------------
set define off;
CREATE OR REPLACE PROCEDURE PR_ARCHIVE_RULE_PROCESS (
------------------------------------------------------------------------------------------------
-- Program Name : PR_ARCHIVE_RULE_PROCESS
-- Description : Archive data processing for rule passed
-------------------------------------------------------------------------------------------------
p_archive_batch_id NUMBER,
p_archive_log_hdr_id NUMBER,
p_rule_id NUMBER,
p_archive_table IN VARCHAR2,
p_delete_table IN VARCHAR2,
p_tbl_owner IN VARCHAR2,
p_error_code IN OUT NUMBER)
IS
v_program_name VARCHAR2(100) := 'PR_ARCHIVE_RULE_PROCESS';
v_table_name VARCHAR2(100) := NULL;
ARCHIVE_EXCEPTION EXCEPTION;
v_err_desc VARCHAR2(500);
TYPE cur_typ IS REF CURSOR;
CUR cur_typ;
-- TYPE values_t IS TABLE OF NUMBER;
l_ref_number_arr TYP_REF_NUMBER;
l_ref_date_arr TYP_REF_DATE;
l_ref_rowid_arr TYP_REF_STR;
v_sql VARCHAR2(4000);
BEGIN
p_error_code := PKG_ARCHIVE.SUCCESS;
dbms_output.put_line('333333 PR_ARCHIVE_RULE_PROCESS in start');
--Insert all records into a temp table for restartibility logic
SELECT TXT_RULE_QUERY
INTO v_sql
FROM ARCHIVE_RULES
WHERE NUM_RULE_ID = p_rule_id
AND TXT_ARCHIVE_TABLE = p_archive_table
AND TXT_TBL_OWNER = p_tbl_owner;
dbms_output.put_line('444444 PR_ARCHIVE_RULE_PROCESS in cursor');
IF PKG_ARCHIVE.RUN_MODE = 'ARCHIVE'
THEN
OPEN CUR FOR v_sql;
LOOP
NULL;
dbms_output.put_line('555555 PR_ARCHIVE_RULE_PROCESS in query loop ' || v_sql);
/* CALL PROCEDURE TO INSERT INTO TEMP_ARCHIVE PK COLS/VALS and status PENDING */
FETCH CUR BULK COLLECT INTO l_ref_rowid_arr LIMIT 5000;
IF l_ref_rowid_arr.COUNT > 0
THEN
dbms_output.put_line('666666 PR_ARCHIVE_RULE_PROCESS in query fetch');
PR_INSERT_ARCHIVE_PK_DATA (p_archive_batch_id, p_archive_log_hdr_id,p_archive_table,p_tbl_owner, l_ref_rowid_arr, p_error_code);
IF p_error_code != PKG_ARCHIVE.SUCCESS
THEN
v_table_name := p_archive_table;
v_err_desc := 'ERROR IN PR_INSERT_ARCHIVE_PK_DATA. Error code : '|| p_error_code;
RAISE ARCHIVE_EXCEPTION;
END IF;
END IF;
EXIT WHEN CUR%NOTFOUND;
END LOOP;
CLOSE CUR;
END IF;
IF PKG_ARCHIVE.RUN_MODE = 'REPLICATE'
THEN
OPEN CUR FOR v_sql;
LOOP
NULL;
dbms_output.put_line('555555 REPLICATE PR_ARCHIVE_RULE_PROCESS in query loop ' || v_sql);
/* CALL PROCEDURE TO INSERT INTO TEMP_ARCHIVE PK COLS/VALS and status PENDING */
FETCH CUR BULK COLLECT INTO l_ref_rowid_arr LIMIT 5000;
IF l_ref_rowid_arr.COUNT > 0
THEN
dbms_output.put_line('666666 REPLICATE PR_ARCHIVE_RULE_PROCESS in query fetch');
PR_INSERT_REPL_DATA (p_archive_batch_id, p_archive_log_hdr_id,p_archive_table,p_delete_table , p_tbl_owner, l_ref_rowid_arr, p_error_code);
IF p_error_code != PKG_ARCHIVE.SUCCESS
THEN
v_table_name := p_archive_table;
v_err_desc := 'ERROR IN PR_INSERT_ARCHIVE_PK_DATA. Error code : '|| p_error_code;
RAISE ARCHIVE_EXCEPTION;
END IF;
END IF;
EXIT WHEN CUR%NOTFOUND;
END LOOP;
CLOSE CUR;
END IF;
--COMMIT;
EXCEPTION
WHEN ARCHIVE_EXCEPTION
THEN
ROLLBACK;
PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, p_error_code);
WHEN OTHERS THEN
ROLLBACK;
v_err_desc := 'ERROR IN ' || v_program_name || ' AT: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
p_error_code := SQLCODE;
PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, p_error_code);
END;
/
--------------------------------------------------------
-- DDL for Procedure PR_ARCHIVE_PROCESS
--------------------------------------------------------
set define off;
CREATE OR REPLACE PROCEDURE PR_ARCHIVE_PROCESS (
------------------------------------------------------------------------------------------------
-- Program Name : PR_ARCHIVE_PROCESS
-- Description : Processing logic for archiving data for each rule
-------------------------------------------------------------------------------------------------
p_archive_batch_id NUMBER,
p_archive_log_hdr_id NUMBER,
p_rule_id NUMBER,
p_archive_table IN VARCHAR2,
p_delete_table IN VARCHAR2,
p_tbl_owner IN VARCHAR2,
p_error_code OUT NUMBER)
AS
V_PROGRAM_NAME VARCHAR2(100) := 'PR_ARCHIVE_PROCESS';
V_TABLE_NAME VARCHAR2(100) := NULL;
ARCHIVE_EXCEPTION EXCEPTION;
v_err_desc VARCHAR2(500);
p_archive_status VARCHAR2(20);
BEGIN
p_error_code := PKG_ARCHIVE.SUCCESS;
dbms_output.put_line('222222222222222222 PR_ARCHIVE_PROCESS');
/* New rule processing */
PR_ARCHIVE_RULE_PROCESS(p_archive_batch_id,p_archive_log_hdr_id, p_rule_id, p_archive_table,p_delete_table, p_tbl_owner, p_error_code);
IF p_error_code != PKG_ARCHIVE.SUCCESS THEN
v_err_desc := 'ERROR IN PR_ARCHIVE_RULE_PROCESS. Error code : '|| p_error_code;
RAISE ARCHIVE_EXCEPTION;
END IF;
-- COMMIT; -- for testing
-- RETURN; -- for testing
PR_ARCHIVE_PENDING(p_error_code);
IF p_error_code != PKG_ARCHIVE.SUCCESS THEN
v_err_desc := 'ERROR IN PR_ARCHIVE_PENDING. Error code : '|| p_error_code;
RAISE ARCHIVE_EXCEPTION;
END IF;
EXCEPTION
WHEN ARCHIVE_EXCEPTION
THEN
ROLLBACK;
PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, p_error_code);
WHEN OTHERS THEN
ROLLBACK;
v_err_desc := 'ERROR IN ' || v_program_name || ' at : ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
p_error_code := SQLCODE;
PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, p_error_code);
END PR_ARCHIVE_PROCESS;
/
--------------------------------------------------------
-- DDL for Procedure PR_GENERATE_ALH_QUERY_STR
--------------------------------------------------------
set define off;
CREATE OR REPLACE PROCEDURE PR_GENERATE_ALH_QUERY_STR (
------------------------------------------------------------------------------------------------
-- Program Name : PR_GENERATE_ALH_QUERY_STR
-- Description : Generate Insert string for ARCHIVE_LOG_HEADER table
-------------------------------------------------------------------------------------------------
p_archive_batch_id NUMBER,
p_rule_cd VARCHAR2,
p_table_name VARCHAR2,
p_delete_table VARCHAR2,
p_tbl_owner VARCHAR2,
p_query_str OUT VARCHAR2,
p_archive_log_header_id OUT NUMBER,
p_error_code IN OUT NUMBER )
AS
v_program_name VARCHAR2(100) := 'PR_GENERATE_ALH_QUERY_STR';
v_table_name VARCHAR2(100) := NULL;
v_err_desc VARCHAR2(500);
i_archive_log_header_id NUMBER;
BEGIN
p_error_code := PKG_ARCHIVE.SUCCESS;
SELECT SEQ_ARCHIVE_LOG_HEADER_ID.NEXTVAL
INTO i_archive_log_header_id
FROM DUAL;
p_archive_log_header_id := i_archive_log_header_id;
p_query_str := 'INSERT INTO ' ||PKG_ARCHIVE.ARCHIVE_SCHEMA || '.ARCHIVE_LOG_HEADER( ';
p_query_str := p_query_str || 'NUM_ARCHIVE_LOG_HEADER_ID, ';
p_query_str := p_query_str || 'TXT_RULE_CD, ';
p_query_str := p_query_str || 'NUM_ARCHIVE_BATCH_ID, ';
p_query_str := p_query_str || 'TXT_TABLE_NAME, ';
p_query_str := p_query_str || 'TXT_DELETE_TABLE, ';
p_query_str := p_query_str || 'TXT_TBL_OWNER, ';
p_query_str := p_query_str || 'TXT_TBL_ARCHIVAL_STATUS) ';
p_query_str := p_query_str || 'VALUES (';
p_query_str := p_query_str || i_archive_log_header_id ||', ';
p_query_str := p_query_str || '''' || p_rule_cd || ''', ';
p_query_str := p_query_str || p_archive_batch_id || ', ';
p_query_str := p_query_str || '''' || p_table_name || ''', ';
p_query_str := p_query_str || '''' || p_delete_table || ''', ';
p_query_str := p_query_str || '''' || p_tbl_owner || ''', ';
p_query_str := p_query_str || '''PENDING'')';
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
v_err_desc := 'ERROR IN ' || v_program_name || ' at : ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
p_error_code := SQLCODE;
PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, p_error_code);
END PR_GENERATE_ALH_QUERY_STR;
/
--------------------------------------------------------
-- DDL for Procedure PR_INSERT_ARCHIVE_LOG_HEADER
--------------------------------------------------------
set define off;
CREATE OR REPLACE PROCEDURE PR_INSERT_ARCHIVE_LOG_HEADER (
------------------------------------------------------------------------------------------------
-- Program Name : PR_INSERT_ARCHIVE_LOG_HEADER
-- Description : Insert data into ARCHIVE LOG HEADER table for a given rule
-------------------------------------------------------------------------------------------------
p_archive_batch_id IN VARCHAR2,
p_rule_id IN NUMBER,
p_archive_table IN VARCHAR2,
p_delete_table IN VARCHAR2,
p_tbl_owner IN VARCHAR2,
p_out_archve_log_hdr OUT NUMBER,
p_error_code IN OUT NUMBER )
AS
V_PROGRAM_NAME VARCHAR2(100) := 'PR_INSERT_ARCHIVE_LOG_HEADER';
ARCHIVE_EXCEPTION EXCEPTION;
V_TABLE_NAME VARCHAR2(100) := NULL;
V_ERR_DESC VARCHAR2(500);
v_output_arr TYPE_REF_STR;
v_output NUMBER;
v_query_str VARCHAR2(4000);
BEGIN
p_error_code := PKG_ARCHIVE.SUCCESS;
-- v_output_arr := FXN_STR_TO_COLUMNS(p_archive_table_list,',');
PR_GENERATE_ALH_QUERY_STR(p_archive_batch_id, p_rule_id, p_archive_table,p_delete_table,p_tbl_owner, v_query_str,p_out_archve_log_hdr, p_error_code);
IF p_error_code != PKG_ARCHIVE.SUCCESS THEN
v_err_desc := 'ERROR IN PR_GENERATE_ALH_QUERY_STR. Error code : '|| p_error_code;
RAISE ARCHIVE_EXCEPTION;
END IF;
BEGIN
EXECUTE IMMEDIATE v_query_str;
EXCEPTION
WHEN OTHERS THEN
p_error_code := SQLCODE;
v_err_desc := 'Error in Execution of Query: ' || v_query_str;
RAISE ARCHIVE_EXCEPTION;
END;
EXCEPTION
WHEN ARCHIVE_EXCEPTION
THEN
ROLLBACK;
PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, p_error_code);
WHEN OTHERS THEN
ROLLBACK;
v_err_desc := 'ERROR IN ' || v_program_name || ' at : ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
p_error_code := SQLCODE;
PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, p_error_code);
END PR_INSERT_ARCHIVE_LOG_HEADER;
/
--------------------------------------------------------
-- DDL for Procedure PR_ARCHIVE_MACHINE
--------------------------------------------------------
set define off;
CREATE OR REPLACE PROCEDURE PR_ARCHIVE_MACHINE
------------------------------------------------------------------------------------------------
-- Program Name : PR_ARCHIVE_MACHINE
-- Description : Data Archiving Main
-------------------------------------------------------------------------------------------------
AS
v_program_name VARCHAR2(100) := 'PR_ARCHIVE_MACHINE';
v_table_name VARCHAR2(100) := NULL;
ARCHIVE_EXCEPTION EXCEPTION;
v_err_desc VARCHAR2(500);
v_error_code NUMBER := PKG_ARCHIVE.SUCCESS;
v_archve_log_hdr NUMBER;
v_sql_row_move VARCHAR2(500);
v_sql_tbl_shrink VARCHAR2(500);
CURSOR C2
IS
SELECT NUM_RULE_ID,
TXT_ARCHIVE_TABLE,
TXT_DELETE_TABLE,
TXT_TBL_OWNER
FROM ARCHIVE_RULES
WHERE TXT_ACTIVE_FLG = 'Y'
ORDER BY NUM_RULE_ID;
v_archive_batch_id NUMBER := 0;
v_prev_rule_id ARCHIVE_RULES.NUM_RULE_ID%TYPE;
BEGIN
DBMS_OUTPUT.DISABLE;
v_error_code := PKG_ARCHIVE.SUCCESS;
dbms_output.put_line('0000000000000');
-- For pending Archival Rule. At a time only one rule may be pending due to previous batch getting aborted.
PR_ARCHIVE_PENDING(v_error_code);
IF v_error_code != PKG_ARCHIVE.SUCCESS THEN
v_err_desc := 'ERROR IN PR_ARCHIVE_PENDING. Error code : '|| v_error_code;
RAISE ARCHIVE_EXCEPTION;
END IF;
--For New Archival Rules
FOR rec IN C2
LOOP --{
IF NVL(v_prev_rule_id,0) != rec.NUM_RULE_ID
THEN
PR_INSERT_ARCHIVE_LOG_MAIN(v_archive_batch_id, v_error_code);
IF v_error_code != PKG_ARCHIVE.SUCCESS THEN
ROLLBACK;
v_err_desc := 'ERROR IN PR_INSERT_ARCHIVE_LOG_MAIN. Error code : '|| v_error_code || ' .Archive Batch Id: ' || v_archive_batch_id;
PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, v_error_code);
CONTINUE;
END IF;
END IF;
dbms_output.put_line('v_archive_batch_id ' || v_archive_batch_id || 'error ' || v_error_code );
PR_INSERT_ARCHIVE_LOG_HEADER(v_archive_batch_id, rec.NUM_RULE_ID, rec.TXT_ARCHIVE_TABLE, rec.TXT_DELETE_TABLE, rec.TXT_TBL_OWNER, v_archve_log_hdr, v_error_code);
IF v_error_code != PKG_ARCHIVE.SUCCESS THEN
ROLLBACK;
v_err_desc := 'ERROR IN PR_INSERT_ARCHIVE_LOG_HEADER. Error code : '|| v_error_code
|| ' .Archive Batch Id: ' || v_archive_batch_id || ' .Archive Log Header Id: ' || v_archve_log_hdr;
PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, v_error_code);
CONTINUE;
END IF;
PR_ARCHIVE_PROCESS(v_archive_batch_id, v_archve_log_hdr, rec.NUM_RULE_ID, rec.TXT_ARCHIVE_TABLE, rec.TXT_DELETE_TABLE, rec.TXT_TBL_OWNER, v_error_code);
IF v_error_code != PKG_ARCHIVE.SUCCESS THEN
ROLLBACK;
v_err_desc := 'ERROR IN PR_ARCHIVE_PROCESS. Error code : '|| v_error_code
|| ' .Archive Batch Id: ' || v_archive_batch_id || ' .Archive Log Header Id: ' || v_archve_log_hdr;
PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, v_error_code);
CONTINUE;
END IF;
v_sql_row_move := 'ALTER TABLE ' || rec.TXT_TBL_OWNER || '.' || rec.TXT_ARCHIVE_TABLE || ' ENABLE ROW MOVEMENT';
EXECUTE IMMEDIATE v_sql_row_move;
v_sql_tbl_shrink := 'ALTER TABLE ' || rec.TXT_TBL_OWNER || '.' || rec.TXT_ARCHIVE_TABLE || ' SHRINK SPACE CASCADE';
EXECUTE IMMEDIATE v_sql_tbl_shrink;
v_prev_rule_id := rec.NUM_RULE_ID;
END LOOP; --}
COMMIT;
RETURN;
EXCEPTION
WHEN ARCHIVE_EXCEPTION
THEN
ROLLBACK;
PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, v_error_code);
WHEN OTHERS THEN
ROLLBACK;
v_err_desc := 'ERROR IN ' || v_program_name || ' AT: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
v_error_code := SQLCODE;
PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, v_error_code);
END PR_ARCHIVE_MACHINE;
/
--------------------------------------------------------
-- DDL for Procedure RUN_ARCHIVE_MACHINE
--------------------------------------------------------
set define off;
CREATE OR REPLACE PROCEDURE RUN_ARCHIVE_MACHINE AS
v_program_name VARCHAR2(100) := 'RUN_ARCHIVE_MACHINE';
v_table_name VARCHAR2(100) := NULL;
ARCHIVE_EXCEPTION EXCEPTION;
v_err_desc VARCHAR2(500);
v_error_code NUMBER := PKG_ARCHIVE.SUCCESS;
v_archv_btn VARCHAR2(10) := 'STOP';
v_loop_cntr NUMBER := 0;
BEGIN
WHILE TRUE
LOOP --{
SELECT ARCHIVE_BTN
INTO v_archv_btn
FROM ARCHIVE_CNTRL;
IF v_archv_btn = 'START'
THEN
v_loop_cntr := v_loop_cntr + 1;
PR_ARCHIVE_MACHINE;
COMMIT;
-- v_err_desc := 'Calling archive machine ' || v_loop_cntr;
-- PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, v_error_code);
ELSE
EXIT;
END IF;
DBMS_LOCK.SLEEP (30);
END LOOP; --}
END RUN_ARCHIVE_MACHINE;
/
Watch for the opportune time,
and beware of evil, and do not be ashamed to be yourself.
For there is a shame that leads to sin,
and there is a shame that is glory and favor
Sirach 4:20-21
No comments:
Post a Comment