MS MIG 전환솔루션
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
1 MS MIG 전환 솔루션[편집]
1.1 레파지토리 정보[편집]
1.1.1 MIG 환경설정[편집]
- TB_MIG_CONFIG : 데이터 입력, 집계함수 정보 입력 , 환경설정
- TB_MIG_CONFIG_DBOWNER : ( owner 별 db 접속정보/ asis-tobe)
- TB_MIG_CONFIG_USER
- TB_MIG_COD_CLS : 코드분류
- TB_MIG_COD : 코드 , CD008 : 집계함수 항목추가
- TB_MIG_COL_DIC_ASIS : ASIS 컬럼 정보입력 ( asis 딕셔너리)
- TB_MIG_COL_DIC_TOBE : TOBE 컬럼 정보입력 (tobe 딕셔너리) , 신규테이블 추가시
1.1.2 전환 매핑 관리[편집]
- 테이블 매핑
- TB_MIG_MAP_TBL_USER -- 전환 설계자(담당자) 테이블 매핑시 TB_MIG_MAP_TBL_SCDUL 과 같이 입력
- TB_MIG_MAP_TBL -- 테이블 전환방식/ 정보 저장/ ... 중요...
- TB_MIG_MAP_TBL_HST : 이력
- TB_MIG_MAP_TBL_SCDUL -- 테이블별 일정관리
- TB_MIG_MAP_TBL_SUBSET -- 테이블 매핑 설계
- TB_MIG_MAP_TBL_SUBSET_HST : 테이블 매핑 설계 이력
- TB_MIG_MAP_TBL_PGM -- 전환프로그램 컴파일 이력
- TB_MIG_MAP_TBL_ASIS -- ASIS 테이블 전환방식/ 정보 저장/ ... 중요...
- 컬럼 매핑
- TB_MIG_MAP_COL : 컬럼매핑설계 , UV_MIG_TOBE_COL_MAP_DEF 관련뷰
- TB_MIG_MAP_COL_HST 컬럼매핑 설계 이력
- TB_MIG_MAP_COL_ASIS : ASIS 매핑 안되는경우 필요
- TB_MIG_MAP_COL_ASIS_HST
- TB_MIG_MAP_COL : 컬럼매핑설계 , UV_MIG_TOBE_COL_MAP_DEF 관련뷰
1.1.3 전환검증[편집]
- TB_MIG_VERIFY_AGG_FUNC -- 집계함수 검증 결과
- TB_MIG_VERIFY_ASIS_ROWS_COUNT -- 일괄검증 후 생성된 로우카운터를 가져온다.
- TB_MIG_VERIFY_TOBE_ROWS_COUNT -- TB_MIG_LOG에 ROW카운트 대체
- TB_MIG_VERIFY_BIZRULE - B/R 검증
- TB_MIG_VERIFY_BIZRULE_RSLT -- B/R 검증 실행 결과 수행이력
- TB_MIG_ASIS_LOAD_VERIFY_COUNT 검증 및 카운트
1.1.4 전환결과 리포트[편집]
- TB_MIG_LOG -- 중요 - 전환결과 로그(전환계/운영계) 임으로 타겟서버에 로그테이블/프로시져 이 필수 존재해야함
1.1.5 추가기능[편집]
- TB_MIG_MAP_TBL_ROWS_SPLIT -- 분리전환 미사용 .
- TB_MIG_CONFIG_SESSION - 불필요 /* 생성보류(미사용 추정) */
- TB_MIG_MAP_AUTOMAP_OBJ -- 미사용 (자동매핑기능) 막혔음.
- TB_MIG_CONFIG_LOAD_TARGET_DB - 불필요
- TB_MIG_VERIFY_TBL_COUNT -- 미사용
1.1.6 주요 VIEW[편집]
- 기능 뷰
- UV_MIG_SUBJECT_AREA
- UV_MIG_COL_DIC_TOBE
- UV_MIG_COL_DIC_ASIS
- UV_MIG_ASIS_COL_MAP_DEF ASIS 오너 추가시 수정해야함
- UV_MIG_TOBE_COL_MAP_DEF TOBE 오너 추가시 수정해야함
- UV_MIG_LOG -- 전환 개발 FAIL 내역
- UV_MIG_LOG_DASHBOARD -- 불필요 추정
- 참조용 뷰
- MIGCOMMON.ALL_TABLES
- MIGCOMMON.ALL_TAB_COMMENTS
- MIGCOMMON.ALL_VIEWS
- MIGCOMMON.ALL_OBJECTS
- MIGCOMMON.ALL_TAB_COLUMNS
- MIGCOMMON.ALL_TAB_COLS
- MIGCOMMON.ALL_CONS_COLUMNS
- MIGCOMMON.ALL_CONSTRAINTS
- MIGCOMMON.ALL_COL_COMMENTS
- DBA_TAB_PARTITIONS_ASIS -- 빈껍데기만 생성, AS-IS 파티션 있을시 변경할 것.
- UV_MIG_CONFIG_USER
- UV_MIG_ASIS_STATS_TOTAL
- UV_MIG_STATS_SCDUL_PLAN
- UV_MIG_MAP_TBL_STATS -- OWNER 확인할것.
- UV_MIG_STATS_SA_OWNER -- OWNER 확인할것.
- UV_MIG_STATS_SA_OWNER_TOT
- UV_MIG_VERIFY_ASIS_ROWS_COUNT
- 매핑용 뷰
- UV_MIG_ASIS_COD_CLS /* AS-IS 공통코드 그룹 */
- UV_MIG_DATE_CHK /* 날짜 체크 */
- UV_MIG_FULL_DATE_CHK
- UV_MIG_FULL_TIME_CHK
- UV_MIG_VERIFY_STAT
- UV_MIG_TOBE_COD : (중요)변경필요
- UV_MIG_ASIS_COD : (중요)변경필요
- 매핑 관련
- UV_MIG_MAP_COL
- UV_MIG_MAP_COL_HST
- UV_MIG_MAP_TBL_PGM
- UV_MIG_MAP_TBL_SUBSET_HST
- UV_MIG_MAP_TBL_HST
- UV_MIG_MAP_TBL_ASIS
- UV_MIG_MAP_TBL_BASE
- UV_MIG_MAP_TBL :(중요)
- UV_MIG_MAP_TBL_DEF : (중요)
- UV_MIG_MAP_TBL_SUBSET : (중요)
1.2 MIG 레파지토리 테이블/컬럼 정보 일괄 갱신 프로시져[편집]
CREATE OR REPLACE PROCEDURE MIGCOMMON.SP_TOBE_TAB_REFRESH
/* MIG_TOBE_TABLE 갱신 프로시져 BY CYKIM (2020/11/03)
* -- 0. 사용법
* EXEC SP_TOBE_TAB_REFRESH();
*
* -- 1.오너 전체 테이블 리플레쉬
* EXEC SP_TOBE_TAB_REFRESH(IN_OWNER=>'TBA', IN_TABLE_NAME=>NULL);
*
* -- 2.오너별, 개별테이블 리플레쉬
* EXEC SP_TOBE_TAB_REFRESH(IN_OWNER=>'오너명', IN_TABLE_NAME=>'TABLE명');
*
*/
(
IN_OWNER IN VARCHAR2 DEFAULT NULL,
IN_TABLE_NAME IN VARCHAR2 DEFAULT NULL
)
AUTHID CURRENT_USER
IS
CURSOR REFRESH_OBJECT IS
SELECT A.OWNER,A.TABLE_NAME
FROM ALL_TABLES A
WHERE A.OWNER = IN_OWNER
AND A.TABLE_NAME=IN_TABLE_NAME
;
V_D_SQL VARCHAR2(200);
V_SQL VARCHAR2(32767);
V_OWNER VARCHAR2(100);
V_TABLE_NAME VARCHAR2(100);
V_MSG long;
BEGIN
IF IN_OWNER IS NULL THEN
DBMS_OUTPUT.ENABLE(100);
DBMS_OUTPUT.PUT_LINE('usage: ');
DBMS_OUTPUT.PUT_LINE('SP_TOBE_TAB_REFRESH(IN_OWNER => '''' , IN_TABLE_NAME => ''''); ');
RETURN;
END IF;
DBMS_OUTPUT.ENABLE(32767);
IF V_TABLE_NAME IS NOT NULL THEN
FOR V_ROW IN REFRESH_OBJECT
LOOP
BEGIN
V_OWNER:= V_ROW.OWNER;
V_TABLE_NAME:= V_ROW.TABLE_NAME;
DBMS_OUTPUT.ENABLE(32767);
IF V_TABLE_NAME IS NOT NULL THEN
-- OWNER.개별 테이블 갱신 처리
V_D_SQL :='DELETE FROM MIGCOMMON.TB_MIG_COL_DIC_TOBE WHERE OWNER='''||V_ROW.OWNER||''' AND TABLE_NAME='''||V_ROW.TABLE_NAME||'''';
V_SQL :='INSERT INTO MIGCOMMON.TB_MIG_COL_DIC_TOBE (OWNER,TABLE_NAME,LOGI_NAME,COLUMN_ID,COLUMN_NAME,ATTR_NAME,DATA_TYPE,CONV_COL_LEN,DATA_LENGTH,DATA_PRECISION,DATA_SCALE,NULLABLE,KEY,COL_CRT_TYPE)
SELECT OWNER,TABLE_NAME,LOGI_NAME,COLUMN_ID,COLUMN_NAME,ATTR_NAME,DATA_TYPE,NULL AS CONV_COL_LEN,NULL AS DATA_LENGTH,NULL AS DATA_PRECISION,NULL AS DATA_SCALE,NULL AS NULLABLE,NULL AS KEY,''가상컬럼'' AS COL_CRT_TYPE
FROM TB_MIG_COL_DIC_TOBE_DUMMY
WHERE OWNER = '''||V_ROW.OWNER||'''
AND TABLE_NAME = '''||V_ROW.TABLE_NAME||'''
UNION ALL
SELECT TB.OWNER,
TB.TABLE_NAME,
SUBSTR (LOGI.COMMENTS, 1, 40) AS LOGI_NAME,
COL.COLUMN_ID,
COL.COLUMN_NAME,
SUBSTR (ATTR.COMMENTS, 1, 40) AS ATTR_NAME,
COL.DATA_TYPE,
CASE
WHEN COL.DATA_TYPE IN (''VARCHAR2'', ''CHAR'')
THEN
TO_CHAR (COL.DATA_LENGTH)
WHEN COL.DATA_TYPE IN (''NUMBER'')
AND COL.DATA_PRECISION IS NOT NULL
THEN
TO_CHAR (COL.DATA_PRECISION)
|| DECODE (COL.DATA_SCALE, ''0'', '''', '''','''' || COL.DATA_SCALE)
ELSE
''''
END AS CONV_COL_LEN,
COL.DATA_LENGTH,
COL.DATA_PRECISION,
COL.DATA_SCALE,
COL.NULLABLE,
CON.KEY,
''물리컬럼'' AS COL_CRT_TYPE
FROM MIGCOMMON.ALL_TABLES TB
INNER JOIN MIGCOMMON.ALL_TAB_COLUMNS COL
ON TB.OWNER = COL.OWNER AND TB.TABLE_NAME = COL.TABLE_NAME
LEFT OUTER JOIN
( SELECT CCL.OWNER,
CCL.TABLE_NAME,
COLUMN_NAME,
CASE
WHEN SUM (DECODE (CONSTRAINT_TYPE, ''P'', 1, 0)) > 0
AND SUM (DECODE (CONSTRAINT_TYPE, ''R'', 1, 0)) > 0
THEN
''PK,FK''
WHEN SUM (DECODE (CONSTRAINT_TYPE, ''P'', 1, 0)) > 0
THEN
''PK''
WHEN SUM (DECODE (CONSTRAINT_TYPE, ''R'', 1, 0)) > 0
THEN
''FK''
ELSE
''''
END AS KEY
FROM MIGCOMMON.ALL_CONS_COLUMNS CCL,
MIGCOMMON.ALL_CONSTRAINTS CNS
WHERE CCL.CONSTRAINT_NAME = CNS.CONSTRAINT_NAME
GROUP BY CCL.OWNER, CCL.TABLE_NAME, COLUMN_NAME) CON
ON TB.OWNER = CON.OWNER
AND TB.TABLE_NAME = CON.TABLE_NAME
AND COL.COLUMN_NAME = CON.COLUMN_NAME
LEFT OUTER JOIN MIGCOMMON.ALL_COL_COMMENTS ATTR
ON ATTR.OWNER = TB.OWNER
AND ATTR.TABLE_NAME = TB.TABLE_NAME
AND ATTR.COLUMN_NAME = COL.COLUMN_NAME
LEFT OUTER JOIN MIGCOMMON.ALL_TAB_COMMENTS LOGI
ON LOGI.OWNER = TB.OWNER AND LOGI.TABLE_NAME = TB.TABLE_NAME
WHERE (TB.OWNER IN
(SELECT DB_OWNER_NM
FROM (SELECT DB_OWNER_NM
FROM MIGCOMMON.TB_MIG_CONFIG_DBOWNER
WHERE DB_TARGET_CD = ''2''
UNION ALL
SELECT ''MIGCOMMON'' AS DB_OWNER_NM FROM DUAL
UNION ALL
SELECT ''MIGBASEDATA'' AS DB_OWNER_NM FROM DUAL)))
AND TB.OWNER = '''||V_ROW.OWNER||'''
AND TB.TABLE_NAME = '''||V_ROW.TABLE_NAME||'''';
-- DBMS_OUTPUT.PUT_LINE('DEL_SQL:'||V_D_SQL);
EXECUTE IMMEDIATE V_D_SQL;
-- DBMS_OUTPUT.PUT_LINE('INS_SQL:'||V_SQL);
EXECUTE IMMEDIATE V_SQL;
END IF;
EXCEPTION
WHEN OTHERS THEN
V_MSG := 'ERROR : ['|| to_char(SQLCODE) ||']'|| substr(SQLERRM,1,500);
DBMS_OUTPUT.PUT_LINE('ERR_SQL:'||V_SQL);
END;
END LOOP;
ELSE
-- 해당 오너의 전체 테이블 REFRESH
V_D_SQL :='DELETE FROM MIGCOMMON.TB_MIG_COL_DIC_TOBE WHERE OWNER='''||IN_OWNER||'''';
V_SQL :='INSERT INTO MIGCOMMON.TB_MIG_COL_DIC_TOBE (OWNER,TABLE_NAME,LOGI_NAME,COLUMN_ID,COLUMN_NAME,ATTR_NAME,DATA_TYPE,CONV_COL_LEN,DATA_LENGTH,DATA_PRECISION,DATA_SCALE,NULLABLE,KEY,COL_CRT_TYPE)
SELECT OWNER,TABLE_NAME,LOGI_NAME,COLUMN_ID,COLUMN_NAME,ATTR_NAME,DATA_TYPE,NULL AS CONV_COL_LEN,NULL AS DATA_LENGTH,NULL AS DATA_PRECISION,NULL AS DATA_SCALE,NULL AS NULLABLE,NULL AS KEY,''가상컬럼'' AS COL_CRT_TYPE
FROM TB_MIG_COL_DIC_TOBE_DUMMY
WHERE OWNER = '''||IN_OWNER||'''
UNION ALL
SELECT TB.OWNER,
TB.TABLE_NAME,
SUBSTR (LOGI.COMMENTS, 1, 40) AS LOGI_NAME,
COL.COLUMN_ID,
COL.COLUMN_NAME,
SUBSTR (ATTR.COMMENTS, 1, 40) AS ATTR_NAME,
COL.DATA_TYPE,
CASE
WHEN COL.DATA_TYPE IN (''VARCHAR2'', ''CHAR'')
THEN
TO_CHAR (COL.DATA_LENGTH)
WHEN COL.DATA_TYPE IN (''NUMBER'')
AND COL.DATA_PRECISION IS NOT NULL
THEN
TO_CHAR (COL.DATA_PRECISION)
|| DECODE (COL.DATA_SCALE, ''0'', '''', '''','''' || COL.DATA_SCALE)
ELSE
''''
END AS CONV_COL_LEN,
COL.DATA_LENGTH,
COL.DATA_PRECISION,
COL.DATA_SCALE,
COL.NULLABLE,
CON.KEY,
''물리컬럼'' AS COL_CRT_TYPE
FROM MIGCOMMON.ALL_TABLES TB
INNER JOIN MIGCOMMON.ALL_TAB_COLUMNS COL
ON TB.OWNER = COL.OWNER AND TB.TABLE_NAME = COL.TABLE_NAME
LEFT OUTER JOIN
( SELECT CCL.OWNER,
CCL.TABLE_NAME,
COLUMN_NAME,
CASE
WHEN SUM (DECODE (CONSTRAINT_TYPE, ''P'', 1, 0)) > 0
AND SUM (DECODE (CONSTRAINT_TYPE, ''R'', 1, 0)) > 0
THEN
''PK,FK''
WHEN SUM (DECODE (CONSTRAINT_TYPE, ''P'', 1, 0)) > 0
THEN
''PK''
WHEN SUM (DECODE (CONSTRAINT_TYPE, ''R'', 1, 0)) > 0
THEN
''FK''
ELSE
''''
END AS KEY
FROM MIGCOMMON.ALL_CONS_COLUMNS CCL,
MIGCOMMON.ALL_CONSTRAINTS CNS
WHERE CCL.CONSTRAINT_NAME = CNS.CONSTRAINT_NAME
GROUP BY CCL.OWNER, CCL.TABLE_NAME, COLUMN_NAME) CON
ON TB.OWNER = CON.OWNER
AND TB.TABLE_NAME = CON.TABLE_NAME
AND COL.COLUMN_NAME = CON.COLUMN_NAME
LEFT OUTER JOIN MIGCOMMON.ALL_COL_COMMENTS ATTR
ON ATTR.OWNER = TB.OWNER
AND ATTR.TABLE_NAME = TB.TABLE_NAME
AND ATTR.COLUMN_NAME = COL.COLUMN_NAME
LEFT OUTER JOIN MIGCOMMON.ALL_TAB_COMMENTS LOGI
ON LOGI.OWNER = TB.OWNER AND LOGI.TABLE_NAME = TB.TABLE_NAME
WHERE (TB.OWNER IN
(SELECT DB_OWNER_NM
FROM (SELECT DB_OWNER_NM
FROM MIGCOMMON.TB_MIG_CONFIG_DBOWNER
WHERE DB_TARGET_CD = ''2''
UNION ALL
SELECT ''MIGCOMMON'' AS DB_OWNER_NM FROM DUAL
UNION ALL
SELECT ''MIGBASEDATA'' AS DB_OWNER_NM FROM DUAL)))
AND TB.OWNER = '''||IN_OWNER||'''';
-- DBMS_OUTPUT.PUT_LINE('DEL_SQL:'||V_D_SQL);
EXECUTE IMMEDIATE V_D_SQL;
-- DBMS_OUTPUT.PUT_LINE('INS_SQL:'||V_SQL);
EXECUTE IMMEDIATE V_SQL;
-- LOG 기록
-- INSERT INTO ZB_MGR_TAB_REFRESH_LOG
-- (OWNER , TABLE_NAME)
-- VALUES (V_ROW.OWNER,V_ROW.TABLE_NAME);
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERR MESSAGE : ' || SQLERRM);
END;
/1.3 테이블명 변경에 따른 MIG 레파지토리 일괄변경 프로시져[편집]
CREATE OR REPLACE PROCEDURE MIGCOMMON.SP_TOBE_TAB_RENAME
/* MIG_TOBE_TABLE 갱신 프로시져 BY CYKIM (2020/11/03)
* -- 0. 사용법
* EXEC SP_TOBE_TAB_RENAME('오너명','OLD_TABLE명','NEW_TABLE명'));
*
* -- 1.오너, 테이블 RENAME
* EXEC SP_TOBE_TAB_RENAME(IN_OWNER=>'오너명', IN_OLD_TABLE=>'OLD_TABLE명', IN_NEW_TABLE=>'NEW_TABLE명');
*
*/
(
IN_OWNER IN VARCHAR2 DEFAULT NULL,
IN_OLD_TABLE IN VARCHAR2 DEFAULT NULL,
IN_NEW_TABLE IN VARCHAR2 DEFAULT NULL
)
--AUTHID CURRENT_USER
IS
PRAGMA AUTONOMOUS_TRANSACTION;
-- CURSOR RENAME_OBJECT IS
-- SELECT A.OWNER,A.TABLE_NAME
-- FROM ALL_TABLES A
-- WHERE A.OWNER = IN_OWNER
-- AND A.TABLE_NAME=IN_OLD_TABLE
-- ;
V_1_SQL VARCHAR2(2000);
V_2_SQL VARCHAR2(2000);
V_FK1_SQL1 VARCHAR2(2000);
V_FK1_SQL2 VARCHAR2(2000);
V_FK1_SQL3 VARCHAR2(2000);
V_FK2_SQL1 VARCHAR2(2000);
V_FK2_SQL2 VARCHAR2(2000);
V_FK2_SQL3 VARCHAR2(2000);
-- V_OWNER VARCHAR2(100);
-- V_TABLE_NAME VARCHAR2(100);
V_MSG long;
BEGIN
IF IN_OWNER IS NULL THEN
DBMS_OUTPUT.ENABLE(200);
DBMS_OUTPUT.PUT_LINE('usage: ');
DBMS_OUTPUT.PUT_LINE('SP_TOBE_TAB_RENAME(IN_OWNER => '''' , IN_OLD_TABLE => '''', IN_NEW_TABLE => ''''); ');
RETURN;
END IF;
DBMS_OUTPUT.ENABLE(32767);
BEGIN
-- V_OWNER:= IN_OWNER;
-- V_TABLE_NAME:= IN_OLD_TABLE;
-- V_TABLE_NAME2:= IN_NEW_TABLE;
DBMS_OUTPUT.ENABLE(32767);
IF IN_NEW_TABLE IS NOT NULL THEN
-- 1-1.F/K 비활성화
V_FK1_SQL1 := 'ALTER TABLE TB_MIG_MAP_TBL_SUBSET DISABLE CONSTRAINTS FK_MIG_MAP_TBL_SUBSET_01';
V_FK1_SQL2 := 'ALTER TABLE TB_MIG_MAP_TBL_USER DISABLE CONSTRAINTS FK_TB_MIG_MAP_TBL_USER_01';
V_FK1_SQL3 := 'ALTER TABLE TB_MIG_MAP_TBL_SCDUL DISABLE CONSTRAINTS FK_TB_MIG_MAP_TBL_SCDUL_01';
-- 1-2.F/K 활성화
V_FK2_SQL1 := 'ALTER TABLE TB_MIG_MAP_TBL_SUBSET ENABLE CONSTRAINTS FK_MIG_MAP_TBL_SUBSET_01';
V_FK2_SQL2 := 'ALTER TABLE TB_MIG_MAP_TBL_USER ENABLE CONSTRAINTS FK_TB_MIG_MAP_TBL_USER_01';
V_FK2_SQL3 := 'ALTER TABLE TB_MIG_MAP_TBL_SCDUL ENABLE CONSTRAINTS FK_TB_MIG_MAP_TBL_SCDUL_01';
-- DBMS_OUTPUT.PUT_LINE('V_FK1_SQL:'||V_FK1_SQL);
EXECUTE IMMEDIATE V_FK1_SQL1;
EXECUTE IMMEDIATE V_FK1_SQL2;
EXECUTE IMMEDIATE V_FK1_SQL3;
-- 매핑테이블 UPDATE 처리
UPDATE TB_MIG_MAP_TBL A SET TOBE_TBL_NM=IN_NEW_TABLE WHERE TOBE_TBL_NM=IN_OLD_TABLE;
-- 매핑테이블 이력 UPDATE 처리
UPDATE TB_MIG_MAP_TBL_HST A SET TOBE_TBL_NM=IN_NEW_TABLE WHERE TOBE_TBL_NM=IN_OLD_TABLE;
-- 매핑컬럼 UPDATE 처리
UPDATE TB_MIG_MAP_COL A SET A.TOBE_TBL_NM=IN_NEW_TABLE WHERE A.TOBE_TBL_NM=IN_OLD_TABLE;
-- 매핑테이블 이력 UPDATE 처리
UPDATE TB_MIG_MAP_COL_HST A SET A.TOBE_TBL_NM=IN_NEW_TABLE WHERE A.TOBE_TBL_NM=IN_OLD_TABLE;
-- 매핑테이블 상세 매핑정보 UPDATE 처리
UPDATE TB_MIG_MAP_TBL_SUBSET A SET TOBE_TBL_NM=IN_NEW_TABLE WHERE TOBE_TBL_NM=IN_OLD_TABLE;
-- 매핑테이블 상세 매핑정보 이력 UPDATE 처리
UPDATE TB_MIG_MAP_TBL_SUBSET_HST A SET TOBE_TBL_NM=IN_NEW_TABLE WHERE TOBE_TBL_NM=IN_OLD_TABLE;
-- 매핑 테이블 담당자 UPDATE
UPDATE TB_MIG_MAP_TBL_USER A SET TOBE_TBL_NM=IN_NEW_TABLE WHERE TOBE_TBL_NM=IN_OLD_TABLE;
-- 매핑 테이블 담당자 스케줄 UPDATE
UPDATE TB_MIG_MAP_TBL_SCDUL A SET TOBE_TBL_NM=IN_NEW_TABLE WHERE TOBE_TBL_NM=IN_OLD_TABLE;
-- F/K ENABLE
-- DBMS_OUTPUT.PUT_LINE('INS_SQL:'||V_FK1_SQL);
COMMIT;
EXECUTE IMMEDIATE V_FK2_SQL1;
EXECUTE IMMEDIATE V_FK2_SQL2;
EXECUTE IMMEDIATE V_FK2_SQL3;
END IF;
EXCEPTION
WHEN OTHERS THEN
V_MSG := 'ERROR : ['|| to_char(SQLCODE) ||']'|| substr(SQLERRM,1,500);
-- DBMS_OUTPUT.PUT_LINE('ERR_SQL:'||V_SQL);
COMMIT;
END;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERR MESSAGE : ' || SQLERRM);
END;
/