행위

메타웍스(MetaWork) 엔터티

DB CAFE

Dbcafe (토론 | 기여)님의 2020년 9월 16일 (수) 23:17 판
thumb_up 추천메뉴 바로가기


1 표준관리 S/W 메타웍스 엔터티[편집]

메타웍스는 데이터표준을 관리하기 위한 툴로써 SI분야 프로젝트등에서 DB표준 작성시 명확하고 편리하게 표준을 관리하는 툴입니다.

  • 데이터표준(도메인,표준단어,표준용어) 생성 및 검사 기능
  • 공통코드 추가/관리 기능
  • 표준신청 및 승인 기능
  • ERWIN 연결 및 ERD파일 표준검사 기능


1.1 표준도메인[편집]

DROP TABLE ERD_ADM.DM_STD_DOMAIN CASCADE CONSTRAINTS;

CREATE TABLE ERD_ADM.DM_STD_DOMAIN
(
  I_NAME        VARCHAR2(100 BYTE)              NOT NULL,
  D_NAME        VARCHAR2(100 BYTE)              NOT NULL,
  D_CLASS       VARCHAR2(100 BYTE)              NOT NULL,
  DFLT_YN       VARCHAR2(1 BYTE),
  D_ENAME       VARCHAR2(100 BYTE),
  D_DTYPE       VARCHAR2(50 BYTE),
  D_DLEN        VARCHAR2(10 BYTE),
  D_DSCALE      NUMBER(3),
  D_LDTYPE_LEN  VARCHAR2(100 BYTE),
  D_PDTYPE_LEN  VARCHAR2(100 BYTE),
  D_VALID       VARCHAR2(1000 BYTE),
  D_DESC        VARCHAR2(2000 BYTE)             NOT NULL,
  D_PARSED      VARCHAR2(200 BYTE),
  REGTR         VARCHAR2(30 BYTE)               NOT NULL,
  REGDT         DATE                            NOT NULL,
  MODFR         VARCHAR2(30 BYTE)               NOT NULL,
  MODDT         DATE                            NOT NULL,
  CD_TYPE       VARCHAR2(1 BYTE),
  CHK_RMK       VARCHAR2(1000 BYTE)
)
;

COMMENT ON TABLE ERD_ADM.DM_STD_DOMAIN IS '표준도메인';

COMMENT ON COLUMN ERD_ADM.DM_STD_DOMAIN.I_NAME IS '인포타입';

COMMENT ON COLUMN ERD_ADM.DM_STD_DOMAIN.D_NAME IS '도메인';

COMMENT ON COLUMN ERD_ADM.DM_STD_DOMAIN.D_CLASS IS '도메인유형';

COMMENT ON COLUMN ERD_ADM.DM_STD_DOMAIN.DFLT_YN IS '디폴트여부';

COMMENT ON COLUMN ERD_ADM.DM_STD_DOMAIN.D_ENAME IS '도메인물리명';

COMMENT ON COLUMN ERD_ADM.DM_STD_DOMAIN.D_DTYPE IS '데이터타입';

COMMENT ON COLUMN ERD_ADM.DM_STD_DOMAIN.D_DLEN IS '데이터길이';

COMMENT ON COLUMN ERD_ADM.DM_STD_DOMAIN.D_DSCALE IS '소수점자리수';

COMMENT ON COLUMN ERD_ADM.DM_STD_DOMAIN.D_LDTYPE_LEN IS '논리데이터타입';

COMMENT ON COLUMN ERD_ADM.DM_STD_DOMAIN.D_PDTYPE_LEN IS '물리데이터타입';

COMMENT ON COLUMN ERD_ADM.DM_STD_DOMAIN.D_VALID IS '유효값';

COMMENT ON COLUMN ERD_ADM.DM_STD_DOMAIN.D_DESC IS '도메인정의';

COMMENT ON COLUMN ERD_ADM.DM_STD_DOMAIN.D_PARSED IS '단어분해';

