Oracle Character Set 변환(9): 6. 사용자 구현 Character Set 변환 방법 (2)

이전 글(Oracle Character Set 변환(8): 6. 사용자 구현 Character Set 변환 방법 (1))에 이어 사용자 구현 Character Set 변환 방법 에 대해 살펴본다. DB Link, CTAS, UTL_RAW package를 활용하여 KO16MSWIN949, US7ASCII에서 AL32UTF8로 변환하는 방법을 확인할 수 있다.

6.2.3. 방법3) DB Link & CTAS & UTL_RAW

DB Link를 통해서 데이터를 가져오면서 Character set을 변환하는 방법이다.

하나의 서버에 세 개의 DB instance를 구성하고 세 가지 테스트를 실행한다.

1. ORAMSWIN949에서 ORAUS7 데이터 가져오기
2. ORAUTF에서 ORAMSWIN949 데이터 가져오기
3. ORAUTF에서 ORAUS7 데이터 가져오기

ORAUS7에 저장된 한글 데이터는 다음과 같이 확인할 수 있다.

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 17 22:34:26 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 use_mon, line_num, sub_sta_id, sub_sta_nm, ride_pasgr_num, alight_pasgr_num, work_dt
  2    from sub_mon_stat
  3   where rownum <= 10
  4  ;

USE_MON  LINE_NUM       SUB_ SUB_STA_NM           RIDE_PASGR_NUM ALIGHT_PASGR_NUM WORK_DT
-------- -------------- ---- -------------------- -------------- ---------------- --------
201301   2호선          0214 강변                        1565310          1549316 20130723
201301   2호선          0215 잠실나루                     536413           505376 20130723
201301   2호선          0216 잠실                        2323516          2106978 20130723
201301   2호선          0217 신천                         814104           786397 20130723
201301   2호선          0218 종합운동장                   374633           389860 20130723
201301   2호선          0219 삼성                        2015421          2050988 20130723
201301   2호선          0220 선릉                        1773445          1572999 20130723
201301   2호선          0221 역삼                        1496812          1644097 20130723
201301   2호선          0222 강남                        3453154          3558986 20130723
201301   2호선          0223 교대                        1195621          1323778 20130723

10 rows selected.

SQL>

아래 테스트에는 Oracle SQL Developer를 사용한다.

SQL Developer는 Oracle에서 제공하는 도구이고, 무료이다. 아래 URL에서 다운로드할 수 있다.

Oracle SQL Developer Downloads

테스트 #1) ORAMSWIN949 에서 ORAUS7 데이터 가져오기

DL_US7 DB Link를 생성하고 DB Link “dl_us7″을 지정하여 데이터를 가져올 수 있다. script는 다음과 같다.

CREATE DATABASE LINK DL_US7
CONNECT TO leg IDENTIFIED BY leg
USING '(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORAUS7)
    )
  )'
;

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

실행결과의 한글은 정상적으로 표시되지 않고 ?(물음표) 문자로 나타난다.

DB Link로 데이터를 가져오는 경우 server(이 경우 ORAUS7) character set은 client(이 경우 ORAMSWIN949)의 character set으로 변환된다.

US7ASCII에 저장된 한글은 잘못 저장되어 있으므로 MSWIN949로 변환할 수 없어서 물음표로 표시되는 것이다.

이 상황에서 ORAUS7의 한글 데이터를 정상적으로 표시하려면 다음과 같은 방법을 적용한다.

• 한글이 포함된 VARCHAR2 data type 컬럼이 character set 변환되지 않도록 UTL_RAW.CAST_TO_RAW 함수를 사용하여 ORAUS7에 View를 생성한다.
  • 한글이 포함된 컬럼에 UTL_RAW.CAST_TO_RAW 함수를 적용한다.
  • RAW data type은 character set을 변환하지 않는다.
• ORAMSWIN949에서는 위에서 생성한 View를 query하고, RAW data type을 다시 VARCHAR2로 변환하고 CONVERT 함수로 source character set과 destination character set을 지정하여 정상적인 한글로 변환한다.

ORAUS7 instance에서 VARCHAR2 data type을 RAW data type으로 변환하는 View를 아래 script로 생성한다.

-- ORAUS7에 View 생성
create or replace view vw_sub_mon_stat as
select use_mon
      --,line_num
      ,utl_raw.cast_to_raw(line_num) as line_num_raw
      ,sub_sta_id
      --,sub_sta_nm
      ,utl_raw.cast_to_raw(sub_sta_nm) as sub_sta_nm_raw
      ,ride_pasgr_num, alight_pasgr_num, work_dt, commt
  from sub_mon_stat
;

-- ORAMSWIN949: utl_raw.cast_to_varchar2, convert 함수 적용하여 한글 데이터 정상 표시
select use_mon
      ,convert(utl_raw.cast_to_varchar2(line_num_raw), 'KO16MSWIN949', 'KO16MSWIN949') line_num
      ,sub_sta_id
      ,convert(utl_raw.cast_to_varchar2(sub_sta_nm_raw), 'KO16MSWIN949', 'KO16MSWIN949') sub_sta_nm
      ,ride_pasgr_num, alight_pasgr_num, work_dt, commt
  from vw_sub_mon_stat@dl_us7 a
