Oracleのデーブルよりデータ取得の際に「ORA-06502」が発生してしまいます。

Question

###前提・実現したいこと
Oracle 12.1.0.2.0
Java 1.8
eclipse
上記開発環境でデータ移行のプログラムを作成していますが、表題のエラーが発生しているため、対応方法をお教えいただきたく。

###発生している問題・エラーメッセージ
移行元のテーブルからデータ取得を行うSQLを実行すると、以下エラーが発生しています。

ORA-06502 character string buffer too small

###該当のソースコード

### ddl
  CREATE TABLE "USER"."TBL_A" 
   ("ID" NUMBER(38,0), 
	"COL2" VARCHAR2(512 BYTE), 
	"COL3" VARCHAR2(512 BYTE), 
	"COL4" DATE, 
	"COL5" VARCHAR2(512 BYTE), 
	"COL6" VARCHAR2(512 BYTE), 
	"COL7" VARCHAR2(1024 BYTE), 
	"COL8" VARCHAR2(1024 BYTE), 
	"COL9" VARCHAR2(256 BYTE), 
	"COL10" VARCHAR2(128 BYTE), 
	"COL11" VARCHAR2(26 BYTE), 
	"COL12" VARCHAR2(512 BYTE), 
	"COL13" VARCHAR2(512 BYTE), 
	"COL14" VARCHAR2(512 BYTE), 
	"COL15" VARCHAR2(512 BYTE), 
	"COL16" VARCHAR2(512 BYTE), 
	"COL17" VARCHAR2(512 BYTE), 
	"COL18" CLOB, 
	"COL19" VARCHAR2(512 BYTE), 
	"COL20" VARCHAR2(512 BYTE), 
	"COL21" VARCHAR2(512 BYTE), 
	"COL22" VARCHAR2(512 BYTE), 
	"COL23" VARCHAR2(512 BYTE), 
	"COL24" VARCHAR2(512 BYTE), 
	"COL25" CLOB, 
	"COL26" VARCHAR2(2048 BYTE), 
	"COL27" CLOB, 
	"COL28" VARCHAR2(4000 BYTE), 
	"COL29" DATE, 
	"COL30" VARCHAR2(512 BYTE), 
	"COL31" VARCHAR2(4000 BYTE), 
	"COL32" DATE, 
	"COL33" VARCHAR2(512 BYTE), 
	"COL34" VARCHAR2(4000 BYTE), 
	"COL35" DATE, 
	"COL36" VARCHAR2(512 BYTE), 
	"COL37" VARCHAR2(4000 BYTE), 
	"COL38" DATE, 
	"COL39" VARCHAR2(512 BYTE), 
	"COL40" VARCHAR2(4000 BYTE), 
	"COL41" DATE, 
	"COL42" VARCHAR2(512 BYTE), 
	"COL43" VARCHAR2(4000 BYTE), 
	"COL44" DATE, 
	"COL45" VARCHAR2(512 BYTE), 
	"COL46" VARCHAR2(4000 BYTE), 
	"COL47" DATE, 
	"COL48" VARCHAR2(512 BYTE), 
	"COL49" VARCHAR2(4000 BYTE), 
	"COL50" DATE, 
	"COL51" VARCHAR2(512 BYTE), 
	"COL52" DATE, 
	"COL53" DATE, 
	"COL54" VARCHAR2(512 BYTE), 
	"COL55" CLOB
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TS" 
 LOB ("COL18") STORE AS SECUREFILE (
  TABLESPACE "TS" ENABLE STORAGE IN ROW CHUNK 8192
  NOCACHE LOGGING  NOCOMPRESS  KEEP_DUPLICATES 
  STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) 
 LOB ("COL25") STORE AS SECUREFILE (
  TABLESPACE "TS" ENABLE STORAGE IN ROW CHUNK 8192
  NOCACHE LOGGING  NOCOMPRESS  KEEP_DUPLICATES 
  STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) 
 LOB ("COL27") STORE AS SECUREFILE (
  TABLESPACE "TS" ENABLE STORAGE IN ROW CHUNK 8192
  NOCACHE LOGGING  NOCOMPRESS  KEEP_DUPLICATES 
  STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) 
 LOB ("COL55") STORE AS SECUREFILE (
  TABLESPACE "TS" ENABLE STORAGE IN ROW CHUNK 8192
  NOCACHE LOGGING  NOCOMPRESS  KEEP_DUPLICATES 
  STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ;

# JAVA
// 長々と書いていますが、要約すると、テーブル内の全データ取得
// 但し、CLOB型についてはnvl2でnullかどうかを判断した上で、substrを実施し取得するようにしています。
String sql = "select ID, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10, COL11, COL12, COL13, COL14, COL15, COL16, COL17, NVL2(COL18, dbms_lob.substr(COL18, 20000, 1), '') AS COL18, COL19, COL20, COL21, COL22, COL23, COL24, NVL2(COL25, dbms_lob.substr(COL25, 20000, 1), '') AS COL25, COL26, NVL2(COL27, dbms_lob.substr(COL27, 20000, 1), '') AS COL27, COL28, COL29, COL30, COL31, COL32, COL33, COL34, COL35, COL36, COL37, COL38, COL39, COL40, COL41, COL42, COL43, COL44, COL45, COL46, COL47, COL48, COL49, COL50, COL51, COL52, COL53, COL54, NVL2(COL55, dbms_lob.substr(COL55, 20000, 1), '') AS COL55 from TBL_A";

org.apache.commons.dbutils.QueryRunner qRunner = new QueryRunner();

// 問題が発生している箇所
List<Map<String, Object>> map = (List<Map<String, Object>>)qRunner.query(
                   conn, sql, new MapListHandler());

###試したこと
発生しているエラー"ORA-06502"についてネットで一通り調べましたが、どれも、定義より長い文字列で更新しようとしている際に発生しているようで、データ取得の際に発生しているような記述が見当たりませんでした。

また、実行しようとしているSQLをコンソールに出力し、SQLDevelopperで実行しましたが、問題なく、結果が返ってきます。

次に、"長さ"で思い当たるのはCLOB型の列に対して実施しているdbms_lob.substrのため、この第二引数を大きくしたり、小さくしたりしてみました。
2 -> エラーは発生しないが、先頭より2文字のみ返却される。(substrとしては正しい動き)
30000 -> エラー発生
32767 -> エラー発生
50000 -> エラーは発生しないが、null値で返却される

###補足情報(言語/FW/ツール等のバージョンなど)

Answer 1

DBMS_LOB.SUBSTRの結果が VARCHAR2データ型 の4,000バイトを超えているのではないでしょうか？
マルチバイト文字1文字はSJISでは2バイトですが、AL32UTF8などのUnicodeでは3バイトになるので、良く引っ掛かります。

Oracle12.1では初期化パラメータ MAX_STRING_SIZE = EXTENDED を指定することでVARCHAR2型で32,767バイトまで長さを指定できます。Javaも含むプログラムの修正は必要ですが。

サポート契約が有効でしたら、現状の DBMS_LOB.SUBSTR以外に DBMS_LOB.SUBSTRB を追加するようにサポート・センターに依頼した方が良いです。旧OTN-Japan掲示板 clobのselectでORA-06502エラー発生

Comments

[gwg2r4]

＞DBMS_LOB.SUBSTRの結果が VARCHAR2データ型 の4,000バイトを超えているのではないでしょうか？

そこかな、とも考えていましたが、substrの第二引数を50000にした際にエラーとならずに、null値で返却されるのが気になります。

＞Oracle12.1では初期化パラメータ MAX_STRING_SIZE = EXTENDED を指定することでVARCHAR2型で32,767バイトまで長さを指定できます。Javaも含むプログラムの修正は必要ですが。

現在、確認出来る限り、32,767バイトを超えるデータはなさそうなので、まずはその対応をしてみようかとおもいます。

余談ですが、今回、dbms_lob.substrを使用したのは、列名そのままでselectした後、.get("列名").toString() で値を取ろうとすると、おかしなデータが返却されたためです。

clob.getSubString(1, (int)clob.length()); で取得できるのでしょうか？
https://docs.oracle.com/javase/jp/6/api/java/sql/ResultSet.html

[Orlofsky]

MAX_STRING_SIZE はALTER SYSTEM で変更できます。
http://docs.oracle.com/cd/E57425_01/121/REFRN/GUID-D424D23B-0933-425F-BC69-9C0E6724693C.htm

＞substrの第二引数を50000にした際にエラーとならずに、null値...
の件はサポート契約が有効でしたら、サポート・センターに問合せた方が良いです。
わたしのJavaの能力は微々たるものなので gwg2r4さんの方でお試しください。

[abs123]

第2引数に32767より上の値を指定するとnullを返却する仕様みたいですよ