COMMENT ON COLUMN ERD_ADM.DM_STD_DOMAIN.REGTR IS '등록자';

COMMENT ON COLUMN ERD_ADM.DM_STD_DOMAIN.REGDT IS '등록일시';

COMMENT ON COLUMN ERD_ADM.DM_STD_DOMAIN.MODFR IS '수정자';

COMMENT ON COLUMN ERD_ADM.DM_STD_DOMAIN.MODDT IS '수정일시';

COMMENT ON COLUMN ERD_ADM.DM_STD_DOMAIN.CD_TYPE IS '코드타입(1:공통코드, 2:개별코드, 3:외부코드, 9:기타-관리안함)';
CREATE UNIQUE INDEX ERD_ADM.DM_STD_DOMAIN_PK ON ERD_ADM.DM_STD_DOMAIN
(I_NAME)
;
CREATE UNIQUE INDEX ERD_ADM.DM_STD_DOMAIN_UK1 ON ERD_ADM.DM_STD_DOMAIN
(DECODE("DFLT_YN","D_NAME",NULL))
;
CREATE OR REPLACE TRIGGER ERD_ADM.TR_DM_STD_DOMAIN
  AFTER UPDATE OR DELETE ON ERD_ADM.DM_STD_DOMAIN
FOR EACH ROW
DECLARE
  V_DT     DATE DEFAULT SYSDATE;
  V_TYPE   VARCHAR(1);
BEGIN
    IF UPDATING THEN 
       V_TYPE := 'U';
    ELSIF DELETING THEN 
       V_TYPE := 'D';
    END IF;
    
    INSERT INTO ERD_ADM.DM_STD_DOMAIN_HST
        (H_DT, H_TYPE, I_NAME, D_NAME, D_CLASS, DFLT_YN, D_ENAME, D_DTYPE, D_DLEN, D_DSCALE
       , D_LDTYPE_LEN, D_PDTYPE_LEN, D_VALID, D_DESC, D_PARSED, REGTR, REGDT, MODFR, MODDT)
    VALUES(V_DT, V_TYPE, :OLD.I_NAME, :OLD.D_NAME, :OLD.D_CLASS, :OLD.DFLT_YN, :OLD.D_ENAME, :OLD.D_DTYPE, :OLD.D_DLEN, :OLD.D_DSCALE
       , :OLD.D_LDTYPE_LEN, :OLD.D_PDTYPE_LEN, :OLD.D_VALID, :OLD.D_DESC, :OLD.D_PARSED, :OLD.REGTR, :OLD.REGDT, :OLD.MODFR, :OLD.MODDT)
    ;
END;
/
CREATE OR REPLACE SYNONYM ERWIN_DEV.DM_STD_DOMAIN FOR ERD_ADM.DM_STD_DOMAIN;
ALTER TABLE ERD_ADM.DM_STD_DOMAIN ADD (
  CONSTRAINT DM_STD_DOMAIN_PK
  PRIMARY KEY
  (I_NAME)
  USING INDEX ERD_ADM.DM_STD_DOMAIN_PK
  ENABLE VALIDATE);
GRANT SELECT ON ERD_ADM.DM_STD_DOMAIN TO ERWIN_DEV;

1.2 표준용어[편집]

DROP TABLE ERD_ADM.DM_STD_TERM CASCADE CONSTRAINTS;

CREATE TABLE ERD_ADM.DM_STD_TERM
(
  TERM         VARCHAR2(100 BYTE)               NOT NULL,
  ETERM        VARCHAR2(30 BYTE)                NOT NULL,
  I_NAME       VARCHAR2(100 BYTE)               NOT NULL,
  PARSED_TERM  VARCHAR2(200 BYTE),
  REGTR        VARCHAR2(30 BYTE)                NOT NULL,
  REGDT        DATE                             NOT NULL,
  MODFR        VARCHAR2(30 BYTE)                NOT NULL,
  MODDT        DATE                             NOT NULL
)
;

