oracle table, column 정보 조회1

SELECT SUBSTR(a.TABLE_NAME,0,2) AS "업무구분"
     , a.TABLE_NAME AS "테이블명"
     , b.COMMENTS AS "테이블한글명"
     , a.COLUMN_ID AS "컬럼순번"
     , a.COLUMN_NAME AS "컬럼명"
     , c.COMMENTS AS "컬럼한글명"
     , a.DATA_TYPE AS "타입"
     , CASE WHEN NVL(a.DATA_SCALE,0) = 0 THEN TO_CHAR(a.DATA_LENGTH)
            ELSE a.DATA_LENGTH||'('||a.DATA_SCALE||')'
       END AS "길이"
     , CASE WHEN d.COLUMN_NAME IS NOT NULL THEN TO_CHAR(d.KEY_SEQ)
            ELSE ''
       END AS "KEY"
     , CASE WHEN a.nullable = 'N' THEN 'Y'
            ELSE ''
       END AS "NOT NULL"
     , a.DATA_DEFAULT AS "초기값"
  FROM ALL_TAB_COLUMNS a
 INNER JOIN ALL_TAB_COMMENTS b
    ON a.OWNER = b.OWNER
   AND a.TABLE_NAME = b.TABLE_NAME
  LEFT OUTER JOIN ALL_COL_COMMENTS c
    ON a.OWNER = c.OWNER
   AND a.TABLE_NAME = c.TABLE_NAME
   AND a.COLUMN_NAME = c.COLUMN_NAME
  LEFT OUTER JOIN (
       SELECT ia.OWNER
            , ia.TABLE_NAME
            , ib.COLUMN_NAME
            , ib.POSITION AS KEY_SEQ
         FROM ALL_CONSTRAINTS ia
        INNER JOIN ALL_CONS_COLUMNS ib
           ON ia.OWNER  = ib.OWNER
          AND ia.TABLE_NAME = ib.TABLE_NAME
          AND ia.CONSTRAINT_NAME = ib.CONSTRAINT_NAME
          AND ia.CONSTRAINT_TYPE = 'P'
       ) d
    ON a.OWNER = d.OWNER
   AND a.TABLE_NAME = d.TABLE_NAME
   AND a.COLUMN_NAME = d.COLUMN_NAME
 WHERE a.OWNER = 'SEMA_ERP' /* bx-erp : ERP, semis : SEMA_OWN  */
   AND a.TABLE_NAME LIKE '%_HIST%' /*조회할 Table명*/
--   AND a.TABLE_NAME = 'AM_CARDCONFMDTALSRECPT' /*조회할 Table명*/
--   AND b.COMMENTS LIKE '%원천징수%' /*조회할 Table명*/
--   AND a.TABLE_NAME  = 'AM_SLIP_WRT_M'
--   AND a.COLUMN_NAME LIKE '%ACNUT_NM_DIV_CD%'
--    AND c.COMMENTS LIKE '%통화%코드%'
--   AND a.column_name NOT LIKE '%#_%'ESCAPE'#'
-- ORDER BY a.TABLE_NAME
--     , a.COLUMN_ID
ORDER BY a.TABLE_NAME
    , a.COLUMN_ID
--    , a.COLUMN_NAME 
;

oracle dml 문장 생성 1

