안녕하세요.

운영DB에서 사용중인 기준 정보 테이블의 일 변경점 데이터만 추출하여 재연DB의 Merge용 테이블에 옮겨 놓고

Merge용 Table 에서 운영DB와 동일한 스키마의 재연 테이블에 데이터를 밀어넣는 프로시저를 다음과 같이 작성하였습니다.

그런데, 실제로 실행해보면 컴파일은 문제없으나 정상적으로 동작 완료가 되지 않습니다. 별도 로깅 테이블을 구성하거나 동작 중 오류발생 메세지가 없어 어떤 이유인지 찾기가 어렵습니다.

해서 하기 코드에서 어떤 문제가 있는지 도움 요청드립니다.

create or replace PROCEDURE SP_TN_CM_MERGE AS 

BEGIN

-- Merge 전 이상 데이터 삭제
DELETE tn_cm_eqpinfo_daily_merge WHERE EQP_ID IS NULL;
DELETE tn_cm_eqp_param_daily_merge where eqp_id is null or param_id is null or addr_id is null;
DELETE tn_cm_plc_addr_daily_merge where addr_id is null or plc_word_addr is null;
DELETE tn_cm_block_set_daily_merge where eqp_id is null;
DELETE tn_cm_eqp_cmnctn_daily_merge where eqp_id is null;
DELETE tn_cm_paraminfo_daily_merge where param_id is null;
commit;
DBMS_OUTPUT.PUT_LINE('이상 데이터 삭제 완료');

-- 중복 데이터 삭제
DELETE tn_cm_eqpinfo a WHERE EXISTS (SELECT 1 FROM TN_CM_EQPINFO_DAILY_MERGE b WHERE a.eqp_id = b.eqp_id);
DELETE TN_CM_EQP_PARAM a WHERE EXISTS (SELECT 1 FROM tn_cm_eqp_PARAM_daily_merge b WHERE a.eqp_id = b.eqp_id and a.param_id = b.param_id);
DELETE tn_cm_plc_addr a WHERE EXISTS (SELECT 1 FROM tn_cm_plc_addr_DAILY_MERGE b WHERE a.ADDR_ID = b.ADDR_ID);
DELETE tn_cm_block_set a WHERE EXISTS (SELECT 1 FROM tn_cm_block_set_DAILY_MERGE b WHERE a.EQP_ID = b.EQP_ID);
DELETE tn_cm_eqp_cmnctn a WHERE EXISTS (SELECT 1 FROM tn_cm_eqp_cmnctn_daily_merge b WHERE a.EQP_ID = b.EQP_ID);
DELETE tn_cm_paraminfo a WHERE EXISTS (SELECT 1 FROM tn_cm_paraminfo_DAILY_MERGE b WHERE a.PARAM_ID = B.PARAM_ID);
commit;
DBMS_OUTPUT.PUT_LINE('기준 정보 테이블 중복 삭제 완료');

INSERT /*+ append */ INTO tn_cm_eqpinfo SELECT * FROM TN_CM_EQPINFO_DAILY_MERGE;
INSERT /*+ append */ INTO TN_CM_EQP_PARAM SELECT * FROM tn_cm_eqp_PARAM_daily_merge ;
INSERT /*+ append */ INTO tn_cm_plc_addr SELECT * FROM tn_cm_plc_addr_DAILY_MERGE;
INSERT /*+ append */ INTO tn_cm_block_set SELECT * FROM tn_cm_block_set_DAILY_MERGE;
INSERT /*+ append */ INTO tn_cm_eqp_cmnctn SELECT * FROM tn_cm_eqp_cmnctn_DAILY_MERGE;
INSERT /*+ append */ INTO tn_cm_paraminfo SELECT * FROM tn_cm_paraminfo_DAILY_MERGE;
commit;
DBMS_OUTPUT.PUT_LINE('변경점 데이터 인서트 완료');

execute immediate 'TRUNCATE TABLE TN_CM_EQPINFO_DAILY_MERGE';
execute immediate 'TRUNCATE TABLE tn_cm_eqp_PARAM_daily_merge';
execute immediate 'TRUNCATE TABLE tn_cm_plc_addr_DAILY_MERGE';
execute immediate 'TRUNCATE TABLE tn_cm_block_set_DAILY_MERGE';
execute immediate 'TRUNCATE TABLE tn_cm_eqp_cmnctn_DAILY_MERGE';
execute immediate 'TRUNCATE TABLE tn_cm_paraminfo_DAILY_MERGE';


END;