COMMENT ON TABLE ERD_ADM.DM_STD_TERM IS '표준용어';

COMMENT ON COLUMN ERD_ADM.DM_STD_TERM.TERM IS '용어';

COMMENT ON COLUMN ERD_ADM.DM_STD_TERM.ETERM IS '영문명';

COMMENT ON COLUMN ERD_ADM.DM_STD_TERM.I_NAME IS '인포타입';

COMMENT ON COLUMN ERD_ADM.DM_STD_TERM.PARSED_TERM IS '단어분해';

COMMENT ON COLUMN ERD_ADM.DM_STD_TERM.REGTR IS '등록자';

COMMENT ON COLUMN ERD_ADM.DM_STD_TERM.REGDT IS '등록일시';

COMMENT ON COLUMN ERD_ADM.DM_STD_TERM.MODFR IS '수정자';

COMMENT ON COLUMN ERD_ADM.DM_STD_TERM.MODDT IS '수정일시';
CREATE UNIQUE INDEX ERD_ADM.DM_STD_TERM_PK ON ERD_ADM.DM_STD_TERM
(TERM)
;

CREATE UNIQUE INDEX ERD_ADM.DM_STD_TERM_U1 ON ERD_ADM.DM_STD_TERM
(ETERM)
;
CREATE OR REPLACE TRIGGER ERD_ADM.TR_DM_STD_TERM
  AFTER UPDATE OR DELETE ON ERD_ADM.DM_STD_TERM
FOR EACH ROW
DECLARE
  V_DT     DATE DEFAULT SYSDATE;
  V_TYPE   VARCHAR(1);
BEGIN
    IF UPDATING THEN 
       V_TYPE := 'U';
    ELSIF DELETING THEN 
       V_TYPE := 'D';
    END IF;
    
    INSERT INTO ERD_ADM.DM_STD_TERM_HST
        (H_DT, H_TYPE, TERM, ETERM, I_NAME, PARSED_TERM, REGTR, REGDT, MODFR, MODDT)
    VALUES(V_DT, V_TYPE, :OLD.TERM, :OLD.ETERM, :OLD.I_NAME, :OLD.PARSED_TERM
         , :OLD.REGTR, :OLD.REGDT, :OLD.MODFR, :OLD.MODDT);
END;
/
CREATE OR REPLACE SYNONYM ERWIN_DEV.DM_STD_TERM FOR ERD_ADM.DM_STD_TERM;
ALTER TABLE ERD_ADM.DM_STD_TERM ADD (
  CONSTRAINT DM_STD_TERM_C1
  CHECK (TERM = TRANSLATE(TERM, '@_ ','@'))
  ENABLE VALIDATE
,  CONSTRAINT DM_STD_TERM_PK
  PRIMARY KEY
  (TERM)
  USING INDEX ERD_ADM.DM_STD_TERM_PK
  ENABLE VALIDATE);
GRANT SELECT ON ERD_ADM.DM_STD_TERM TO ERWIN_DEV;

1.3 표준단어[편집]

DROP TABLE ERD_ADM.DM_STD_VOCA CASCADE CONSTRAINTS;

CREATE TABLE ERD_ADM.DM_STD_VOCA
(
  VOCA     VARCHAR2(100 BYTE)                   NOT NULL,
  EABB     VARCHAR2(30 BYTE)                    NOT NULL,
  ENAME    VARCHAR2(100 BYTE)                   NOT NULL,
  NOUSE    VARCHAR2(1000 BYTE),
  CLS_YN   VARCHAR2(1 BYTE)                     NOT NULL,
  SRC      VARCHAR2(100 BYTE),
  V_DESC   VARCHAR2(4000 BYTE)                  NOT NULL,
  V_TYPE   NUMBER(2)                            NOT NULL,
  BIZAREA  VARCHAR2(100 BYTE),
  V_NO     NUMBER(5)                            NOT NULL,
  E_NO     NUMBER(5)                            NOT NULL,
  REGTR    VARCHAR2(30 BYTE)                    NOT NULL,
  REGDT    DATE                                 NOT NULL,
  MODFR    VARCHAR2(30 BYTE)                    NOT NULL,
  MODDT    DATE                                 DEFAULT SYSDATE               NOT NULL
)
;
COMMENT ON TABLE ERD_ADM.DM_STD_VOCA IS '표준단어';