WITH tempTbl AS (
    SELECT a.COLUMN_ID AS colId 
         , a.COLUMN_NAME AS colName
         , LOWER(SUBSTR(REPLACE(INITCAP(a.COLUMN_NAME),'_'),1,1))||SUBSTR(REPLACE(INITCAP(a.COLUMN_NAME),'_'),2,LENGTH(a.COLUMN_NAME)-1) AS camel 
         , ('/*'||c.COMMENTS || ','|| a.DATA_TYPE ||'('||a.DATA_LENGTH ||')'||'*/') AS cmt
         , ('/*'||c.COMMENTS ||'*/') AS cmt2
         , c.COMMENTS AS orgCmt
         , a.DATA_TYPE AS dataType
         , a.DATA_LENGTH AS dataLen
      FROM ALL_TAB_COLUMNS a
      LEFT OUTER JOIN ALL_COL_COMMENTS c
        ON a.OWNER = c.OWNER
       AND a.TABLE_NAME = c.TABLE_NAME
       AND a.COLUMN_NAME = c.COLUMN_NAME
     WHERE a.OWNER = 'SEMA_ERP'
       AND a.TABLE_NAME IN ('AM_CMMN_COSTPT_D') /*조회할 Table명*/
--       AND a.COLUMN_NAME IN ('COMPANY_CODE','BANK_NO_SEQ','BANK_NO','BANK_DIV','CUST_CODE','DEPOSIT_OWNER','USE_YN','DEPT_CODE','APL_STR_DTE'
--                            ,'APL_END_DTE','FUND_CHK_YN','FUND_CHK_NOTE','FUND_CHK_YN_B','BANK_RES_CODE','BANK_RES_DESC','INPUT_DATE','INPUT_DUTY_ID'
--                            ,'CHG_DATE','CHG_DUTY_ID'
--       )
)
SELECT MAX(aa.ommType) AS ommType
     , MAX(aa.colId) AS colId
     , aa.ommName
     , MAX(aa.ommDescription) AS ommDescription
     , MAX(aa.ommLength) AS ommLength
     , (MAX(aa.ommType)||' '||aa.ommName||'<length='||MAX(aa.ommLength)||' description="'||MAX(aa.ommDescription)||'">;') AS ommGenStr
     , MAX(aa.dmlStr) AS dmlStr
     , MAX(aa.dmlStr2) AS dmlStr2
  FROM (
        SELECT CASE WHEN a.dataType IN ('VARCHAR2') THEN 'String' 
                    WHEN a.dataType IN ('DATE') THEN 'Date'
                    WHEN a.dataType IN ('NUMBER') AND a.dataLen < 10 THEN 'Integer'
                    WHEN a.dataType IN ('NUMBER') AND a.dataLen >= 10 THEN 'BigDecimal'
               END ommType
             , a.camel AS ommName
             , a.orgCmt AS ommDescription
             , a.dataLen AS ommLength
             , a.colId 
             , (', '||RPAD(a.colName, b.maxLenCol, ' ') ||' AS '||RPAD(a.camel, b.maxLenCmt,' ')||' '||a.cmt) AS dmlStr
             , (', '||RPAD(a.colName, b.maxLenCol, ' ') ||' '||a.cmt2) AS dmlStr2
          FROM tempTbl a
         OUTER APPLY( SELECT MAX(LENGTHB(colName)) AS maxLenCol
                           , MAX(LENGTHB(camel)) AS maxLenCmt 
                        FROM tempTbl
                    ) b  
       ) aa
 GROUP BY aa.ommName
-- ORDER BY aa.ommName
 ORDER BY MAX(aa.colId)
-- ORDER BY MAX(aa.ommName)

API Log 구성 항목

API Log에 기록될 항목들을 아래와 같이 정리해 보았다.
*표시가 붙은 항목은 가급적 포함시켜야 하는 항목이다.

api transaction id*
ㆍ구성예 : api was의 host명 + ‘-’ + api was에서 생성한 uuid
ㆍhost name 조회 방법 예(java) : InetAddress.getLocalHost().getHostName()
ㆍuuid 획득 방법 예(java) : UUID.randomUUID()
ㆍ생성 : WAS

client trnsaction id
ㆍclient에서 전달한 client transaction id
ㆍ생성 : request header

result code*
ㆍ처리 결과 코드
ㆍ코드 정보를 DB table등으로 관리하여 사용한다.
ㆍ생성 : WAS

request time*
ㆍ처리 요청 시각
ㆍ생성 : WAS

process time*
ㆍapi 처리시간( 단위 : 초 ).
ㆍ1/1000 초 사용시 0초로 기록되는 경우가 발생하니 프로젝트 성격에 따라 이하 단위까지 측정하여 기록할지 결정하도록 한다. 0초로 기록됐을 경우 표시는 ‘0.001 초 이하’ 로 표시하면 된다.
ㆍ생성 : WAS

api server instance*
ㆍapi를 처리한 api was instance host name
ㆍ생성 : WAS

response content*
ㆍapi 응답 메시지 내용
ㆍ문자열보다 json type 사용 권장.
ㆍ생성 : WAS

user id
ㆍapi 요청자
ㆍ생성 : request header or request parameter

request parameter content*
ㆍ요청 파라메터 내용을 json 형식으로 표시.
ㆍ문자열보다 json type 사용 권장.
ㆍ생성 : request parameter

client id
ㆍclient가 복수이거나 client instance가 복수일 경우 기록.
ㆍ생성 : request header

client request time
ㆍclient에서 전달한 요청 시각.
ㆍ생성 : request header

url*
ㆍdomain과 request parameter를 제외한 api request url.
ㆍ생성 : WAS( from HttpServletRequest )

method *
ㆍhttp request method
    > GET : 주요 기능이 조회일 경우.
    > POST : 주요 기능이 추가일 경우.
    > PUT : 주요 기능이 변경일 경우.
    > DELETE : 주요 기능이 삭제일 경우.
ㆍ생성 : WAS( from HttpServletRequest )

ip address
ㆍapi를 요청한 ip 주소
ㆍ생성 : WAS( from HttpServletRequest )

write time*
ㆍapi log  등록 시각.
ㆍinsert시 CURRENT_TIMESTAMP 사용.
ㆍ생성 : DBMS.

referer*
ㆍapi를 요청한 referer.
ㆍ생성 : request header 또는 WAS( from HttpServletRequest )