Oracle Databaseでは可変長文字列を格納するデータ型としてVARCHAR2を使います。大規模データにはCLOB型を使うこともあります。またNVARCHAR2型やNCLOB型を使うこともあります。
ここではVARCHAR2型の制限について書いてみます。
VARCHAR2型の制限
Oracle Database 12c Release 2のリファレンス・マニュアル(E72905-02)にはVARCHAR2型の制限について以下のように書かれています。
最大サイズ: 4000バイト、または32767バイト(MAX_STRING_SIZE初期化パラメータがEXTENDEDに設定されている場合)
セマンティックスの違い
Oracle Databaseの文字列型の指定には、最大バイト数を指定する「バイト・セマンティックス」と文字数を指定する「キャラクタ・セマンティクス」を選択することができます。
以下の例ではtable1テーブルの作成ではキャラクタ・セマンティクスを、table2テーブルの作成ではバイト・セマンティクスを指定しています。
SQL> CREATE TABLE table1(c1 VARCHAR2(10 CHAR)) ;
Table created.
SQL> CREATE TABLE table2(c1 VARCHAR2(10 BYTE)) ;
Table created.
省略時の値は、初期化パラメーターnls_length_semanticsに依存し、デフォルト値はBYTEです。
データベースの格納文字コードがAL32UTF8の場合（UTF-8）、漢字のバイト数は3バイトまたは4バイトになります。このためすべて漢字データの場合、VARCHAR2型の列に格納できるのは1000文字までになる可能性があります。
最大バイト数が4,000と言いながら、Oracle Database 12cは下記のようにCREATE TABLE文でVARCHAR2(4000 CHAR) の指定を許します。
SQL> SHOW PARAMETER max_string_size ;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_string_size string STANDARD
SQL> CREATE TABLE table3(c1 VARCHAR(4000 CHAR)) ;
Table created.
SQL> DESCRIBE table3
Name Null? Type
----------------------------------------- -------- ----------------------------
C1 VARCHAR2(4000 CHAR)
下記の例はtable3テーブルのc1列に漢字を1,024文字（=3,072バイト）格納しています。
この文字列を２倍に拡大しようとして失敗しています。
SQL> SELECT LENGTH(c1), LENGTHB(c1) FROM table3 ;
LENGTH(C1) LENGTHB(C1)
---------- -----------
1024 3072
SQL> UPDATE table3 SET c1=c1 || c1 ;
UPDATE table3 SET c1=c1 || c1
*
ERROR at line 1:
ORA-01489: result of string concatenation is too long
テーブル定義上は4,000文字格納できますが、実際にはそこまで格納できないことがわかります。
このため、列定義にキャラクタ・セマンティクスを使用する場合は以下の点に注意が必要です。
- 4,000バイトを超えないことを確認する
- 初期化パラメーターmax_string_sizeをEXTENDEDに指定する
- CLOB / NCLOB型を使う
max_string_size = EXTENDED の影響
初期化パラメーターmax_string_sizeをEXTENDEDに指定することで、VARCHAR2型の最大長は32 KBまで拡大することができます。内部的にはBLOBデータになりますが、アプリケーションからはVARCHAR2型として認識されます。そこでOracle Database Client 11gからVARCHAR2(5000) 列のデータを検索してみました。
$ sqlplus SCOTT/pass@db12c
SQL*Plus: Release 11.2.0.4.0 Production on Thu Nov 16 15:47:28 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> DESCRIBE data1
名前 NULL? 型
----------------------------------------- -------- ----------------------------
C1 VARCHAR2(5000)
SQL> SELECT LENGTHB(c1) FROM data1 ;
LENGTHB(C1)
-----------
4096
SQL> SELECT c1 FROM data1 ;
SELECT c1 FROM data1
*
ERROR at line 1:
ORA-24920: column size too large for client
上記の結果から、4,000バイトを超えるVARCHAR2型の列は旧バージョンのクライアントからは検索不可能であることがわかります。Oracle Database 11gのJDBC Driverを使ったアプリケーションでも同様の動作になりました。