COMMENT ON COLUMN ERD_ADM.DM_STD_VOCA.VOCA IS '표준단어';

COMMENT ON COLUMN ERD_ADM.DM_STD_VOCA.EABB IS '영문약어';

COMMENT ON COLUMN ERD_ADM.DM_STD_VOCA.ENAME IS '영문명';

COMMENT ON COLUMN ERD_ADM.DM_STD_VOCA.NOUSE IS '유사어';

COMMENT ON COLUMN ERD_ADM.DM_STD_VOCA.CLS_YN IS '분류어여부';

COMMENT ON COLUMN ERD_ADM.DM_STD_VOCA.SRC IS '출처';

COMMENT ON COLUMN ERD_ADM.DM_STD_VOCA.V_DESC IS '단어정의';

COMMENT ON COLUMN ERD_ADM.DM_STD_VOCA.V_TYPE IS '단어구분(10:표준,11:복합어,40:금칙(등록),41:복합금칙(등록),49:금칙(자동생성), 50:복합금칙(삭제대상), 90:미사용,99:검토중)';

COMMENT ON COLUMN ERD_ADM.DM_STD_VOCA.BIZAREA IS '업무영역';

COMMENT ON COLUMN ERD_ADM.DM_STD_VOCA.V_NO IS '단어순번';

COMMENT ON COLUMN ERD_ADM.DM_STD_VOCA.E_NO IS '약어순번';

COMMENT ON COLUMN ERD_ADM.DM_STD_VOCA.REGTR IS '등록자';

COMMENT ON COLUMN ERD_ADM.DM_STD_VOCA.REGDT IS '등록일시';

COMMENT ON COLUMN ERD_ADM.DM_STD_VOCA.MODFR IS '수정자';

COMMENT ON COLUMN ERD_ADM.DM_STD_VOCA.MODDT IS '수정일시';
CREATE UNIQUE INDEX ERD_ADM.DM_STD_VOCA_PK ON ERD_ADM.DM_STD_VOCA
(VOCA)
;

CREATE UNIQUE INDEX ERD_ADM.DM_STD_VOCA_U01 ON ERD_ADM.DM_STD_VOCA
(CASE  WHEN "V_TYPE"<20 THEN "ENAME" ELSE NULL END )
;

CREATE UNIQUE INDEX ERD_ADM.DM_STD_VOCA_UK01 ON ERD_ADM.DM_STD_VOCA
(EABB)
;
CREATE OR REPLACE TRIGGER ERD_ADM.TR_DM_STD_VOCA
  AFTER UPDATE OR DELETE ON ERD_ADM.DM_STD_VOCA
FOR EACH ROW
DECLARE
  V_DT     DATE DEFAULT SYSDATE;
  V_TYPE   VARCHAR(1);
BEGIN
    IF UPDATING THEN 
       V_TYPE := 'U';
    ELSIF DELETING THEN 
       V_TYPE := 'D';
    END IF;
    
    INSERT INTO ERD_ADM.DM_STD_VOCA_HST
        (H_DT, H_TYPE, VOCA, EABB, ENAME, NOUSE, CLS_YN, SRC, V_DESC, V_TYPE, BIZAREA, V_NO, E_NO, REGTR, REGDT, MODFR, MODDT)
    VALUES(V_DT, V_TYPE, :OLD.VOCA, :OLD.EABB, :OLD.ENAME, :OLD.NOUSE, :OLD.CLS_YN, :OLD.SRC
        , :OLD.V_DESC, :OLD.V_TYPE, :OLD.BIZAREA, :OLD.V_NO, :OLD.E_NO, :OLD.REGTR, :OLD.REGDT, :OLD.MODFR, :OLD.MODDT);
