In this post I have put the list of tables, packages, procedure and functions which have been modified for replication.
--------------------------------------------------------
-- DDL for Table ARCHIVE_CNTRL
--------------------------------------------------------
CREATE TABLE HR_ARCHIVE.ARCHIVE_CNTRL
( ARCHIVE_BTN VARCHAR2(10 BYTE)
) ;
--------------------------------------------------------
-- DDL for Table ARCHIVE_ERROR_LOG
--------------------------------------------------------
CREATE TABLE HR_ARCHIVE.ARCHIVE_ERROR_LOG
( NUM_ARCHIVE_LOG_ID NUMBER,
TXT_PROGRAM_NAME VARCHAR2(100 BYTE),
TXT_TABLE_NAME VARCHAR2(30 BYTE),
TXT_REMARKS VARCHAR2(4000 BYTE),
TXT_SQLCODE VARCHAR2(20 BYTE),
TXT_SQLERRM VARCHAR2(4000 BYTE),
DAT_ARCHIVE_DATE DATE,
CRTD_BY VARCHAR2(100 BYTE) DEFAULT USER,
CRTD_DT DATE DEFAULT SYSDATE,
UPDTD_BY VARCHAR2(100 BYTE),
UPDTD_DT DATE
) ;
--------------------------------------------------------
-- DDL for Table ARCHIVE_LOG_EXCEPTIONS
--------------------------------------------------------
CREATE TABLE HR_ARCHIVE.ARCHIVE_LOG_EXCEPTIONS
( NUM_ARCHIVE_LOG_EXCEPTIONS_ID NUMBER(20,0),
NUM_ARCHIVE_LOG_HEADER_ID NUMBER(20,0),
TXT_TABLE_NM VARCHAR2(100 BYTE),
NUM_EXCEPTION_ERR_CODE NUMBER,
TXT_EXCEPTION_ERR_DESC VARCHAR2(4000 BYTE),
TXT_REMARKS VARCHAR2(500 BYTE),
CRTD_BY VARCHAR2(100 BYTE) DEFAULT USER,
CRTD_DT DATE DEFAULT SYSDATE,
UPDTD_BY VARCHAR2(100 BYTE),
UPDTD_DT DATE
) ;
--------------------------------------------------------
-- DDL for Table ARCHIVE_LOG_HEADER
--------------------------------------------------------
CREATE TABLE HR_ARCHIVE.ARCHIVE_LOG_HEADER
( NUM_ARCHIVE_LOG_HEADER_ID NUMBER(20,0),
NUM_ARCHIVE_BATCH_ID NUMBER(20,0),
TXT_RULE_CD VARCHAR2(20 BYTE),
TXT_TABLE_NAME VARCHAR2(100 BYTE),
TXT_DELETE_TABLE VARCHAR2(100 BYTE),
TXT_TBL_OWNER VARCHAR2(100 BYTE),
TXT_TBL_ARCHIVAL_STATUS VARCHAR2(20 BYTE),
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 BYTE) DEFAULT 'SYSTEM',
CRTD_DT DATE DEFAULT SYSDATE,
UPDTD_BY VARCHAR2(100 BYTE) DEFAULT 'SYSTEM',
UPDTD_DT DATE DEFAULT SYSDATE
) ;
--------------------------------------------------------
-- DDL for Table ARCHIVE_LOG_MAIN
--------------------------------------------------------
CREATE TABLE HR_ARCHIVE.ARCHIVE_LOG_MAIN
( NUM_ARCHIVE_BATCH_ID NUMBER(20,0),
DAT_ARCHIVE_DATE DATE,
TXT_ARCHIVAL_STATUS VARCHAR2(20 BYTE),
CRTD_BY VARCHAR2(100 BYTE) DEFAULT 'SYSTEM',
CRTD_DT DATE DEFAULT SYSDATE,
UPDTD_BY VARCHAR2(100 BYTE) DEFAULT 'SYSTEM',
UPDTD_DT DATE DEFAULT SYSDATE
) ;
--------------------------------------------------------
-- DDL for Table ARCHIVE_PK_DTLS
--------------------------------------------------------
CREATE TABLE HR_ARCHIVE.ARCHIVE_PK_DTLS
( NUM_ARCHIVE_BATCH_ID NUMBER(20,0),
NUM_ARCHIVE_LOG_HEADER_ID NUMBER(20,0),
TXT_TBL_NM VARCHAR2(50 BYTE),
TXT_PK_COL_NMS VARCHAR2(500 BYTE),
TXT_PK_COL_VALS VARCHAR2(1000 BYTE),
TXT_PK_COL_DTYPS VARCHAR2(1000 BYTE),
TXT_ARCH_STAT VARCHAR2(20 BYTE),
CRTD_BY VARCHAR2(100 BYTE) DEFAULT 'SYSTEM',
CRTD_DT DATE DEFAULT SYSDATE,
UPDTD_BY VARCHAR2(100 BYTE) DEFAULT 'SYSTEM',
UPDTD_DT DATE DEFAULT SYSDATE
) ;
--------------------------------------------------------
-- DDL for Table ARCHIVE_RULES
--------------------------------------------------------
CREATE TABLE HR_ARCHIVE.ARCHIVE_RULES
( NUM_RULE_ID NUMBER,
TXT_RULE_DESC VARCHAR2(1000 BYTE),
TXT_RULE_QUERY VARCHAR2(4000 BYTE),
TXT_ACTIVE_FLG VARCHAR2(1 BYTE) DEFAULT 'Y',
TXT_ARCHIVE_TABLE VARCHAR2(100 BYTE),
TXT_DELETE_TABLE VARCHAR2(1000 BYTE),
TXT_TBL_OWNER VARCHAR2(100 BYTE),
CRTD_BY VARCHAR2(100 BYTE) DEFAULT 'SYSTEM',
CRTD_DT DATE DEFAULT SYSDATE,
UPDTD_BY VARCHAR2(100 BYTE) DEFAULT 'SYSTEM',
UPDTD_DT DATE DEFAULT SYSDATE
) ;
--------------------------------------------------------
-- DDL for Table CURR_TRDE_DT
--------------------------------------------------------
CREATE TABLE HR_ARCHIVE.CURR_TRDE_DT
( TRDE_DT DATE
) ;
--------------------------------------------------------
-- DDL for Table CUSTOMER
--------------------------------------------------------
CREATE TABLE HR_ARCHIVE.CUSTOMER
( CUST_ID NUMBER,
CUST_NAME VARCHAR2(100 BYTE),
PHONE_NUMBER VARCHAR2(20 BYTE),
EMAIL VARCHAR2(30 BYTE),
ADDR_LINE_1 VARCHAR2(500 BYTE),
ADDR_LINE_2 VARCHAR2(500 BYTE),
ADDR_LINE_3 VARCHAR2(500 BYTE),
COUNTRY_ID CHAR(2 BYTE),
REGION_ID NUMBER,
GENDER VARCHAR2(20 BYTE),
ARCHIVE_LOG_HEADER_ID NUMBER(20,0),
ARCHIVE_DATE DATE
) ;
--------------------------------------------------------
-- DDL for Table DAILY_TRANSACTIONS
--------------------------------------------------------
CREATE TABLE HR_ARCHIVE.DAILY_TRANSACTIONS
( TRANS_ID NUMBER,
TRANS_DATE DATE,
CUST_ID NUMBER,
TRANS_AMT NUMBER,
TRANS_CRNCY VARCHAR2(3 BYTE),
TRANS_IND VARCHAR2(10 BYTE),
ACCT_NO VARCHAR2(50 BYTE),
BNK_NAME VARCHAR2(100 BYTE),
BNK_LOCATION VARCHAR2(100 BYTE),
BANK_IFSC VARCHAR2(100 BYTE),
ARCHIVE_LOG_HEADER_ID NUMBER(20,0),
ARCHIVE_DATE DATE
) ;
--------------------------------------------------------
-- DDL for Table EMPLOYEES
--------------------------------------------------------
CREATE TABLE HR_ARCHIVE.EMPLOYEES
( EMPLOYEE_ID NUMBER(6,0),
FIRST_NAME VARCHAR2(20 BYTE),
LAST_NAME VARCHAR2(25 BYTE),
EMAIL VARCHAR2(25 BYTE),
PHONE_NUMBER VARCHAR2(20 BYTE),
HIRE_DATE DATE,
JOB_ID VARCHAR2(10 BYTE),
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
) ENABLE ROW MOVEMENT ;
--------------------------------------------------------
-- DDL for Table EMP_TEST
--------------------------------------------------------
CREATE TABLE HR_ARCHIVE.EMP_TEST
( EMPLOYEE_ID NUMBER(6,0),
EMP_NAME VARCHAR2(20 BYTE),
EMAIL VARCHAR2(25 BYTE),
PHONE_NUMBER VARCHAR2(20 BYTE),
HIRE_DATE DATE,
ARCHIVE_LOG_HEADER_ID NUMBER(20,0),
ARCHIVE_DATE DATE,
CLOB_TEST CLOB,
BLOB_TEST BLOB,
FLOAT_TEST FLOAT(126),
FLOAT_TEST_PK FLOAT(126),
TSTAMP_TEST TIMESTAMP (6),
TSTAMP_TEST_PK TIMESTAMP (6)
) ;
--------------------------------------------------------
-- DDL for Table JOB_HISTORY
--------------------------------------------------------
CREATE TABLE HR_ARCHIVE.JOB_HISTORY
( EMPLOYEE_ID NUMBER(6,0),
START_DATE DATE,
END_DATE DATE,
JOB_ID VARCHAR2(10 BYTE),
DEPARTMENT_ID NUMBER(4,0),
ARCHIVE_DATE DATE,
ARCHIVE_LOG_HEADER_ID NUMBER(20,0)
) ;
--------------------------------------------------------
-- DDL for Table REPL_DTLS
--------------------------------------------------------
CREATE TABLE HR_ARCHIVE.REPL_DTLS
( NUM_ARCHIVE_BATCH_ID NUMBER(20,0),
NUM_ARCHIVE_LOG_HEADER_ID NUMBER(20,0),
TXT_TBL_NM VARCHAR2(50 BYTE),
TXT_PK_COL_NMS VARCHAR2(500 BYTE),
TXT_PK_COL_VALS VARCHAR2(1000 BYTE),
TXT_PK_COL_DTYPS VARCHAR2(1000 BYTE),
TXT_DELETE_TABLE VARCHAR2(100 BYTE),
TXT_DEL_TABLE_ID VARCHAR2(100 BYTE),
TXT_REPL_STAT VARCHAR2(20 BYTE),
CRTD_BY VARCHAR2(100 BYTE) DEFAULT 'SYSTEM',
CRTD_DT DATE DEFAULT SYSDATE,
UPDTD_BY VARCHAR2(100 BYTE) DEFAULT 'SYSTEM',
UPDTD_DT DATE DEFAULT SYSDATE
) ;
--------------------------------------------------------
-- DDL for Index IDX_ARCHIVE_ERROR_LOG
--------------------------------------------------------
CREATE INDEX HR_ARCHIVE.IDX_ARCHIVE_ERROR_LOG ON HR_ARCHIVE.ARCHIVE_ERROR_LOG (TXT_TABLE_NAME, DAT_ARCHIVE_DATE) TABLESPACE INDX;
--------------------------------------------------------
-- DDL for Index IDX_ARCHIVE_ERROR_LOG_1
--------------------------------------------------------
CREATE INDEX HR_ARCHIVE.IDX_ARCHIVE_ERROR_LOG_1 ON HR_ARCHIVE.ARCHIVE_ERROR_LOG (TXT_PROGRAM_NAME) TABLESPACE INDX;
--------------------------------------------------------
-- DDL for Index PK_ARCHIVE_BATCH_ID
--------------------------------------------------------
CREATE UNIQUE INDEX HR_ARCHIVE.PK_ARCHIVE_BATCH_ID ON HR_ARCHIVE.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 HR_ARCHIVE.PK_ARCHIVE_LOG_ID ON HR_ARCHIVE.ARCHIVE_ERROR_LOG (NUM_ARCHIVE_LOG_ID) TABLESPACE INDX;
--------------------------------------------------------
-- DDL for Index PK_EXCEPTION_DETAILS_ID
--------------------------------------------------------
CREATE UNIQUE INDEX HR_ARCHIVE.PK_EXCEPTION_DETAILS_ID ON HR_ARCHIVE.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 HR_ARCHIVE.EMP_EMP_ID_PK ON HR_ARCHIVE.EMPLOYEES (EMPLOYEE_ID) TABLESPACE INDX;
--------------------------------------------------------
-- DDL for Index JHIST_EMP_ID_ST_DATE_PK
--------------------------------------------------------
CREATE UNIQUE INDEX HR_ARCHIVE.JHIST_EMP_ID_ST_DATE_PK ON HR_ARCHIVE.JOB_HISTORY (EMPLOYEE_ID, START_DATE) TABLESPACE INDX;
------------ Constraints for Table ARCHIVE_ERROR_LOG ---------
ALTER TABLE ARCHIVE_ERROR_LOG ADD CONSTRAINT PK_ARCHIVE_LOG_ID PRIMARY KEY (NUM_ARCHIVE_LOG_ID)
USING INDEX TABLESPACE INDX ENABLE;
------------ Constraints for Table ARCHIVE_LOG_EXCEPTIONS ---------
ALTER TABLE ARCHIVE_LOG_EXCEPTIONS ADD CONSTRAINT PK_EXCEPTION_DETAILS_ID PRIMARY KEY (NUM_ARCHIVE_LOG_EXCEPTIONS_ID)
USING INDEX TABLESPACE INDX ENABLE;
------------ Constraints for Table ARCHIVE_LOG_HEADER ---------
ALTER TABLE ARCHIVE_LOG_HEADER ADD CONSTRAINT PK_ARCHIVE_LOG_HEADER_ID PRIMARY KEY (NUM_ARCHIVE_LOG_HEADER_ID)
USING INDEX TABLESPACE INDX ENABLE;
------------ Constraints for Table ARCHIVE_LOG_MAIN ---------
ALTER TABLE ARCHIVE_LOG_MAIN ADD CONSTRAINT PK_ARCHIVE_BATCH_ID PRIMARY KEY (NUM_ARCHIVE_BATCH_ID)
USING INDEX TABLESPACE INDX ENABLE;
------------ Constraints for Table ARCHIVE_RULES ---------
ALTER TABLE ARCHIVE_RULES ADD CONSTRAINT PK_RULE_CD PRIMARY KEY (TXT_RULE_CD)
USING INDEX TABLESPACE INDX ENABLE;
--------------------------------------------------------
-- Constraints for Table EMPLOYEES
--------------------------------------------------------
ALTER TABLE EMPLOYEES MODIFY (LAST_NAME CONSTRAINT EMP_LAST_NAME_NN NOT NULL ENABLE);
ALTER TABLE EMPLOYEES MODIFY (EMAIL CONSTRAINT EMP_EMAIL_NN NOT NULL ENABLE) ;
ALTER TABLE EMPLOYEES MODIFY (HIRE_DATE CONSTRAINT EMP_HIRE_DATE_NN NOT NULL ENABLE);
ALTER TABLE EMPLOYEES MODIFY (JOB_ID CONSTRAINT EMP_JOB_NN NOT NULL ENABLE);
ALTER TABLE EMPLOYEES ADD CONSTRAINT EMP_EMP_ID_PK PRIMARY KEY (EMPLOYEE_ID)
USING INDEX EMP_EMP_ID_PK ENABLE;
--------------------------------------------------------
-- Constraints for Table JOB_HISTORY
--------------------------------------------------------
ALTER TABLE JOB_HISTORY MODIFY (EMPLOYEE_ID CONSTRAINT JHIST_EMPLOYEE_NN NOT NULL ENABLE);
ALTER TABLE JOB_HISTORY MODIFY (START_DATE CONSTRAINT JHIST_START_DATE_NN NOT NULL ENABLE);
ALTER TABLE JOB_HISTORY MODIFY (END_DATE CONSTRAINT JHIST_END_DATE_NN NOT NULL ENABLE);
ALTER TABLE JOB_HISTORY MODIFY (JOB_ID CONSTRAINT JHIST_JOB_NN NOT NULL ENABLE);
ALTER TABLE JOB_HISTORY ADD CONSTRAINT JHIST_EMP_ID_ST_DATE_PK PRIMARY KEY (EMPLOYEE_ID, START_DATE)
USING INDEX JHIST_EMP_ID_ST_DATE_PK ENABLE;
--------------------------------------------------------
-- DDL for Function FXN_STR_TO_COLUMNS
--------------------------------------------------------
CREATE OR REPLACE EDITIONABLE FUNCTION "HR_ARCHIVE"."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 EDITIONABLE PACKAGE "HR_ARCHIVE"."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';
--MAIN_SCHEMA CONSTANT VARCHAR2(100) := 'CUST';
ARCHIVE_SCHEMA CONSTANT VARCHAR2(100) := 'HR_ARCHIVE';
RUN_MODE CONSTANT VARCHAR2(10) := 'ARCHIVE'; -- REPLICATE --ARCHIVE
END PKG_ARCHIVE;
/
--------------------------------------------------------
-- DDL for Procedure PR_ARCHIVE_ERROR_LOG
--------------------------------------------------------
set define off;
CREATE OR REPLACE PROCEDURE "HR_ARCHIVE"."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_ARCHIVE_LOG_EXCEPTIONS
--------------------------------------------------------
set define off;
CREATE OR REPLACE PROCEDURE "HR_ARCHIVE"."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_MACHINE
--------------------------------------------------------
set define off;
CREATE OR REPLACE PROCEDURE "HR_ARCHIVE"."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 PR_ARCHIVE_OR_REPLICATE
--------------------------------------------------------
set define off;
CREATE OR REPLACE PROCEDURE "HR_ARCHIVE"."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_ARCHIVE_PENDING
--------------------------------------------------------
set define off;
CREATE OR REPLACE PROCEDURE "HR_ARCHIVE"."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;
-- IF PKG_ARCHIVE.RUN_MODE = 'REPLICATE'
-- THEN
-- v_sql := 'DELETE FROM ' || rec.TXT_TBL_OWNER || '.' || rec.TXT_TABLE_NAME || ' WHERE REPL_STAT = ''' || PKG_ARCHIVE.REPL_PENDING || '''';
-- 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_ARCHIVE_PENDING_OLD
--------------------------------------------------------
set define off;
CREATE OR REPLACE PROCEDURE "HR_ARCHIVE"."PR_ARCHIVE_PENDING_OLD" (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;
CURSOR C1
IS
SELECT alh.NUM_ARCHIVE_BATCH_ID,
alh.NUM_ARCHIVE_LOG_HEADER_ID,
alh.TXT_RULE_CD
FROM ARCHIVE_LOG_MAIN alm,
ARCHIVE_LOG_HEADER alh
WHERE alm.NUM_ARCHIVE_BATCH_ID = alh.NUM_ARCHIVE_BATCH_ID
AND TXT_ARCHIVAL_STATUS = PKG_ARCHIVE.ARCHIVE_PENDING;
v_archive_batch_id NUMBER;
BEGIN
p_error_code := PKG_ARCHIVE.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 --{
PR_ARCHIVE_PROCESS(rec.NUM_ARCHIVE_BATCH_ID,rec.NUM_ARCHIVE_LOG_HEADER_ID, rec.TXT_RULE_CD, p_error_code);
IF p_error_code != PKG_ARCHIVE.SUCCESS THEN
v_err_desc := 'ERROR IN PR_ARCHIVE_PROCESS. Error code : '|| p_error_code;
RAISE ARCHIVE_EXCEPTION;
END IF;
END LOOP; --}
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_OLD;
/
--------------------------------------------------------
-- DDL for Procedure PR_ARCHIVE_PROCESS
--------------------------------------------------------
set define off;
CREATE OR REPLACE PROCEDURE "HR_ARCHIVE"."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_ARCHIVE_PROCESS_OLD
--------------------------------------------------------
set define off;
CREATE OR REPLACE PROCEDURE "HR_ARCHIVE"."PR_ARCHIVE_PROCESS_OLD" (
------------------------------------------------------------------------------------------------
-- 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_cd NUMBER,
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);
CURSOR C1(p_archive_batch_id NUMBER)
IS
SELECT NUM_ARCHIVE_LOG_HEADER_ID,
TXT_TABLE_NAME
FROM ARCHIVE_LOG_HEADER
WHERE NUM_ARCHIVE_BATCH_ID = p_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);
p_archive_status VARCHAR2(20);
BEGIN
p_error_code := PKG_ARCHIVE.SUCCESS;
dbms_output.put_line('222222222222222222');
/* New rule processing */
-- this should happen after pending are all completed
IF p_rule_cd IS NOT NULL THEN
PR_ARCHIVE_RULE_PROCESS(p_archive_batch_id,p_archive_log_hdr_id, p_rule_cd, 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;
END IF;
FOR rec IN C1(p_archive_batch_id)
LOOP --{
PR_ARCHIVE_TABLE_DATA(p_archive_batch_id, rec.NUM_ARCHIVE_LOG_HEADER_ID, rec.TXT_TABLE_NAME, 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_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 (p_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; --}
PR_UPD_ARCHIVE_BATCH_STATUS(p_archive_batch_id, p_archive_status, p_error_code );
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;
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_OLD;
/
--------------------------------------------------------
-- DDL for Procedure PR_ARCHIVE_RULE_PROCESS
--------------------------------------------------------
set define off;
CREATE OR REPLACE PROCEDURE "HR_ARCHIVE"."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_TABLE_DATA
--------------------------------------------------------
set define off;
CREATE OR REPLACE PROCEDURE "HR_ARCHIVE"."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_ARCHIVE_TABLE_INSRT
--------------------------------------------------------
set define off;
CREATE OR REPLACE PROCEDURE "HR_ARCHIVE"."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_GENERATE_ALH_QUERY_STR
--------------------------------------------------------
set define off;
CREATE OR REPLACE PROCEDURE "HR_ARCHIVE"."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 "HR_ARCHIVE"."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_INSERT_ARCHIVE_LOG_MAIN
--------------------------------------------------------
set define off;
CREATE OR REPLACE PROCEDURE "HR_ARCHIVE"."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_INSERT_ARCHIVE_PK_DATA
--------------------------------------------------------
set define off;
CREATE OR REPLACE PROCEDURE "HR_ARCHIVE"."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_INSERT_REPL_DATA
--------------------------------------------------------
set define off;
CREATE OR REPLACE PROCEDURE "HR_ARCHIVE"."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_INS_PK_DTLS
--------------------------------------------------------
set define off;
CREATE OR REPLACE PROCEDURE "HR_ARCHIVE"."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_INS_REPL_DTLS
--------------------------------------------------------
set define off;
CREATE OR REPLACE PROCEDURE "HR_ARCHIVE"."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_REPL_TABLE_DATA
--------------------------------------------------------
set define off;
CREATE OR REPLACE PROCEDURE "HR_ARCHIVE"."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_REPL_TABLE_INSRT
--------------------------------------------------------
set define off;
CREATE OR REPLACE PROCEDURE "HR_ARCHIVE"."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_UPD_ARCHIVE_BATCH_STATUS
--------------------------------------------------------
set define off;
CREATE OR REPLACE PROCEDURE "HR_ARCHIVE"."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_UPD_ARCHIVE_COUNT_N_STATUS
--------------------------------------------------------
set define off;
CREATE OR REPLACE PROCEDURE "HR_ARCHIVE"."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 RUN_ARCHIVE_MACHINE
--------------------------------------------------------
set define off;
CREATE OR REPLACE PROCEDURE "HR_ARCHIVE"."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;
/
--------------------------------------------------------
-- DDL for Procedure START_ARCHIVE_MACHINE
--------------------------------------------------------
set define off;
CREATE OR REPLACE PROCEDURE "HR_ARCHIVE"."START_ARCHIVE_MACHINE" 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_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 (10);
END LOOP; --}
END START_ARCHIVE_MACHINE;
/
The query checks if any records are there in the REPL_QUEUE table with a PNDNG status. For these records the replication process will copy data from the table mentioned in TXT_ARCHIVE_TABLE column and insert into the corresponding table in the replication schema.
Once the records are replicated in replication schema, the records from the queue table mentioned in TXT_DELETE_TABLE column are deleted.
| NUM_RULE_ID | TXT_RULE_DESC | TXT_RULE_QUERY | TXT_ACTIVE_FLG | TXT_ARCHIVE_TABLE | TXT_DELETE_TABLE | TXT_TBL_OWNER |
|---|---|---|---|---|---|---|
| 100002 | Replicate data from EMP_TEST table. | SELECT UNIQUE_ID FROM HR.REPL_QUEUE WHERE REPL_STAT = 'PNDNG' AND TBL_NAME = 'EMP_TEST' | N | EMP_TEST | REPL_QUEUE | HR |
Thought for the day
My child, perform your tasks with humility,
Then you will be loved by those whom GOD accepts.
The greater you are the more you must humble yourself;
So you will find favor in the sight of the Lord.
Sirach 3:17-18
No comments:
Post a Comment