Oracle Character Set 변환(10): 6.3.CLOB 변환 방법

이 글은 새로운 블로그로 옮겼습니다. 5초후 자동으로 이동합니다.

▶ 새로운 블로그 주소: https://prodskill.com/

▶ 새로운 글 주소: https://prodskill.com/oracle-character-set-migration-10-convert-clob/

이 글에서는 US7ASCII character set DB에서 한글이 포함된 CLOB type을 UTF8로 변환하는 방법을 살펴본다.

목차

 

---

 

6.3. CLOB 변환 방법

 

Oracle Character Set 변환(8): 6.구현이 필요한 방법 (tistory.com)

Oracle Character Set 변환(8): 6.구현이 필요한 방법

 

위 글에서 다음과 같은 내용을 언급했었다.

참고로, US7ASCII to AL32UTF8 변환의 경우는 CLOB type 컬럼이 정상적으로 변환되지 않아 별도 처리가 필요하다.
다음 글에서 설명 예정이다.

 

이 글에서 US7ASCII 에서 AL32UTF8로 DB Link를 이용하여 한글이 포함된 CLOB type을 UTF8로 변환하는 방법을 설명한다.

 

먼저 ORAUS7, ORAMSWIN949, ORAUTF 세 개 instance에서 CLOB 데이터를 확인해 보자.

 

6.3.1. CLOB 데이터 확인

ORAUS7, ORAMSWIN949, ORAUTF 세 개 instance에 아래 INSERT 문장을 실행하여 CLOB 컬럼(REF_DES)에 데이터를 채운다.

INSERT INTO SUB_MON_STAT
(USE_MON, LINE_NUM, SUB_STA_ID, SUB_STA_NM, RIDE_PASGR_NUM,
 ALIGHT_PASGR_NUM, WORK_DT, COMMT,
 REF_DES, REF_IMG)
VALUES
('201301', '1호선', '0150', '서울역', 2199181,
 1855268, '20130723', RPAD('서울역', 3000, '서울역'),
 RPAD('서울역', 3000, '서울역'), UTL_RAW.CAST_TO_RAW('12345'))
;

 

각 컬럼의 data type은 아래와 같다.

Oracle Character Set 변환 테스트 테이블 구성

 

이제 다음 SQL로 각 instance에서 ref_des 컬럼(CLOB type) 데이터를 확인해 보자.

select dbms_lob.getlength(ref_des) chr_len
      ,lengthb(dbms_lob.substr(ref_des, dbms_lob.getlength(ref_des), 1)) byte_len
      ,dump(dbms_lob.substr(ref_des, dbms_lob.getlength(ref_des), 1), 1016) dp
      ,ref_des
  from sub_mon_stat
 where ref_des is not null
   and rownum <= 1
;

 

▼ ORAUS7

CLOB 데이터의 Character set은 US7ASCII이고, 한글은 정상적으로 표시되지 않는다.

ORAUS7 CLOB 데이터 확인

 

문자수(chr_len)는 3,000 이고, 바이트수(byte_len)도 3,000이다. 즉 한 문자가 1 바이트로 저장됨을 알 수 있다.

 

SQL Developer에서 NLS_LANG을 설정하는 방법을 찾지 못하여 sqlplus에서 실행하여 한글이 표시되는 것을 확인했다.

 

C:\Users\ymlee>set NLS_LANG=AMERICAN_AMERICA.US7ASCII

C:\Users\ymlee>sqlplus leg/leg@oraus7

SQL*Plus: Release 11.2.0.1.0 Production on Sun Jul 24 21:42:43 2022

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set pagesize 100
SQL> set linesize 200
SQL> select dbms_lob.getlength(ref_des) chr_len
  2        ,lengthb(dbms_lob.substr(ref_des, dbms_lob.getlength(ref_des), 1)) byte_len
  3        ,substr(dump(dbms_lob.substr(ref_des, dbms_lob.getlength(ref_des), 1), 1016), 1, 200) dp
  4        ,ref_des
  5    from sub_mon_stat
  6   where ref_des is not null
  7     and rownum <= 1
  8
SQL> /

   CHR_LEN   BYTE_LEN
---------- ----------
DP
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
REF_DES
--------------------------------------------------------------------------------
      3000       3000
Typ=1 Len=3000 CharacterSet=US7ASCII: bc,ad,bf,ef,bf,aa,bc,ad,bf,ef,bf,aa,bc,ad,bf,ef,bf,aa,bc,ad,bf,ef,bf,aa,bc,ad,bf,ef,bf,aa,bc,ad,bf,ef,bf,aa,bc,ad,bf,ef,bf,aa,bc,ad,bf,ef,bf,aa,bc,ad,bf,ef,bf,aa,
서울역서울역서울역서울역서울역서울역서울역서울역서울역서울역서울역서울역서울역서