END;
/
CREATE OR REPLACE SYNONYM ERWIN_DEV.DM_STD_VOCA FOR ERD_ADM.DM_STD_VOCA;
ALTER TABLE ERD_ADM.DM_STD_VOCA ADD (
  CONSTRAINT DM_STD_VOCA_PK
  PRIMARY KEY
  (VOCA)
  USING INDEX ERD_ADM.DM_STD_VOCA_PK
  ENABLE VALIDATE);
GRANT SELECT ON ERD_ADM.DM_STD_VOCA TO ERWIN_DEV;

1.4 공통 코드[편집]

DROP TABLE ERD_ADM.TB_DM_CM_CD CASCADE CONSTRAINTS;

CREATE TABLE ERD_ADM.TB_DM_CM_CD
(
  GRP_CD   VARCHAR2(30 BYTE)                    NOT NULL,
  CM_CD    VARCHAR2(30 BYTE)                    NOT NULL,
  CD_NM    VARCHAR2(200 BYTE)                   NOT NULL,
  CD_DESC  VARCHAR2(2000 BYTE),
  USE_YN   VARCHAR2(1 BYTE)                     NOT NULL,
  ORD_NO   NUMBER(3),
  ATT1     VARCHAR2(100 BYTE),
  ATT2     VARCHAR2(100 BYTE),
  ATT3     VARCHAR2(100 BYTE),
  ATT4     VARCHAR2(100 BYTE),
  ATT5     VARCHAR2(100 BYTE),
  ATT6     VARCHAR2(100 BYTE),
  ATT7     VARCHAR2(100 BYTE)
)
;
COMMENT ON COLUMN ERD_ADM.TB_DM_CM_CD.GRP_CD IS '그룹코드';

COMMENT ON COLUMN ERD_ADM.TB_DM_CM_CD.CM_CD IS '공통코드';

COMMENT ON COLUMN ERD_ADM.TB_DM_CM_CD.CD_NM IS '공통코드명';

COMMENT ON COLUMN ERD_ADM.TB_DM_CM_CD.CD_DESC IS '설명';

COMMENT ON COLUMN ERD_ADM.TB_DM_CM_CD.USE_YN IS '사용여부';

COMMENT ON COLUMN ERD_ADM.TB_DM_CM_CD.ORD_NO IS '순번';

COMMENT ON COLUMN ERD_ADM.TB_DM_CM_CD.ATT1 IS '부가속성1';

COMMENT ON COLUMN ERD_ADM.TB_DM_CM_CD.ATT2 IS '부가속성2';

COMMENT ON COLUMN ERD_ADM.TB_DM_CM_CD.ATT3 IS '부가속성3';

COMMENT ON COLUMN ERD_ADM.TB_DM_CM_CD.ATT4 IS '부가속성4';

COMMENT ON COLUMN ERD_ADM.TB_DM_CM_CD.ATT5 IS '부가속성5';

COMMENT ON COLUMN ERD_ADM.TB_DM_CM_CD.ATT6 IS '부가속성6';

COMMENT ON COLUMN ERD_ADM.TB_DM_CM_CD.ATT7 IS '부가속성7';
CREATE UNIQUE INDEX ERD_ADM.PK_TB_DM_CM_CD ON ERD_ADM.TB_DM_CM_CD
(GRP_CD, CM_CD)
;
ALTER TABLE ERD_ADM.TB_DM_CM_CD ADD (
  CONSTRAINT PK_TB_DM_CM_CD
  PRIMARY KEY
  (GRP_CD, CM_CD)
  USING INDEX ERD_ADM.PK_TB_DM_CM_CD
  ENABLE VALIDATE);

2 메타웍스 연동 표준검사[편집]

2.1 테이블 논리/물리명표준체크 뷰[편집]