;

실행 결과는 다음과 같이 한글이 정상적으로 표시된다.

테스트 #2) ORAUTF 에서 ORAMSWIN949 데이터 가져오기

DB Link 상에서 MSWIN949에서 UTF8로 정상적으로 characterset이 변환되어 한글을 잘 가져온다.

-- ORAUTF에서 ORAMSWIN949 데이터 가져오기
select use_mon, line_num, sub_sta_id, sub_sta_nm, ride_pasgr_num, alight_pasgr_num, work_dt, commt
  from sub_mon_stat@dl_mswin949
;

실행결과는 다음과 같다.

테스트 #3) ORAUTF 에서 ORAUS7 데이터 가져오기

“테스트 #1) ORAMSWIN949 에서 ORAUS7 데이터 가져오기”와 동일하게 한글이 ?(물음표)로 표시된다.

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

실행결과는 다음과 같다.

한글을 정상적으로 표시하려면, “테스트 #1) ORAMSWIN949 에서 ORAUS7 데이터 가져오기” 단계에서 생성한 View는 그대로 사용하고, ORAUTF에서 DB Link를 통해 실행하는 SQL의 CONVERT 함수 parameter만 약간 변경하면 된다.

-- ORAUTF: utl_raw.cast_to_varchar2, convert 함수 적용하여 한글 데이터 정상 표시
select use_mon
      ,convert(utl_raw.cast_to_varchar2(line_num_raw), 'AL32UTF8', 'KO16MSWIN949') line_num
      ,sub_sta_id
      ,convert(utl_raw.cast_to_varchar2(sub_sta_nm_raw), 'AL32UTF8', 'KO16MSWIN949') sub_sta_nm
      ,ride_pasgr_num, alight_pasgr_num, work_dt, commt
  from vw_sub_mon_stat@dl_us7 a
;

CONVERT함수의 두번째 parameter가 destination character set을 의미하므로 AL32UTF8을 지정하였다.

실행 결과는 다음과 같다.

참고

RAW data type 살펴보기

RAW data type에 대한 oracle 문서 내용중 일부를 아래에 발췌해 둔다.

출처: https://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements001.htm#SQLRF50993

RAW and LONG RAW Data Types

The RAW and LONG RAW data types store data that is not to be explicitly converted by Oracle Database when moving data between different systems. These data types are intended for binary data or byte strings. For example, you can use LONG RAW to store graphics, sound, documents, or arrays of binary data, for which the interpretation is dependent on the use.

…

RAW is a variable-length data type like VARCHAR2, except that Oracle Net (which connects client software to a database or one database to another) and the Oracle import and export utilities do not perform character conversion when transmitting RAW or LONG RAW data In contrast, Oracle Net and the Oracle import and export utilities automatically convert CHAR, VARCHAR2, and LONG data between different database character sets, if data is transported between databases, or between the database character set and the client character set, if data is transported between a database and a client. The client character set is determined by the type of the client interface, such as OCI or JDBC, and the client configuration (for example, the NLS_LANG environment variable).

위 내용 중 색상을 반전한 문장의 의미는 다음과 같다.

• These data types are intended for binary data or byte strings
  –> RAW data type은 binary 데이터를 저장하는 용도이다.
• do not perform character conversion when transmitting RAW or LONG RAW data
  –> DB Link(Oracle Net)을 통해 RAW data type을 전송하는 경우 character set을 변환하지 않는다.

Character set 변환 함수 CONVERT

CONVERT 함수에 대한 oracle 문서 내용중 일부를 아래에 발췌해 둔다.

출처: https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions034.htm#SQLRF00620

CONVERT converts a character string from one character set to another.

  – The char argument is the value to be converted. It can be any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

  – The dest_char_set argument is the name of the character set to which char is converted.

  – The source_char_set argument is the name of the character set in which char is stored in the database. The default value is the database character set.

The return value for CHAR and VARCHAR2 is VARCHAR2. For NCHAR and NVARCHAR2, it is NVARCHAR2. For CLOB, it is CLOB, and for NCLOB, it is NCLOB.

주의사항

RAW data type은 2000 byte까지 사용 가능한다. Oracle 12c R2 이후 확장가능하나, 일반적으로는 2000 byte까지이다. US7ASCII에서 한글은 2 byte로 저장되므로 최대 한글 1000자 까지만 이 방법을 적용할 수 있다.

(영문자 숫자가 포함되어 있는 경우라면 변환 가능한 글자수는 늘어날 수 있다.)

2000 byte를 초과하는 데이터를 변환하려면 View에서 두 개 이상의 컬럼으로 나누고 View를 읽는 instance(예: ORAUTF)에서 convert후 합치는 방법이 가능할 것으로 보이나 테스트하지는 않았다.

변환 가능한 최대 byte 수에 주의가 필요하다.