SQL>

 

▼ ORAMSWIN949

CLOB 데이터의 Character set은 KO16MSWIN949이고, 한글은 정상적으로 표시된다.

ORAMSWIN949 CLOB 데이터 확인

 

문자수(chr_len)는 1,500 이고, 바이트수(byte_len)는 3,000이다. 즉 한 문자가 2 바이트로 저장됨을 알 수 있다.

 

▼ ORAUTF

CLOB 데이터의 Character set은 AL32UTF8이고, 한글은 정상적으로 표시된다.

ORAUTF CLOB 데이터 확인

 

문자수(chr_len)는 1,334 이고, 바이트수(byte_len)는 4,000이다. 즉 한 문자가 3 바이트로 저장됨을 알 수 있다.

(정확한 바이트수는 4,002 이나, dbms_lob.substr로 반환되는 최대 바이트수가 4,000이라서 약간 차이가 있다)

 

 

6.3.2. CLOB 데이터 변환

CLOB 데이터는 DB Link를 통해 select를 실행할 수 없다. "ORA-22992: 원격 테이블로 부터 선택된 LOB 위치를 사용할 수 없습니다" 오류가 발생한다.

CLOB 데이터를 DB Link로 query할 때 오류

 

select use_mon, line_num, sub_sta_id, sub_sta_nm, ride_pasgr_num, alight_pasgr_num, work_dt, commt
      ,ref_des
  from sub_mon_stat@dl_us7 a
;

ORA-22992: 원격 테이블로 부터 선택된 LOB 위치를 사용할 수 없습니다
22992. 00000 -  "cannot use LOB locators selected from remote tables"
*Cause:    A remote LOB column cannot be referenced.
*Action:   Remove references to LOBs in remote tables.

 

다음과 같이 CTAS(Create Table As Select) 구문을 사용하여 데이터를 복사할 수는 있다.

create table sub_mon_stat_from_oraus7
as
select use_mon, line_num, sub_sta_id, sub_sta_nm, ride_pasgr_num, alight_pasgr_num, work_dt
      ,ref_des
  from sub_mon_stat@dl_us7 a
;

select dbms_lob.getlength(ref_des) chr_len
      ,lengthb(dbms_lob.substr(ref_des, 1333, 1)) byte_len
      ,dump(dbms_lob.substr(ref_des, 1333, 1), 1016) dp
      ,ref_des
  from sub_mon_stat_from_oraus7
 where ref_des is not null
   and rownum <= 1
;

 

위 query 실행 결과를 보면 CLOB 컬럼의 Character set은 AL32UTF8로 변환되었으나, 한글 문자열은 정상적으로 변환되지 않았다.

CTAS로 생성한 Table 데이터 확인

 

CLOB 컬럼의 한글을 변환하는 과정은 다음과 같다.

CLOB 컬럼의 한글을 변환하는 과정 요약

 