CREATE OR REPLACE FORCE VIEW V_TABLE_META_CHECK
AS
    WITH
        temp
        AS
            (SELECT OWNER
                  , COMMENTS
                  , TABLE_NAME -- , SUBSTR (TABLE_NAME, 7, 30)     SUB_TABLE_NAME
                  , REGEXP_REPLACE (SUBSTR (TABLE_NAME, 7, 30)
                                  , '_[0-9]$'
                                  , '')    SUB_TABLE_NAME
               FROM DBA_TAB_COMMENTS
              WHERE OWNER IN ('SCOTT', 'CYKIM') AND TABLE_NAME LIKE 'TB%')
      SELECT OWNER
           , COMMENTS
           , TABLE_NAME
           , SUB_TABLE_NAME
           , EABB
           , VOCA
           , COLUMN_VALUE
           , STD_VOCA_TABLE_NAME
           , SUBSTR (COMMENTS, 1, 3) || STD_VOCA_TABLE_NAME    AS FULL_STD_VOCA_TABLE_NAME
        FROM (SELECT A.OWNER
                   , A.COMMENTS
                   , A.TABLE_NAME
                   , A.SUB_TABLE_NAME
                   , B.EABB
                   , B.VOCA
                   , COLUMN_VALUE
                   , LISTAGG (VOCA, '')
                         WITHIN GROUP (ORDER BY COLUMN_VALUE)
                         OVER (PARTITION BY A.TABLE_NAME)    AS STD_VOCA_TABLE_NAME
                FROM (SELECT T.OWNER
                           , T.COMMENTS
                           , T.TABLE_NAME
                           , TRIM (REGEXP_SUBSTR (T.SUB_TABLE_NAME
                                                , '[^_]+'
                                                , 1
                                                , LEVELS.COLUMN_VALUE))    AS SUB_TABLE_NAME
                           , LEVELS.COLUMN_VALUE
                        FROM TEMP T
                           , TABLE (
                                 CAST (
                                     MULTISET (
                                             SELECT LEVEL
                                               FROM DUAL
                                         CONNECT BY LEVEL <=
                                                      LENGTH (
                                                          REGEXP_REPLACE (
                                                              T.SUB_TABLE_NAME
                                                            , '[^_]+'))
                                                    + 1) AS SYS.ODCINUMBERLIST))
                             LEVELS) A
                     LEFT JOIN ERD_ADM.DM_STD_VOCA B
                         ON A.SUB_TABLE_NAME = B.EABB
               WHERE B.V_TYPE <= 20)
       WHERE     COMMENTS <> SUBSTR (COMMENTS, 1, 3) || STD_VOCA_TABLE_NAME
             AND NOT REGEXP_LIKE (TABLE_NAME, '_[1-9]$')
    ORDER BY TABLE_NAME, COLUMN_VALUE;

2.2 컬럼 물리/논리명,타입 표준 체크 뷰[편집]

