Mac De Oracle Heterogeneous! #65 Tweet

前回のつづき。Generic Connectivyty経由でMySQL4.0.25の文字型にアクセス。二回目。

１．マッピングされる型の確認

Generic Connectivity経由でMySQL4.0.25の文字型がOracleのどの型にマップされるかを確認。

１）char型

MySQL4.0.25でchar(20)、char(20) binary、char(255)、char(255) binaryとして定義した場合、Oracle側ではchar型にマッピングされる。

CORYDORAS> set null "null"
CORYDORAS> l
  1  select
  2    "r_char_20",
  3    lengthb("r_char_20") as "bytes",
  4    substr(
  5      dump("r_char_20",16),
  6      1,
  7      instr(dump("r_char_20",16),' ')
  8    ) as "dump"
  9  from
 10    char_test_mysql4025_mac@oracle10g_win
 11  where
 12*   "id" <= 5
CORYDORAS> /

r_char_20                                     bytes dump
---------------------------------------- ---------- ----------
 あ                                              20 Typ=96
null                                     null       null
                                                 20 Typ=96
12345678901234567890                             20 Typ=96
１２３４５６７８９０                             20 Typ=96

CORYDORAS> 
CORYDORAS> l
  1  select
  2    "r_char_20_binary",
  3    lengthb("r_char_20_binary") as "bytes",
  4    substr(
  5      dump("r_char_20_binary",16),
  6      1,
  7      instr(dump("r_char_20_binary",16),' ')
  8    ) as "dump"
  9  from
 10    char_test_mysql4025_mac@oracle10g_win
 11  where
 12*   "id" between 6 and 10
CORYDORAS> /

r_char_20_binary                              bytes dump
---------------------------------------- ---------- ----------
 あ                                              20 Typ=96
null                                     null       null
                                                 20 Typ=96
12345678901234567890                             20 Typ=96
１２３４５６７８９０                             20 Typ=96

CORYDORAS> 
CORYDORAS> l
  1  select
  2    "r_char_255",
  3    lengthb("r_char_255") as "bytes",
  4    substr(
  5      dump("r_char_255",16),
  6      1,
  7      instr(dump("r_char_255",16),' ')
  8    ) as "dump"
  9  from
 10    char_test_mysql4025_mac@oracle10g_win
 11  where
 12*   "id" = 11
CORYDORAS> /

r_char_255                                    bytes dump
---------------------------------------- ---------- ----------
****************************************        255 Typ=96
****************************************
****************************************
****************************************
****************************************
****************************************
***************

CORYDORAS> 
CORYDORAS> col r_char_255_binary for a40
CORYDORAS> l
  1  select
  2    "r_char_255_binary",
  3    lengthb("r_char_255_binary") as "bytes",
  4    substr(
  5      dump("r_char_255_binary",16),
  6      1,
  7      instr(dump("r_char_255_binary",16),' ')
  8    ) as "dump"
  9  from
 10    char_test_mysql4025_mac@oracle10g_win
 11  where
 12*   "id" = 11
CORYDORAS> /

r_char_255_binary                             bytes dump
---------------------------------------- ---------- ----------
****************************************        255 Typ=96
****************************************
****************************************
****************************************
****************************************
****************************************
***************

CORYDORAS> 

２）varchar型

MySQL4.0.25で、varchar(20)、varchar(20) binary、varchar(255)、varchar(255) binaryとして定義した場合、Oracle側では varchar2型にマッピングされる。

CORYDORAS> l
  1  select
  2    "r_varchar_20",
  3    lengthb("r_varchar_20") as "bytes",
  4    substr(
  5      dump("r_varchar_20",16),
  6      1,
  7      instr(dump("r_varchar_20",16),' ')
  8    ) as "dump"
  9  from
 10    varchar_test_mysql4025_mac@oracle10g_win
 11  where
 12*   "id" between 19 and 23
CORYDORAS> /

r_varchar_20                                  bytes dump
---------------------------------------- ---------- ----------
 あ                                               3 Typ=1
null                                     null       null
null                                     null       null
12345678901234567890                             20 Typ=1
１２３４５６７８９０                             20 Typ=1

CORYDORAS> 
CORYDORAS> l
  1  select
  2    "r_varchar_20_binary",
  3    lengthb("r_varchar_20_binary") as "bytes",
  4    substr(
  5      dump("r_varchar_20_binary",16),
  6      1,
  7      instr(dump("r_varchar_20_binary",16),' ')
  8    ) as "dump"
  9  from
 10    varchar_test_mysql4025_mac@oracle10g_win
 11  where
 12*   "id" between 24 and 28
CORYDORAS> /

r_varchar_20_binary                           bytes dump
---------------------------------------- ---------- ----------
 あ                                               3 Typ=1
null                                     null       null
null                                     null       null
12345678901234567890                             20 Typ=1
１２３４５６７８９０                             20 Typ=1

CORYDORAS> 
CORYDORAS> col r_varchar_255 for a40
CORYDORAS> l
  1  select
  2    "r_varchar_255",
  3    lengthb("r_varchar_255") as "bytes",
  4    substr(
  5      dump("r_varchar_255",16),
  6      1,
  7      instr(dump("r_varchar_255",16),' ')
  8    ) as "dump"
  9  from
 10    varchar_test_mysql4025_mac@oracle10g_win
 11  where
 12*   "id" = 29
CORYDORAS> /

r_varchar_255                                 bytes dump
---------------------------------------- ---------- ----------
****************************************        255 Typ=1
****************************************
****************************************
****************************************
****************************************
****************************************
***************

CORYDORAS>
CORYDORAS> col r_varchar_255_binary for a40
CORYDORAS> l
  1  select
  2    "r_varchar_255_binary",
  3    lengthb("r_varchar_255_binary") as "bytes",
  4    substr(
  5      dump("r_varchar_255_binary",16),
  6      1,
  7      instr(dump("r_varchar_255_binary",16),' ')
  8    ) as "dump"
  9  from
 10    varchar_test_mysql4025_mac@oracle10g_win
 11  where
 12*   "id" = 29
CORYDORAS> /

r_varchar_255_binary                          bytes dump
---------------------------------------- ---------- ----------
****************************************        255 Typ=1
****************************************
****************************************
****************************************
****************************************
****************************************
***************

CORYDORAS> 

今日はここまで。