1. Source DB(Instance #1, ORAUS7)에서 CLOB 컬럼의 data type을 BLOB으로 변환한 View 생성
2. Target DB(Instance #3, ORAUTF)에서 CTAS(Create Table As Select) 구문으로 Table 생성
3. 위에서 생성한 Table의 BLOB data type을 CLOB type으로 변환하여 Table 생성

 

하나씩 살펴보자.

 

1. Source DB(Instance #1, ORAUS7)에서 CLOB 컬럼의 data type을 BLOB으로 변환한 View 생성

CLOB 컬럼의 data type을 BLOB으로 변환하면 DB Link로 query할 때 Character set이 변환되지 않는다.

DBMS_LOB.CONVERTTOBLOB procedure를 이용하여 CLOB을 BLOB으로 변환하는 함수를 다음과 같이 생성한다.

CREATE OR REPLACE FUNCTION FN_CLOB_TO_BLOB (P_CLOB CLOB) RETURN BLOB
AS
 L_BLOB          BLOB;
 L_DEST_OFFSET   INTEGER := 1;
 L_SOURCE_OFFSET INTEGER := 1;
 L_LANG_CONTEXT  INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;
 L_WARNING       INTEGER := DBMS_LOB.WARN_INCONVERTIBLE_CHAR;
BEGIN
  IF DBMS_LOB.GETLENGTH(P_CLOB) != 0 THEN
      DBMS_LOB.CREATETEMPORARY(L_BLOB, TRUE);
      DBMS_LOB.CONVERTTOBLOB
      (
       DEST_LOB    =>L_BLOB,
       SRC_CLOB    =>P_CLOB,
       AMOUNT      =>DBMS_LOB.LOBMAXSIZE,
       DEST_OFFSET =>L_DEST_OFFSET,
       SRC_OFFSET  =>L_SOURCE_OFFSET,
       BLOB_CSID   =>DBMS_LOB.DEFAULT_CSID,
       LANG_CONTEXT=>L_LANG_CONTEXT,
       WARNING     =>L_WARNING
      );
  END IF;
  RETURN L_BLOB;
END;

 

그리고, 위 함수를 이용한 View를 생성한다.

테스트 목적으로 일부 컬럼과 CLOB 컬럼인 REF_DES에 값이 있는 행만 선택하도록 했다.

CREATE OR REPLACE VIEW VW_SUB_MON_STAT_BLOB
AS
SELECT  USE_MON, SUB_STA_ID
       ,FN_CLOB_TO_BLOB(REF_DES) REF_DES_BLOB
  FROM  SUB_MON_STAT
 WHERE  REF_DES IS NOT NULL;

 

 

2. Target DB(Instance #3, ORAUTF)에서 CTAS(Create Table As Select) 구문으로 Table 생성

다음 구문으로 DB Link에서 데이터를 가져와서 Table을 생성한다.

CREATE  TABLE SUB_MON_STAT_BLOB
AS
SELECT  A.*
  FROM  VW_SUB_MON_STAT_BLOB@DL_US7 A;

 

 

3. 위에서 생성한 Table의 BLOB data type을 CLOB type으로 변환하여 Table 생성

DBMS_LOB.CONVERTTOCLOB procedure를 이용하여 BLOB을 CLOB으로 변환하는 함수를 다음과 같이 생성한다.

CREATE OR REPLACE FUNCTION FN_BLOB_TO_CLOB (P_BLOB BLOB) RETURN CLOB
AS
 L_CLOB          CLOB;
 L_DEST_OFFSET   INTEGER := 1;
 L_SOURCE_OFFSET INTEGER := 1;
 L_LANG_CONTEXT  INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;
 L_WARNING       INTEGER := DBMS_LOB.WARN_INCONVERTIBLE_CHAR;
BEGIN
  DBMS_LOB.CREATETEMPORARY(L_CLOB, TRUE);
  DBMS_LOB.CONVERTTOCLOB
  (
   DEST_LOB    =>L_CLOB,
   SRC_BLOB    =>P_BLOB,
   AMOUNT      =>DBMS_LOB.LOBMAXSIZE,
   DEST_OFFSET =>L_DEST_OFFSET,
   SRC_OFFSET  =>L_SOURCE_OFFSET,
   --BLOB_CSID   =>DBMS_LOB.DEFAULT_CSID,
   BLOB_CSID   =>846, -- NLS_CHARSET_ID('KO16MSWIN949')
   LANG_CONTEXT=>L_LANG_CONTEXT,
   WARNING     =>L_WARNING
  );
  RETURN L_CLOB;
END;

 

DBMS_LOB.CONVERTTOCLOB procedure의 parameter중 BLOB_CSID는 "원본 데이터의 Character set ID(The character set ID of the source data)"를 의미한다.

자세한 내용은 아래 문서를 참조하기 바란다.

DBMS_LOB (oracle.com)

DBMS_LOB

 

문자열이 아닌 정수형으로 값을 지정해야 하고 NLS_CHARSET_ID 함수로 그 값을 알 수 있다.

원본 데이터의 Character set이 KO16MSWIN949이므로 다음과 같이 실행하여 846을 확인했다.

NLS_CHARSET_ID 함수로 BLOB_CSID 확인

 

SELECT NLS_CHARSET_ID('KO16MSWIN949')
  FROM DUAL;

 

그리고, 위 함수를 이용하여 Table을 생성하거나 query할 수 있다.

CREATE  TABLE SUB_MON_STAT_BLOB_TO_CLOB
AS
SELECT  USE_MON, SUB_STA_ID
       ,FN_BLOB_TO_CLOB(REF_DES_BLOB) REF_DES
  FROM  SUB_MON_STAT_BLOB A;

select *
  from sub_mon_stat_blob_to_clob
;

-- Table 생성하지 않을 경우는 다음과 같이 query 가능
select use_mon, sub_sta_id
       ,fn_blob_to_clob(ref_des_blob) ref_des
  from sub_mon_stat_blob
;

 

다음과 같이 한글이 정상적으로 변환된 것을 확인할 수 있다.

CLOB 변환결과 확인

 

---

여기까지 US7ASCII character set DB에서 한글이 포함된 CLOB type을 UTF8로 변환하는 방법을 살펴보았다.

여러 과정을 거쳐서 변환하다 보니 조금 복잡해 보이나, 한 단계씩 보면 어려울 것은 없다.

 

이 글까지 하여 Oracle Character set 변환에 대한 연재를 마친다.

필요한 사람에게 조금이나마 도움이 되었으면 한다.