CREATE OR REPLACE FORCE VIEW V_TAB_COL_CHECK
AS
    SELECT A.OWNER
         , A.TABLE_NAME
         , D."담당자"    TABLE_OWNER
         , C.COMMENTS       AS "TABLE_HAN_NAME"
         , A.COLUMN_NAME
         , A.DATA_TYPE      AS "COL_TYPE"
         , B.PDATATYPE      AS "META_TYPE"
         , A.COMMENTS       AS "COL_COMMENT"
         , B.TERMNAME       AS "META_COMMENT"
         , DECODE (
               A.DATA_TYPE
             , B.PDATATYPE, ''
             ,    'ALTER TABLE xxx.'
               || A.TABLE_NAME
               || ' MODIFY ('
               || A.COLUMN_NAME
               || ' '
               || B.PDATATYPE
               || ');')     AS TYPE_SQL
         , DECODE (
               A.COMMENTS
             , B.TERMNAME, ''
             ,    'COMMENT ON COLUMN xxx.'
               || A.TABLE_NAME
               || '.'
               || A.COLUMN_NAME
               || ' IS '''
               || B.TERMNAME
               || ''';')    AS COMT_SQL
      FROM (  SELECT OWNER
                   , TABLE_NAME
                   , COLUMN_NAME                                   --     , PK
                   , CASE DATA_TYPE
                         WHEN 'DATE'
                         THEN
                             'DATE'
                         WHEN 'TIMESTAMP(6)'
                         THEN
                             'TIMESTAMP'
                         WHEN 'CLOB'
                         THEN
                             'CLOB'
                         WHEN 'BLOB'
                         THEN
                             'BLOB'
                         ELSE
                                DATA_TYPE
                             || '('
                             || NVL (DATA_TYPE_2, DATA_LENGTH)
                             || ')'
                     END    AS DATA_TYPE                     --     , NULLABLE
                   , COMMENTS
                FROM (SELECT A.OWNER
                           , A.TABLE_NAME
                           , A.COLUMN_ID
                           , B.POSITION                                   PK
                           , A.COLUMN_NAME
                           , A.DATA_TYPE
                           ,    A.DATA_PRECISION
                             || DECODE (
                                    DECODE (A.DATA_SCALE
                                          , 0, NULL
                                          , A.DATA_SCALE)
                                  , NULL, NULL
                                  , ',' || A.DATA_SCALE)                  DATA_TYPE_2
                           , A.DATA_LENGTH
                           , A.DATA_PRECISION
                           , A.DATA_SCALE
                           , A.NULLABLE
                           , A.COMMENTS
                           , ROW_NUMBER ()
                                 OVER (
                                     PARTITION BY A.OWNER
                                                , A.TABLE_NAME
                                                , A.COLUMN_ID
                                     ORDER BY A.COLUMN_ID, B.POSITION)    RN
                        FROM (SELECT COL.OWNER
                                   , COL.TABLE_NAME
                                   , COL.COLUMN_ID
                                   , --                                    COL.COLUMN_NAME,
                                     REGEXP_REPLACE (COL.COLUMN_NAME
                                                   , '([[:digit:]]+)$')    AS COLUMN_NAME
                                   , COL.DATA_TYPE
                                   , COL.DATA_LENGTH
                                   , COL.DATA_PRECISION
                                   , COL.DATA_SCALE
                                   , COL.NULLABLE
                                   , -- COM.COMMENTS
                                     REGEXP_REPLACE (COMMENTS
                                                   , '([[:digit:]]+)$')    AS COMMENTS
                                FROM DBA_TAB_COLUMNS COL, DBA_COL_COMMENTS COM
                               WHERE     COL.COLUMN_NAME = COM.COLUMN_NAME
                                     AND COL.OWNER = COM.OWNER
                                     AND COL.TABLE_NAME = COM.TABLE_NAME
                                     AND COM.OWNER IN ('SCOTT', 'CYKIM') -- AND    COM.TABLE_NAME LIKE:IN_TABLE_NAME || '%'
                                     AND COL.TABLE_NAME LIKE 'TB%') A
                           , DBA_CONS_COLUMNS B
                       WHERE     B.OWNER(+) = A.TABLE_NAME
                             AND B.TABLE_NAME(+) = A.TABLE_NAME
                             AND B.COLUMN_NAME(+) = A.COLUMN_NAME) X
               WHERE X.RN = 1
            ORDER BY X.TABLE_NAME, X.COLUMN_ID) A
           LEFT OUTER JOIN ERD_ADM.V_STD_TERM B
               ON A.COLUMN_NAME = B.TERMENGNAME
           LEFT OUTER JOIN DBA_TAB_COMMENTS C
               ON C.OWNER = A.OWNER AND C.TABLE_NAME = A.TABLE_NAME
           LEFT OUTER JOIN MIGCOMMON.UV_ERD_TBL_MGMT_USER D
               ON C.TABLE_NAME = D.TABLE_NAME
     WHERE (   A.DATA_TYPE <> B.PDATATYPE
            OR A.COMMENTS <> B.TERMNAME
            OR B.PDATATYPE IS NULL);