NVARCHAR2で定義したカラムに1000文字を超える全角文字を登録しようとしたらエラーになる（ORA-01461: LONG値はLONG列にのみバインドできます）
OracleのDBでNVARCHAR2で定義されたカラムに1000文字を超える全角文字を登録しようとしたらエラーが発生して登録できないことがありました。
画面での入力で文字数の制限はしていましたが、最初はその制限の処理が間違っていたのかと思いましたが実際は違いました。
目次
- エラーの内容
- 原因
- 解決策
- 最後に
エラーの内容
Oracleで出力されたエラーは以下の通り。
ORA-01461: LONG値はLONG列にのみバインドできます
エラーが出力された瞬間は、ぱっと見で、数値型のカラムに文字列でも突っ込んでしまったのかと思いました。
原因
原因はどうやら、VARCHAR2やNVARCHAR2で定義したカラムに格納できるデータサイズが4000バイトであるという点。
NVARCHAR2(2000)と定義したカラムがあったら、普通は2000文字まで登録できそうに思えますが、それはあくまで1文字が2バイト以下であった場合のみ。入力された文字が3バイトの文字であれば1333文字までで、UTF16のサロゲートペア（4バイト）のみ登録されるのであれば、1000文字までしかカラムに格納できません。
以前、VARCHAR2やNVARCHAR2の型を調べた時になんとなく二つの型の違いを分かった気になっていましたが、格納できるデータサイズについては、文字数で定義したとか関係なく、共通して4000”バイト”になります。
解決策
DBにOracleを使ったシステムで1000文字を超える全角文字を登録する場合、その文字を格納する型がVARCHAR2やNVARCHAR2だと上記のような問題が発生する可能性があるので、代わりにCLOBやNCLOBといったデータ型を使います。
CLOBやNCLOBに格納できるデータの最大サイズは4GB近くあるので、最大サイズを超えてしまう心配はいりません。
ただ、すでにNVARCHAR2で定義されている型をNCLOBに変更することはできないので、新しくNCLOBで定義したカラムを追加して、データを移動した後に、旧カラムを削除する必要があります。
NVARCHAR2の型からNCLOB型のカラムに変更する場合の手順。
-- カラム（NCLOB）追加
ALTER TABLE mytbl ADD (new_a_col NCLOB);
-- データ移行
UPDATE mytbl SET new_a_col = a_col;
-- カラム（NVARCHAR2）削除
ALTER TABLE mytbl DROP (a_col CASCADE CONSTRAINTS);
-- カラム名のリネーム
ALTER TABLE mytbl RENAME COLUMN new_a_col TO a_col;
上記のSQLを実行した場合、追加したカラムはもちろん一番下に行きます。
改修や障害対応の場合、分類で大まかにまとめてあったカラムの順番が崩れて見栄えが悪くなるので、テーブルの再作成をすべきかどうかいつも悩みますね。
追加時にカラムの位置を指定したり、後で位置を変更したりできたら非常に便利なんですが、多くのDBでそういったことはできないので、技術的に難しかったりするんでしょうか...
最後に
文字コードやカラムの型っていうのはシステムを作り込んでいくと必ず躓きます。しかも『こうやればいい』という正解が調べてもなかなか見つかりづらいので、面倒だからと敬遠せずに、問題があった時に文字コードや種類についてちゃんと学習しておくといいかもしれませんね。