Oracle Character Set 変換(9): 6. ユーザー実装 Character Set 変換方法 (2)

前の記事（Oracle Character Set 変換(8): 6. ユーザー実装 Character Set 変換方法 (1)）に続いて、ユーザー実装のキャラクタセットの変換方法について説明します。 DB Link、CTAS、UTL_RAWパッケージを活用してKO16MSWIN949、US7ASCIIからAL32UTF8に変換する方法を確認できます。

6.2.3。方法3）DBリンク＆CTAS＆UTL_RAW

DB Linkを介してデータをインポートしながらCharacter setを変換する方法です。

1つのサーバーに3つのDBインスタンスを構成し、3つのテストを実行します。

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データ型列がキャラクタセットに変換されないように、UTL_RAW.CAST_TO_RAW関数を使用してORAUS7にビューを作成します。
  • ハングルを含む列にUTL_RAW.CAST_TO_RAW関数を適用します。
  • RAWデータ型は文字セットを変換しません。
• ORAMSWIN949では、上記で作成したViewをクエリし、RAWデータ型をVARCHAR2に変換し、CONVERT関数にsource character setとdestination character setを指定して通常のハングルに変換します。

ORAUS7 instanceで、VARCHAR2データ型をRAWデータ型に変換するViewを以下のスクリプトに生成します。

-- 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関数パラメータだけ少し変更すればよい。

-- 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関数の2番目のパラメータはdestination character setを意味するので、AL32UTF8を指定しました。

実行結果は以下の通りである。

メモ

RAWデータ型を見る

RAWデータ型の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 transportd 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データ型は、バイナリデータを格納するための用途です。
• do not perform character conversion when transmitting RAW or LONG RAW data
  - > DB Link（Oracle Net）を介してRAWデータ型を転送する場合は、文字セットを変換しません。

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データタイプは2000バイトまで利用可能です。 Oracle 12c R2以降は拡張可能ですが、通常は2000バイトまでです。 US7ASCIIでは、ハングルは2バイトで保存されるため、最大ハングル1000文字までのみこの方法を適用できます。

（英字の数字が含まれている場合、変換可能な文字数は増えることができる。）

2000バイトを超えるデータを変換するには、Viewから2つ以上の列に分割し、Viewを読み取るインスタンス（ORAUTFなど）で変換後に結合する方法が可能であるとは見えませんでした。

変換可能な最大バイト数に注意が必要です。