最大文字数の実験でRPAD関数にやられた話
今回は図らずも発見したオラクルはRPAD関数を使う際の注意点について。
概要
オラクルにおける文字列データ型は
最大が4000バイトであるが
たとえばvarchar2(4000 CHAR)
のように、CHARを指定するとバイトではなく、文字数に変更される。
しかして、マルチバイト文字、たとえばひらがなを4000文字格納するためにはmax_string_sizeパラメータをSTANDARDからEXTENDEDに変更する必要がある。なお、一度EXTENDEDにするとSTANDARDには戻せないので注意。
この最大文字数拡張機能を試すために作成したテーブルに4000文字以上のひらがなをINSERTする際にRPAD関数を使用したら想定外の動作をしたので気をつけないといけないことを発見したという話。
結論は、RPAD関数で例えば4000文字のひらがなを指定しても 実際にはその半分の2000文字しか入らない。8000文字のひらがなを指定して初めて4000文字入る。このあたりの説明はドキュメントにも一応それらしき記載があった。
環境
oracle 12c ( Single Instance )
下準備
max_string_sizeの設定の変更
公式ドキュメントを参考に設定の変更を実施する。
非CDBのVARCHAR2、NVARCHAR2およびRAW列の最大サイズを増加する
非CDBでVARCHAR2、NVARCHAR2およびRAW列の最大サイズを増加するには、次の手順に従います。
1 データベースの停止
2 UPGRADEモードでデータベースを再起動します。
3 MAX_STRING_SIZEの設定をEXTENDEDに変更します。
4 rdbms/admin/utl32k.sqlスクリプトを実行します。このスクリプトを実行するには、AS SYSDBAと接続している必要があります。
5 データベースをNORMALモードで再起動します。
以下、実際のコマンド。
connect / as sysdba
shutdown immediate;
startup upgrade;
show parameter max_strings_size
--> STANDARDになっていることを確認
alter system set MAX_STRING_SIZE=extended;
@?/rdbms/admin/utl32k.sql
shutdown immediate;
startup;
show parameter max_strings_size
--> EXTENDEDになっていることを確認
テスト表の作成
まずは挙動確認のために小さい文字数で作成する。
SQL> create table testuser.testtbl1 ( c1 varchar2(5 char ) ) tablespace test_tblsp;
表が作成されました。
SQL> desc testuser.testtbl1
名前 NULL? 型
----------------------------------------- -------- ----------------------------
C1 VARCHAR2(5 CHAR)
実験１：BYTEではなく文字数で指定長データが入ることを確認
ひらがな（マルチバイト）、アルファベット（バイト）関わらず、5文字入ることを確認する。
SQL> insert into testuser.testtbl1 values ('あいうえお');
1行が作成されました。
SQL> commit;
コミットが完了しました。
SQL> select * from testuser.testtbl1;
C1
---------------
あいうえお
SQL> insert into testuser.testtbl1 values ('abcde');
1行が作成されました。
SQL> commit;
コミットが完了しました。
SQL> select * from testuser.testtbl1;
C1
---------------
あいうえお
abcde
SQL> insert into testuser.testtbl1 values ('abcdef');
insert into testuser.testtbl1 values ('abcdef')
*
行1でエラーが発生しました。:
ORA-12899: 列"TESTUSER"."TESTTBL1"."C1"の値が大きすぎます(実際: 6、最大: 5)
BYTE, ひらがな共に５文字入った。よしよし。あらかわ。
実験２：マルチバイト（ひらがな）が4000文字以上入ることを確認する
さて本題。
MAX_STRING_SIZEをEXTENDEDにしてあるので、たとえばvarchar2(4000 CHAR)にすればひらがなも4000文字入るはずである。せっかくEXTENDEDにしたので5000文字で試す。
-- 実験結果をわかりやすくするために一度データを消しておく
SQL> truncate table testuser.testtbl1;
表が切り捨てられました。
-- カラムサイズの変更。4000以上である値を指定できるようになっていることを確認
SQL> alter table testuser.testtbl1 modify c1 varchar2(5000 char );
表が変更されました。
SQL> desc testuser.testtbl1
名前 NULL? 型
----------------------------------------- -------- ----------------------------
C1 VARCHAR2(5000 CHAR)
-- レコードのINSERT。
SQL> insert into testuser.testtbl1 values (rpad('あ',5000,'い'));
1行が作成されました。
SQL> commit;
コミットが完了しました。
SQL> select lengthb(c1), length(c1) from testuser.testtbl1;
LENGTHB(C1) LENGTH(C1)
----------- ----------
5000 2500
上記の結果から、想定ではRPAD関数を用いて5000文字ひらがなが入るはずが、実際には2500文字しか入っていないことがわかる。
にしてもRPAD関数、5000文字入らないんならエラー返して欲しい。
。。。
あれ、まてよ。ちょ、まてよ。チョ、マテヨ。
エラー出てないってことは。。。！？
SQL> insert into testuser.testtbl1 values (rpad('あ',5001,'い'));
1行が作成されました。
SQL> commit;
コミットが完了しました。
SQL> select lengthb(c1), length(c1) from testuser.testtbl1;
LENGTHB(C1) LENGTH(C1)
----------- ----------
5000 2500
5001 2501 <-- これ
SQL>
5001にしたら文字数は2501文字INSERTされた。 5000文字で実際には2500文字のINSERTだったので 10000文字を指定すれば10000文字入る気がする。
というわけで実験。
SQL> truncate table testuser.testtbl1;
表が切り捨てられました。
SQL> select lengthb(c1), length(c1) from testuser.testtbl1;
レコードが選択されませんでした。
SQL> insert into testuser.testtbl1 values (rpad('あ',10000,'い'));
1行が作成されました。
SQL> commit;
コミットが完了しました。
SQL> select lengthb(c1), length(c1) from testuser.testtbl1;
LENGTHB(C1) LENGTH(C1)
----------- ----------
10000 5000
おおお、予想通り5000文字入った。
ちなみにこれが上限であることも確認しておく。
SQL> insert into testuser.testtbl1 values (rpad('あ',10001,'い'));
insert into testuser.testtbl1 values (rpad('あ',10001,'い'))
*
行1でエラーが発生しました。:
ORA-12899: 列"TESTUSER"."TESTTBL1"."C1"の値が大きすぎます(実際: 5001、最大:
5000)
SQL>
よしよし。あらかわ。
追加実験：ひらがなの最大文字数の実験
最後に、EXTENDEDにより最大32767バイトに拡張されるため ひらがなが何文字入るのかを試す。
SQL> truncate table testuser.testtbl1;
表が切り捨てられました。
SQL> desc testuser.testtbl1
名前 NULL? 型
----------------------------------------- -------- ----------------------------
C1 VARCHAR2(5000 CHAR)
--カラムサイズの変更
SQL> alter table testuser.testtbl1 modify c1 varchar2( 32767 char );
表が変更されました。
SQL> desc testuser.testtbl1
名前 NULL? 型
----------------------------------------- -------- ----------------------------
C1 VARCHAR2(32767 CHAR)
SQL> insert into testuser.testtbl1 values (rpad('あ',65534,'い'));
1行が作成されました。
SQL> commit;
コミットが完了しました。
-- 限界値として32767*2+1=65535を指定
SQL> insert into testuser.testtbl1 values (rpad('あ',65535,'い'));
1行が作成されました。
-- エラーなくINSERTされてしまった。。。
SQL> commit;
コミットが完了しました。
-- 結果を見ると文字数的には16384文字が最大のよう。
SQL> select lengthb(c1), length(c1) from testuser.testtbl1;
LENGTHB(C1) LENGTH(C1)
----------- ----------
32767 16384
32767 16384
--数を増やしてもエラーは出ず。
SQL> insert into testuser.testtbl1 values (rpad('あ',65538,'い'));
1行が作成されました。
SQL> commit;
コミットが完了しました。
SQL> select lengthb(c1), length(c1) from testuser.testtbl1;
LENGTHB(C1) LENGTH(C1)
----------- ----------
32767 16384
32767 16384
32767 16384
SQL>
最大文字数以上を指定してもRPAD関数はエラーを出さない模様。 これはつらい。
まとめ
公式ドキュメントを読んでみたところ、この指定した文字数入らない現象はおそらく以下の記述に当てはまると思われる。
引数nは、戻り値が画面に表示される場合の全体の長さです。ほとんどのキャラクタ・セットでは、戻り値の文字数です。ただし、一部のマルチバイト・キャラクタ・セットでは、文字列の表示長はその文字列の文字数と異なることがあります。
こういった最大値の実験をする際には手順を簡略化するために関数を使用することが多いと思うが、こういった事態もあるので注意されたい。 特に、追加実験でも示した通り、最大バイト数を指定している場合は、RPADで最大バイト数*2以上を指定してもエラーが返らないので要注意である。
もっとも、実験１の時にも同様に関数を使用していたらもっと早くに気づけたというのが反省点ではある。
SQL> insert into testuser.testtbl1 values (’あいうえお’);
1行が作成されました。
SQL> insert into testuser.testtbl1 values (rpad('あ',5,'い'));
1行が作成されました。
SQL> commit;
コミットが完了しました。
SQL> select * from testuser.testtbl1;
C1
---------------
あいうえお
あい
SQL>
なお、MAX_STRING_SIZEがEXTENDEDの状態で格納できる最大バイト数である32767を指定した場合は、ひらがな（マルチバイト）の場合はその半分の16384文字格納できることが分かった。
参考資料
MAX_STRING_SIZE - Oracle® Databaseリファレンス
12c リリース1 (12.1)
RPAD関数 - Oracle® Database SQL言語リファレンス
12cリリース1 (12.1)