DB CAFE
메뉴
둘러보기
대문
최근 바뀜
임의 문서로
도움말
검색
여기를 가리키는 문서
가리키는 글의 최근 바뀜
특수 문서 목록
문서 정보
최근 바뀜
도움말
로그인
행위
문서
토론
원본 보기
역사
MS MIG 전환솔루션 문서 원본 보기
←
MS MIG 전환솔루션
문서 편집 권한이 없습니다. 다음 이유를 확인해주세요:
요청한 명령은 다음 권한을 가진 사용자에게 제한됩니다:
사용자
.
이 문서는 편집하거나 다른 명령을 할 수 없도록 보호되어 있습니다.
문서의 원본을 보거나 복사할 수 있습니다.
== MS MIG 전환 솔루션 == === 레파지토리 정보 === ==== 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 딕셔너리) , 신규테이블 추가시 ==== 전환 매핑 관리 ==== # 테이블 매핑 ## 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_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 검증 및 카운트 ==== 전환결과 리포트 ==== # TB_MIG_LOG -- 중요 - 전환결과 로그(전환계/운영계) 임으로 타겟서버에 로그테이블/프로시져 이 필수 존재해야함 ==== 추가기능 ==== # 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 -- 미사용 ==== 주요 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 : (중요) === MIG 레파지토리 테이블/컬럼 정보 일괄 갱신 프로시져 === <source lang=sql> 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; / </source> === 테이블명 변경에 따른 MIG 레파지토리 일괄변경 프로시져 === <source lang=sql> 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; / </source>
MS MIG 전환솔루션
문서로 돌아갑니다.