Oracle DatabaseのIN句で『ORA-01795: リストに指定できる式の最大数は1000です。』のエラーになる

japan.zdnet.com

　Bardは、Googleの実験的な会話型AIチャットサービスだ。「ChatGPT」と同じように機能するように設計されているが、最大の違いは、Bardがすべての情報をウェブから取得することだ。

「Google Bard」とは--「ChatGPT」対抗でグーグルが発表したAIチャットボット - ZDNET Japan

　LaMDAはGoogleの「Transformer」上に構築されている。Transformerは、Googleが発明し、2017年にオープンソース化したニューラルネットワークアーキテクチャーだ。Googleによると、興味深いことに、ChatGPTの基盤となる言語モデル「GPT-3」もTransformer上に構築されているという。

「Google Bard」とは--「ChatGPT」対抗でグーグルが発表したAIチャットボット - ZDNET Japan

⇧ う～む、コアな部分は一緒なんか。

Oracle DatabaseのIN句で『ORA-01795: リストに指定できる式の最大数は1000です。』のエラーになる

Oracle Databaseに限らないかもしれないですが、SELECT文のIN句にJavaのリストを引数で渡したら、表題のエラーが起こった話。

Apache Commons DBUtils というライブラリとOracle Database 19cで試してます。

ちなみに、取得しようとしたテーブルのレコード数は、たかだか999904件で、100万件に満たないという状況です。

上記テーブルへは、

BEGIN
    FOR v_LoopCounter IN 100..1000000 LOOP
        INSERT INTO USERS (user_id, insert_user, insert_date) 
            VALUES (TO_CHAR(v_LoopCounter),'admin',CURRENT_TIMESTAMP);
        COMMIT;
        INSERT INTO USER_DETAIL (user_detail_id, user_id, last_name, first_name, gender, birthday, insert_user, insert_date, delete_flg) 
            VALUES (TO_CHAR(v_LoopCounter),TO_CHAR(v_LoopCounter),'last-name-'|| TO_CHAR(v_LoopCounter),'first-name-'|| TO_CHAR(v_LoopCounter),'man', CURRENT_DATE, 'admin',CURRENT_TIMESTAMP, '0');
        COMMIT;        
    END LOOP;
END;
/

⇧ 適当にダミーデータを投入してます。

今回、試してるのは、

ts0818.hatenablog.com

⇧ 上記のソースコードに手を加えたものです。

■エラーの出るソースコード

package com.dao;

import java.sql.SQLException;
import java.util.List;
import java.util.Objects;
import java.util.stream.Collectors;

import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.GenerousBeanProcessor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Component;

import com.entity.UserDetail;

@Component
//@RequiredArgsConstructor
public class UserDetailDaoImpl {
	
	@Autowired
	@Qualifier(value = "queryRunner")
	private QueryRunner queryRunner;
	
	public List<UserDetail> findAll() throws SQLException {
		ResultSetHandler<List<UserDetail>> resultSetHandler =
                new BeanListHandler<UserDetail>(UserDetail.class, new BasicRowProcessor(new GenerousBeanProcessor()));
		final List<UserDetail> userDetailList = queryRunner.query("select * from user_detail", resultSetHandler);
		return userDetailList;
	}
	
	public List<UserDetail> findByIds(List<String> ids) throws SQLException {
		ResultSetHandler<List<UserDetail>> resultSetHandler =
                new BeanListHandler<UserDetail>(UserDetail.class, new BasicRowProcessor(new GenerousBeanProcessor()));

		List<String> notNullIds = ids.stream()
				.filter(Objects::nonNull)
				.collect(Collectors.toList());
		
		// SQL文の作成
		StringBuilder sbSql = new StringBuilder();
		  sbSql.append("SELECT ")
		    .append("* ")
		    .append("FROM ")
		    .append("user_detail ")
		    .append("WHERE ")
            .append("user_detail_id IN (%s) ")
            .append("AND insert_user = ?");

		// プレースホルダー設定（SQLインジェクション対策）
		String sbSqlInPlaceFolder = String.format(sbSql.toString(), notNullIds.stream()
		    .map(v -> "?")
            .collect(Collectors.joining(", ")));
		 
		// プリペアードステートメントに設定する値
		notNullIds.add("admin");
		Object[] sqlParams = notNullIds.toArray();
		
		// SELECT文を実行
		final List<UserDetail> userDetailList = queryRunner.query(sbSqlInPlaceFolder.toString(), resultSetHandler, sqlParams);
		return userDetailList;
	}

}

