표준 Json 타입 전환 방안(oracle to mysql)

표준 JSON 타입 전환 방안

​

1. 환경 및 전환요건

ASIS : Oracle의 전환 Table에서 대상컬럼이 전환사유상세내용 VARCHAR2(4000) 이라고하자

TOBE : Mysql에서 전환사유상세내용의 컬럼 타입이 JSON으로 되어 있다.

전환요건 : ASIS테이블의 모든 컬럼 값을 JSON형태로 전환요청 하였다.

​

2. 전환방법

​

[1] JSON 변환 추출 스크립트

SELECT 'SELECT JSON_OBJECT'||CHR(10)||'(' AS JSON_SCRIPT FROM DUAL
UNION ALL
SELECT JSON_SCRIPT
  FROM (
SELECT CASE WHEN COLUMN_ID = 1 THEN '  ' ELSE ', ' END
       ||' KEY '''||COLUMN_NAME|| ''' IS ''"''||' ||
       CASE WHEN DATA_TYPE IN ('DATE','TIMESTAMP') 
			THEN 'TO_CHAR('||COLUMN_NAME||',''YYYYMMDDHH24MISS'') '
            WHEN DATA_TYPE IN ('VARCHAR2','VARCHAR','CHAR')
            THEN 'REGEXP_REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TRIM('||COLUMN_NAME||'),''\'',''\\''),''"'',''\"''),''/'',''\/''),chr(8),''\b''),chr(9),''\t''),chr(10),''\n''),chr(12),''\f''),chr(13),''\r''),'''''''',''\\''''''),''[^[:print:]]'')  '
            ELSE 'TO_CHAR('||COLUMN_NAME||')'
       END
       || '||''"'''
       || ' FROMAT JSON' AS JSON_SCRIPT
 FROM ALL_TAB_COLUMNS A
WHERE OWNER = :OWNER
  AND TABLE_NAME = :TABLE_NAME 
ORDER BY COLUMN_ID
)
UNION ALL
SELECT ') AS JSON_OBJECT'||CHR(10)||' FROM '||:OWNER||'.'||:TABLE_NAME||CHR(10)||'WHERE 1=1'||CHR(10) AS JSON_SCRIPT FROM DUAL
;

[:print:] : 출력이 가능한 모든 문자 (아스키 32~126)

[^[:print:]] : 출력이 불 가능한 문자

​

[2] JSON_OBJECT 적용

SELECT JSON_OBJECT
       (
         KEY 'NO' IS '"'||TO_CHAR(NO)||'"' FORMAT JSON
       , KEY 'CUST_NO' IS '"'||TO_CHAR(CUST_NO) || '"' FORMAT JSON
       , KEY 'CONTENTS' IS '"'||REGEXP_REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TRIM(CONTENTS),'\','\\'),'"','\"'),'/','\/'),chr(8),'\b'),chr(9),'\t'),chr(10),'\n'),chr(12),'\f'),chr(13),'\r'),'''',''\\'''),'[^[:print:]]')||'"' FORMAT JSON
       , KEY 'CREATE_DATE' IS '"'||TO_CHAR(CREATE_DATE,'YYYYMMDDHH24MISS')|| '"' FORMAT JSON
       , ........
       ) AS JSON_SCRIPT
  FROM OWNER.TABLE_NAME
-- WHERE NO = 1111
;

 

[3] 실행결과 JSON 형태로 추출된다.

{"NO":"1111","CUST_NO":"12341234","NAME":"JEUS\\'S PATH","CREATE_DATE":"20210611131313",...}

 

 

json \를 escape문자로 사용한다. 특수문자(")를 표시하기 위해선 /" 형태를 취해야 한다.

​

The JSON Standards have rules governing how to escape special characters in strings, where a backslash \ is used as the escaping character. For example, if you are including a double quote ” within a string, you have to escape it to \”. Other examples include :

\b  Backspace (ASCII code x08)
\f  Form feed (ASCII code x0C)
\n  New line (ASCII code x0A)
\r  Carriage Return (ASCII code x0D)
\t  Horizontal Tab (ASCII code x09)
\v  Vertical tab (ASCII code x0B)
\'  Apostrophe / Single quote (ASCII code x27)
\"  Double quote (ASCII code x22)
\\  Backslash character / Reverse Solidis (ASCII code x5C)
\Uxxxx Unprintable / Unicode characters (x = hex digit)

​

https://paulzipblog.wordpress.com/2020/04/28/json-escape-unescape-string-functions/