LOADER (매일아침 9시에 코드삽입)

개요 : 복제서버에서 시뮬레이션을 위해 매일 아침 9시에 코드정보 삽입하기

 

1. ASIS코드 매핑결과 적재테이블 생성

DROP TABLE EN_ASIS_CD_DATA;

CREATE TABLE EN_ASIS_CD_DATA(
"순번"			NUMBER,
"주제영역FULL명"	VARCHAR2(1000),	
"주제영역명"		VARCHAR2(1000),
"모델명"			VARCHAR2(1000),
"엔터티명"			VARCHAR2(1000),
"테이블명"			VARCHAR2(1000),
"속성명"			VARCHAR2(1000),
"컬럼명"			VARCHAR2(1000),
"테이블명컬럼명"		VARCHAR2(1000),
"데이터타입"		VARCHAR2(1000),
"데이터길이"		VARCHAR2(1000),
"데이터소수점자리"	VARCHAR2(1000),	
"PK여부"			VARCHAR2(1000),
"NULL여부"		VARCHAR2(1000),
"코드구분"			VARCHAR2(1000),
"공통코드엔터티명"	VARCHAR2(1000),	
"코드추출소스"		VARCHAR2(1000),
"참조코드테이블명"	VARCHAR2(4000),
"참조코드컬럼명"		VARCHAR2(1000),
"참조코드ID"		VARCHAR2(1000),
"코드오류존재여부"  	VARCHAR2(1000)
)
NOLOGGING;

 

 

 

2. 엑셀데이터 테이블에 넣어서 script파일 만들기

-- 누락 확인
create table tmp_dual
as 
select rownum no from dual connect by level <= 27206;
select no 
  from tmp_dual a
 where not exists (select /*+anti_sj*/ 순번 
                     from EN_ASIS_CD_DATA x
                    where x.순번 = a.no)
;

-- chr(10)이 행 두개로 분리되는 현상 제거, 스페이스 한개이상 -> 한개로 변환
select repexp_replace(replace(참조코드테이블명,chr(10),'##enter##'),'( )+',chr(32)) 
  from EN_ASIS_CD_DATA 
 where instr(참조코드테이블명,chr(10))>0; 

-- 아래 sql 실행결과를 code_script2.sql로 저장.
select 
순번
||'||||'||NVL(TRIM(주제영역FULL명),'NULL')
||'||||'||NVL(TRIM(주제영역명),'NULL')
||'||||'||NVL(TRIM(모델명),'NULL')
||'||||'||NVL(TRIM(엔터티명),'NULL')
||'||||'||NVL(TRIM(테이블명),'NULL')
||'||||'||NVL(TRIM(속성명),'NULL')
||'||||'||NVL(TRIM(컬럼명),'NULL')
||'||||'||NVL(TRIM(테이블명컬럼명),'NULL')
||'||||'||NVL(TRIM(데이터타입),'NULL')
||'||||'||NVL(TRIM(데이터길이),'NULL')
||'||||'||NVL(TRIM(데이터소수점자리),'NULL')
||'||||'||NVL(TRIM(PK여부),'NULL')
||'||||'||NVL(TRIM(NULL여부),'NULL')
||'||||'||NVL(TRIM(코드구분),'NULL') 
||'||||'||NVL(TRIM(공통코드엔터티명),'NULL') 
||'||||'||NVL(TRIM(코드추출소스),'NULL') 
||'||||'||NVL(regexp_replace(replace(TRIM(참조코드테이블명),chr(10),'##enter##'),'( )+',chr(32)),'NULL') 
||'||||'||NVL(TRIM(참조코드컬럼명),'NULL') 
||'||||'||NVL(TRIM(참조코드ID),'NULL') script
from EN_ASIS_CD_DATA
order by 순번
;

 

 

3. code_script2.sql파일 loader로 삽입

load data
infile 'D:/code/code_script2.sql'
append
into table SCOTT.EN_ASIS_CD_DATA
fields terminated by '||||'
trailing nullcols
(
순번
,주제영역FULL명
,주제영역명
,모델명
,엔터티명
,테이블명
,속성명
,컬럼명
,테이블명컬럼명
,데이터타입
,데이터길이
,데이터소수점자리
,PK여부
,NULL여부
,코드구분
,공통코드엔터티명 char(1000)
,코드추출소스 char(1000)
,참조코드테이블명 char(1000)
,참조코드컬럼명 char(1000)
,참조코드ID char(1000)
,코드오류존재여부 char(1000)
)

 

 

4. ##enter-> chr(10) NULL문자-->NULL값으로 치환 후 인덱스 생성

