[Oracle]TO_CHAR関数を使って文字データに変換すると定義長はどうなる？

TO_CHAR 関数を使って文字データへの変換を行うと変換対象が収まる十分な定義長の VARCHAR 値に変換されます。

主なデータ型についてみてみると、以下のような桁数となります。

NUMBER	数値書式に応じた長さ ※ 数値書式省略時は有効桁数 + 2
DATE	日付書式に応じた長さ ※ 日付書式省略時はデフォルト日付書式に応じた長さ
NCHAR、NVARCHAR2	元の長さ×10 ※ 元の長さが400 以上の場合、4,000
CLOB、NCLOB	4,000

NUMBER 型や DATE 型の値を変換する際には定義長は特に問題にはならないと思いますが、NCHAR 型、NVARCHAR2型、CLOB 型、NCLOB 型などを変換する場合には定義長がかなり大きくなってしまうため、使用するアプリケーションによっては注意が必要です。

例えば、Java で Oracle JDBC Thin ドライバを使ってこのようなデータを取得しようとすると

java.sql.SQLException: 型の長さが最大を超えています。

が発生することがあります。

このエラーはおそらくバインドサイズの制限に起因する問題かと思われます。


このように、TO_CHAR 関数を使って文字データへの変換を行った結果、必要以上に定義長が大きくなり、不都合が生じる場合は、SUBSTR 関数を使って文字データを切り取るなどの工夫が必要になります。

例えば、以下のように NVARCHAR2(400) の文字データを TO_CHAR 関数を使って文字データに変換すると VARCHAR2(4000) となりますが、

CREATE TABLE TEST ( COL_NVARCHAR2_400 NVARCHAR2(400) )
/
CREATE OR REPLACE VIEW V_TEST AS 
  SELECT TO_CHAR( COL_NVARCHAR2_400 ) COL_VARCHAR2_400 FROM TEST
/

名前                              NULL?    型
--------------------------------- -------- ---------------

COL_NVARCHAR2_400                          NVARCHAR2(4000)

SUBSTR 関数を使って 400 で切ってやれば 400 となります。

CREATE OR REPLACE VIEW V_TEST AS 
  SELECT SUBSTR( TO_CHAR( COL_NVARCHAR2_400 ), 1, 400 ) COL_VARCHAR2_400 FROM TEST
/

名前                              NULL?    型
--------------------------------- -------- ---------------

COL_NVARCHAR2_400                          NVARCHAR2(400)

しかし．．．NVARCHAR2 から VARCHAR2 に変換するとなぜ10倍になるのでしょう？

摩訶不思議なり．．．