Oracle Character Set変換（10）：6.3。 CLOB type ハングル変換方法

CLOB type ハングル変換方法を見てみましょう。 US7ASCII character set DBでハングルを含むCLOB typeをAL32UTF8に変換する方法と変換結果を確認することができる。

前の記事で続く内容だ。

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

6.3。 CLOB type ハングル変換方法

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

上記の記事では、次のようなことを言及しました。

参考までに、US7ASCIIからAL32UTF8への変換の場合は、CLOBタイプの列が正常に変換されず、別途処理が必要です。
次の記事で説明する予定です。

この記事では、US7ASCIIからAL32UTF8にDB Linkを使用してハングルを含むCLOBタイプをAL32UTF8に変換する方法について説明します。 Oracleの基本パッケージであるDBMS_LOBを活用します。

まず、ORAUS7、ORAMSWIN949、ORAUTFの3つのインスタンスでCLOBデータを確認してみましょう。

6.3.1. CLOBデータの確認

ORAUS7、ORAMSWIN949、ORAUTF 3つのインスタンスで以下の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'))
;

各列のデータ型は以下の通りです。

次に、次のSQLで各インスタンスで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データのキャラクタセットはUS7ASCIIであり、ハングルは正常に表示されません。

文字数（chr_len）は3,000であり、バイト数（byte_len）も3,000です。つまり、1文字が1バイトで格納されることがわかります。

SQL DeveloperでNLS_LANGを設定する方法が見つからず、sqlplusで実行してハングルが表示されることを確認しました。

以下のようにNLS_LANGをUS7ASCIIに設定すると、ハングルが表示されます。

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データのキャラクタセットはKO16MSWIN949で、ハングルは正常に表示されます。

文字数（chr_len）は1,500、バイト数（byte_len）は3,000です。つまり、1文字が2バイトで格納されることがわかります。

▼ORAUTF

CLOBデータのキャラクタセットはAL32UTF8であり、ハングルは正常に表示されます。

文字数（chr_len）は1,334、バイト数（byte_len）は4,000です。つまり、1文字が3バイトで格納されることがわかります。

（正確なバイト数は4,002ですが、dbms_lob.substrで返される最大バイト数は4,000なので少し違いがあります）

6.3.2。 CLOBデータ変換

CLOBデータはDB Linkを介してselectを実行できません。 「ORA-22992：リモート表から選択されたLOB位置が使用できません」エラーが発生します。

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.

次のように、Create Table As Select（CTAS）構文を使用してデータをコピーできます。

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に変換されましたが、ハングル文字列は正常に変換されませんでした。

CLOB列のハングルを変換するプロセスは次のとおりです。

1. Source DB（Instance #1、ORAUS7）でCLOB列のデータ型をBLOBに変換したViewの生成
2. Target DB (Instance #3, ORAUTF) で CTAS (Create Table As Select) 構文でテーブルを作成する
3. 上記で作成した表のBLOBデータ型をCLOB型に変換して表を作成する

一つずつ見てみよう。

1. Source DB (Instance #1, ORAUS7) で CLOB カラムの data type を BLOB に変換した View の生成

CLOB列のデータ型をBLOBに変換すると、DB Linkにクエリするときにキャラクタセットは変換されません。

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からデータをインポートしてテーブルを作成します。

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のパラメータ中のBLOB_CSIDは、「ソースデータのキャラクタセットID（The character set ID of the source data）」を意味します。

詳細については、以下の文書を参照してください。

DBMS_LOB – CONVERTTOCLOB Procedure (oracle.com)

BLOB_CSIDは文字列ではなく整数型で値を指定する必要があり、NLS_CHARSET_ID関数でその値を知ることができます。

元データのキャラクタセットがKO16MSWIN949なので、次のように実行して846を確認しました。

SELECT NLS_CHARSET_ID('KO16MSWIN949')
  FROM DUAL;

そして、上記の関数を使ってテーブルを作成またはクエリすることができます。

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
;

以下のようにハングルが正常に変換されたことを確認することができる。

---

これまで、US7ASCII character set DBでハングルを含むCLOB typeをAL32UTF8に変換する方法を見てきました。

複数の過程を経て変換してみると少し複雑に見えるが、一段階ずつ見ると難しいことはない。

この記事まで、Oracle Character set変換の連載を完了します。

必要な人に少しでも助けてほしい。