update
(
select 
순번,decode(순번,'NULL','','순번') as upd_순번
,주제영역FULL명,decode(주제영역FULL명,'NULL','',주제영역FULL명) as upd_주제영역FULL명
,주제영역명,decode(주제영역명,'NULL','',주제영역명) as upd_주제영역명
,모델명,decode(모델명,'NULL','',모델명) as upd_모델명
,엔터티명,decode(엔터티명,'NULL','',엔터티명) as upd_엔터티명
,테이블명,decode(테이블명,'NULL','',테이블명) as upd_테이블명
,속성명,decode(속성명,'NULL','',속성명) as upd_속성명
,컬럼명,decode(컬럼명,'NULL','',컬럼명) as upd_컬럼명
,테이블명컬럼명,decode(테이블명컬럼명,'NULL','',테이블명컬럼명) as upd_테이블명컬럼명
,데이터타입,decode(데이터타입,'NULL','',데이터타입) as upd_데이터타입
,데이터길이,decode(데이터길이,'NULL','',데이터길이) as upd_데이터길이
,데이터소수점자리,decode(데이터소수점자리,'NULL','',데이터소수점자리) as upd_데이터소수점자리
,PK여부,decode(PK여부,'NULL','',PK여부) as upd_PK여부
,NULL여부,decode(NULL여부,'NULL','',NULL여부) as upd_NULL여부
,코드구분,decode(코드구분,'NULL','',코드구분) as upd_코드구분
,공통코드엔터티명,decode(공통코드엔터티명,'NULL','',공통코드엔터티명) as upd_공통코드엔터티명
,코드추출소스,decode(코드추출소스,'NULL','',코드추출소스) as upd_코드추출소스
,참조코드테이블명,decode(참조코드테이블명,'NULL','',참조코드테이블명) as upd_참조코드테이블명
,참조코드컬럼명,decode(참조코드컬럼명,'NULL','',참조코드컬럼명) as upd_참조코드컬럼명
,참조코드ID,decode(참조코드ID,'NULL','',참조코드ID) as upd_참조코드ID
,코드오류존재여부,decode(코드오류존재여부,'NULL','',코드오류존재여부) as upd_코드오류존재여부
from EN_ASIS_CD_DATA
)
set
순번=upd_순번
,주제영역FULL명=upd_주제영역FULL명
,주제영역명=upd_주제영역명
,모델명=upd_모델명
,엔터티명=upd_엔터티명
,테이블명=upd_테이블명
,속성명=upd_속성명
,컬럼명=upd_컬럼명
,테이블명컬럼명=upd_테이블명컬럼명
,데이터타입=upd_데이터타입
,데이터길이=upd_데이터길이
,데이터소수점자리=upd_데이터소수점자리
,PK여부=upd_PK여부
,NULL여부=upd_NULL여부
,코드구분=upd_코드구분
,공통코드엔터티명=upd_공통코드엔터티명
,코드추출소스=upd_코드추출소스
,참조코드테이블명=upd_참조코드테이블명
,참조코드컬럼명=upd_참조코드컬럼명
,참조코드ID=upd_참조코드ID
,코드오류존재여부=upd_코드오류존재여부
;

update EN_ASIS_CD_DATA
set 참조코드테이블명 = replace(참조코드테이블명,'##enter##',chr(10))
;

CREATE INDEX EN_ASIS_CD_DATA_IDX01 ON EN_ASIS_CD_DATA(테이블명,컬럼명) NOLOGGING;

 

 

 

5.시뮬레이션 SQL

SELECT 'SELECT ' AS SQLSCRIPT
FROM DUAL
UNION ALL
SELECT SQLSCRIPT
FROM(
SELECT CASE WHEN TO_NUMBER(A.COLUMN_ID) = 1 THEN '' ELSE ',' END
  ||RPAD(A.COLUMN_NAME,34)||' AS '||RPAD(SUBSTRB('"'||NVL(B.COMMENTS,B.COLUMN_NAME)||'"',1,32),32)||'--'||A.DATA_TYPE||'('||A.DATA_LENGTH||')'||'--'||I.INDEX_NAME
  ||CASE WHEN D.참조코드테이블명 LIKE '%APPS.VW_CMMN_CD_NM_01%' THEN CHR(13)||',(SELECT CODE.CMMN_CD_NM FROM APPS.VW_CMMN_CD_NM_01 CODE '||CHR(13)||'WHERE CODE.CMMN_KD_CD='''||참조코드ID||''' AND CODE.CMMN_CD=A.'||A.COLUMN_NAME||') AS '||SUBSTRB('''[공통'||참조코드ID||']'||B.COMMENTS,1,28)||'"'
         WHEN D.참조코드컬럼명 LIKE '%SEGMENT1%' THEN CHR(13)||',(SELECT CODE.DESCRIPTION FROM MTL_SYSTEM_ITEMS_B CODE '||CHR(13)||'WHERE CODE.SEGMENT1=A.'||A.COLUMN_NAME||' AND ROWNUM<=1) AS '||SUBSTRB('"[상품]'||B.COMMENTS,1,28)||'"'
         WHEN D.참조코드테이블명 IS NOT NULL THEN 코드구분||'-'||참조코드테이블명||'-'||참조코드컬럼명 END AS SQLSCRIPT
FROM ALL_TAB_COLUMNS A
,ALL_COL_COMMENTS B
,ALL_TAB_COLS C
,EN_ASIS_CD_DATA D
,ALL_IND_COLUMNS I
WHERE A.OWNER = B.OWNER
AND B.OWNER = C.OWNER
AND A.TABLE_NAME = B.TABLE_NAME
AND A.COLUMN_NAME = B.COLUMN_NAME
AND A.TABLE_NAME = C.TABLE_NAME
AND A.COLUMN_NAME = C.COLUMN_NAME
AND C.VIRTUAL_COLUMN != 'YES'
AND A.TABLE_NAME = D.테이블명(+)
AND A.COLUMN_NAME = D.컬럼명(+)
AND A.OWNER = :L_OWNER
AND A.TABLE_NAME = :L_TABLE_NM
AND A.OWNER = I.TABLE_OWNER(+)
AND A.TABLE_NAME = I.TABLE_NAME(+)
AND A.COLUMN_NAME = I.COLUMN_NAME(+)
AND REGEXP_LIKE(I.INDEX_NAME(+),'(^PK_)|)_PK$)')
ORDER BY A.COLUMN_ID
)
UNION ALL
SELECT 'FROM '||:L_OWNER||'.'||:L_TABLE_NM||' A
WHERE 1=1
;'
FROM DUAL;