で、実行すると、以下のようにエラーになりますと。

IN句が実用に耐えないということが分かったのですが、検索条件がJavaのリストで渡ってくる想定なので、EXISTS句に書き換えるということはできない、困った...

同様の事象は、昔からあったようです（というか、改善はする気がないんか、Oracle Database...）。

まさかとは思うけど、

「1000のバイオリン」（せんのバイオリン）は、日本のロックバンド、THE BLUE HEARTSの通算15枚目のシングル。

1000のバイオリン - Wikipedia

⇧ THE BULE HEARTSの「1000のバイオリン」をリスペクトしての、1000件の制限なのか？

だとしたら、改善しないでも致し方ない！

例え、Oracle Databaseの利用者が解決のために馬鹿馬鹿しくも貴重な時間を浪費して苦しむしかないとしても許すしかないではないか...

話が脱線しましたが、で、

www.joshlifejourney.com

⇧ 上記サイト様が、回避策を提示してくれてました。

試したソースコード

package com.dao;

import java.sql.SQLException;
import java.util.List;
import java.util.Objects;
import java.util.stream.Collectors;

import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.GenerousBeanProcessor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Component;

import com.entity.UserDetail;

@Component
//@RequiredArgsConstructor
public class UserDetailDaoImpl {
	
	@Autowired
	@Qualifier(value = "queryRunner")
	private QueryRunner queryRunner;
	
	public List<UserDetail> findAll() throws SQLException {
		ResultSetHandler<List<UserDetail>> resultSetHandler =
                new BeanListHandler<UserDetail>(UserDetail.class, new BasicRowProcessor(new GenerousBeanProcessor()));
		final List<UserDetail> userDetailList = queryRunner.query("select * from user_detail", resultSetHandler);
		return userDetailList;
	}
	
	public List<UserDetail> findByIds(List<String> ids) throws SQLException {
		ResultSetHandler<List<UserDetail>> resultSetHandler =
                new BeanListHandler<UserDetail>(UserDetail.class, new BasicRowProcessor(new GenerousBeanProcessor()));
		
		List<String> notNullIds = ids.stream()
				.filter(Objects::nonNull)
				.collect(Collectors.toList());
		
		// SQL文の作成
		StringBuilder sbSql = new StringBuilder();
		  sbSql.append("SELECT ")
		    .append("* ")
		    .append("FROM ")
		    .append("user_detail ")
		    .append("WHERE ")
            .append("(user_detail_id, '1') IN (%s) ")
            .append("AND insert_user = ?");

		// プレースホルダー設定（SQLインジェクション対策）
		String placeHolder = notNullIds.stream()
					.map(v -> "(?, ?)")
					.collect(Collectors.joining(", "));
		String sbSqlInPlaceFolder = String.format(sbSql.toString(), placeHolder);

		 // プリペアードステートメントに設定する値
		List<String> bindParamList = notNullIds.stream()
//				.map(id -> String.join("", "'", id, "'"))
				.collect(Collectors.toList());
		
		int addIndex = 0;
		int beforeListSize = bindParamList.size();
		for (int index = 0; index < bindParamList.size(); index++) {
			if (index % 2 == 0) {
				addIndex = index;
				if (index != (beforeListSize - 1) * 2) {
					continue;
				}
			}
			addIndex++;
			bindParamList.add(addIndex, "1");
			if (index == (beforeListSize - 1) * 2) {
				break;
			}
		}
		
		bindParamList.add("admin");
		Object[] sqlParams = bindParamList.toArray();
		
		// SELECT文を実行
		final List<UserDetail> userDetailList = queryRunner.query(sbSqlInPlaceFolder.toString(), resultSetHandler, sqlParams);
		return userDetailList;
	}
	
}

はい、エラー。

『java.sql.SQLException: ORA-03146: TTCフィールドのバッファ長が無効です』って言われてもね...