Conversión de juego de caracteres de Oracle (10): 6.3. Cómo convertir el tipo CLOB a coreano

Echemos un vistazo al método de conversión de tipo coreano CLOB. En la base de datos del juego de caracteres US7ASCII, puede comprobar cómo convertir el tipo CLOB, incluido Hangul, a AL32UTF8 y el resultado de la conversión.

Esta es una continuación del artículo anterior.

Conversión de juegos de caracteres de Oracle (9): 6. Cómo convertir juegos de caracteres implementados por el usuario (2)

6.3. Cómo convertir el tipo CLOB a coreano

Conversión de conjuntos de caracteres de Oracle(8): 6. Cómo convertir un conjunto de caracteres implementado por el usuario (1)

En el artículo anterior, mencioné lo siguiente:

Como referencia, en el caso de la conversión de US7ASCII a AL32UTF8, las columnas de tipo CLOB no se convierten normalmente, por lo que se requiere un procesamiento por separado.
Se explicará en el siguiente artículo.

En este artículo, explicaremos cómo convertir el tipo CLOB, incluidos los caracteres coreanos, a AL32UTF8 utilizando DB Link de US7ASCII a AL32UTF8. Utilice DBMS_LOB, que es un paquete básico de Oracle.

Primero, verifiquemos los datos CLOB de tres instancias: ORAUS7, ORAMSWIN949 y ORAUTF.

6.3.1. Consultar datos CLOB

Llene la columna CLOB (REF_DES) con datos ejecutando la declaración INSERT a continuación en tres instancias: ORAUS7, ORAMSWIN949 y ORAUTF.

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'))
;

El tipo de datos de cada columna es el siguiente.

Ahora, verifiquemos los datos de la columna ref_des (tipo CLOB) en cada instancia con el siguiente SQL.

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

El conjunto de caracteres de los datos CLOB es US7ASCII y los caracteres coreanos no se muestran normalmente.

El número de caracteres (chr_len) es 3000 y el número de bytes (byte_len) también es 3000. Es decir, se puede ver que un carácter se almacena como un byte.

No pude encontrar una manera de configurar NLS_LANG en SQL Developer, así que lo ejecuté en sqlplus y confirmé que se mostraban los caracteres coreanos.

Si NLS_LANG se establece en US7ASCII como se muestra a continuación, se muestra el coreano.

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

El conjunto de caracteres de los datos CLOB es KO16MSWIN949 y los caracteres coreanos se muestran normalmente.

El número de caracteres (chr_len) es 1500 y el número de bytes (byte_len) es 3000. Es decir, se puede ver que un carácter se almacena como 2 bytes.

▼ ORAUTF

El conjunto de caracteres de los datos CLOB es AL32UTF8 y los caracteres coreanos se muestran normalmente.

El número de caracteres (chr_len) es 1334 y el número de bytes (byte_len) es 4000. Es decir, se puede ver que un carácter se almacena como 3 bytes.

(El número exacto de bytes es 4002, pero hay una pequeña diferencia porque el número máximo de bytes devuelto por dbms_lob.substr es 4000)

6.3.2. Conversión de datos CLOB

Los datos CLOB no pueden ejecutarse select a través de DB Link. Recibo el error "ORA-22992: no se puede usar la ubicación LOB seleccionada de la tabla remota".

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.

Puede copiar datos utilizando la sintaxis CTAS (Crear tabla como selección) de la siguiente manera.

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
;

Mirando el resultado de la ejecución de la consulta anterior, el conjunto de caracteres de la columna CLOB se convirtió a AL32UTF8, pero la cadena de caracteres coreanos no se convirtió normalmente.

El proceso de conversión de caracteres coreanos en una columna CLOB es el siguiente.

1. Cree una vista que convierta el tipo de datos de la columna CLOB a BLOB en la base de datos de origen (instancia #1, ORAUS7)
2. Crear tabla con sintaxis CTAS (Crear tabla como selección) en la base de datos de destino (Instancia #3, ORAUTF)
3. Cree una tabla convirtiendo el tipo de datos BLOB de la tabla creada anteriormente al tipo CLOB

Veámoslos uno por uno.

1. Cree una vista que convierta el tipo de datos de la columna CLOB a BLOB en la base de datos de origen (instancia #1, ORAUS7)

Si el tipo de datos de la columna CLOB se convierte a BLOB, el conjunto de caracteres no se convierte al consultar con DB Link.

Cree una función que convierta CLOB en BLOB utilizando el procedimiento DBMS_LOB.CONVERTTOBLOB de la siguiente manera.

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;

Luego, cree una vista usando la función anterior.

Para fines de prueba, solo se seleccionaron las filas con valores en algunas columnas y la columna 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. Cree una tabla con sintaxis CTAS (Crear tabla como selección) en la base de datos de destino (Instancia #3, ORAUTF)

Obtenga datos de DB Link con la siguiente sintaxis y cree una tabla.

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

3. Cree una tabla convirtiendo el tipo de datos BLOB de la tabla creada anteriormente al tipo CLOB

Cree una función que convierta BLOB en CLOB utilizando el procedimiento DBMS_LOB.CONVERTTOCLOB de la siguiente manera.

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;

Entre los parámetros del procedimiento DBMS_LOB.CONVERTTOCLOB, BLOB_CSID significa "El ID del conjunto de caracteres de los datos de origen".

Para obtener más detalles, consulte el documento a continuación.

DBMS_LOB – Procedimiento CONVERTTOCLOB (oracle.com)

BLOB_CSID debe especificarse como un valor entero, no como una cadena de caracteres, y el valor se puede conocer con la función NLS_CHARSET_ID.

Dado que el conjunto de caracteres de los datos originales es KO16MSWIN949, 846 se confirmó ejecutando lo siguiente.

SELECT NLS_CHARSET_ID('KO16MSWIN949')
  FROM DUAL;

Y puede crear o consultar una tabla usando la función anterior.

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
;

Puede verificar que Hangul se haya convertido normalmente de la siguiente manera.

---

Hasta este punto, hemos visto cómo convertir el tipo CLOB, incluidos los caracteres coreanos en la base de datos del juego de caracteres US7ASCII, a AL32UTF8.

Parece un poco complicado ya que se convierte a través de varios procesos, pero no hay nada difícil si lo miras paso a paso.

Hasta este artículo, la serie sobre la conversión de juegos de caracteres de Oracle ha terminado.

Espero poder ser de ayuda a alguien que lo necesite.