--------------------------------------------------------
-- 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_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_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_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
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;
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_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_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;
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) := PKG_ARCHIVE.MAIN_SCHEMA;
PNDNG CONSTANT VARCHAR2(10) := PKG_ARCHIVE.ARCHIVE_PENDING;
BEGIN
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;
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_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
PR_INS_PK_DTLS(p_archive_batch_id, p_archive_log_hdr_id, p_tbl_nm, 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_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_cd 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);
CURSOR C1
IS
SELECT TXT_RULE_QUERY, TXT_PRIMARY_TABLE
FROM ARCHIVE_RULES
WHERE TXT_RULE_CD = p_rule_cd;
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;
--Insert all records into a temp table for restartibility logic
FOR rec IN C1
LOOP
v_sql := rec.TXT_RULE_QUERY;
OPEN CUR FOR v_sql;
LOOP
NULL;
/* 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
PR_INSERT_ARCHIVE_PK_DATA (p_archive_batch_id, p_archive_log_hdr_id,rec.TXT_PRIMARY_TABLE, l_ref_rowid_arr, p_error_code);
IF p_error_code != PKG_ARCHIVE.SUCCESS
THEN
v_table_name := rec.TXT_PRIMARY_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 LOOP;
--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_INSRT
--------------------------------------------------------
set define off;
create or replace PROCEDURE PR_ARCHIVE_TABLE_INSRT (
p_archive_log_header_id NUMBER,
p_table_name 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) := PKG_ARCHIVE.MAIN_SCHEMA;
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
v_sql := v_sql || ' AND TRUNC(' || 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_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_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_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_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;
/* 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;
/
--------------------------------------------------------
-- 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;
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;
-- 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;
/
--------------------------------------------------------
-- 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_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_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 || '''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_cd IN VARCHAR2,
p_archive_table_list 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_rec_key_columns TYP_REC_TABLE_KEY_COLS := TYP_REC_TABLE_KEY_COLS(NULL,NULL,NULL,NULL,NULL,NULL);
v_query_str VARCHAR2(4000);
BEGIN
p_error_code := PKG_ARCHIVE.SUCCESS;
v_output_arr := FXN_STR_TO_COLUMNS(p_archive_table_list,',');
FOR i IN v_output_arr.FIRST .. v_output_arr.LAST
LOOP --{
PR_GENERATE_ALH_QUERY_STR(p_archive_batch_id, p_rule_cd, v_output_arr(i), 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;
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_LOG_HEADER;
/
--------------------------------------------------------
-- 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
);
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_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;
CURSOR C2
IS
SELECT TXT_RULE_CD,
TXT_ARCHIVE_TABLE_LIST
FROM ARCHIVE_RULES
WHERE TXT_ACTIVE_FLG = 'Y';
v_archive_batch_id NUMBER;
BEGIN
DBMS_OUTPUT.DISABLE;
v_error_code := PKG_ARCHIVE.SUCCESS;
-- 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 rec2 IN C2
LOOP --{
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;
PR_INSERT_ARCHIVE_LOG_HEADER(v_archive_batch_id, rec2.TXT_RULE_CD, rec2.TXT_ARCHIVE_TABLE_LIST,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, rec2.TXT_RULE_CD, 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;
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, 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;
/
God's Word for the day
The preservation of FriendshipOne who pricks the eye brings tears,
and one who pricks the heart makes clear its feelings.
One who throws a stone at birds scares them away,
and one who reviles a friend destroys a friendship.
Sirach 22:19-20
Gospel teachings of Jesus
Treasures new and old
"Have you understood all this?
They answered "Yes". And he said to them,
"Therefore every scribe who has been trained for the kingdom of heaven
Is like the master of a household who brings out of his treasure
what is new and what is old."
When Jesus had finished these parables, he left that place.
Mathew 13:51-53
No comments:
Post a Comment