近期，有一个批量插入的sql，突然报出错误“ORA-01745: invalid host/bind variable name（无效的主机/绑定变量名称错误）”。 字面意思排查，可能原因是：使用了保留字、多余或者缺少冒号或者逗号等等语句的问题。
经过仔细核对和测试，单条插入都可以正常执行，所以考虑是不是条数太多的问题。所以临时将插入操作改为批次执行，暂时解决了问题。
那么，真相到底是怎样呢？
我们先来还原一下当时的场景：
mybatis xml 语句
<insert id="batchInsertMerAccountInfo" parameterType="java.util.List">
insert into tDETAIL (
NO, AMOUNT, BALANCE,
TYPE, MAC, TAG, ORDERID,BATCHNO
)
(
<foreach collection="list" index="" item="item" separator="union all">
select
#{item.no,jdbcType=VARCHAR},
#{item.amount,jdbcType=NUMERIC},
#{item.balance,jdbcType=NUMERIC},
#{item.type,jdbcType=VARCHAR},
#{item.mac,jdbcType=VARCHAR},
#{item.tag,jdbcType=VARCHAR},
#{item.orderid,jdbcType=VARCHAR},
#{item.batchno,jdbcType=VARCHAR}
from dual
</foreach>
)
</insert>
后来经过查阅资料，发现根本原因是由于sql语句包含了太多的“？”（占位符），超出了Oracle的限制。经过验证，当占位符数量超过65536时，就会报出异常。
/**
* Constructs a query using '?' for placeholders and using
* as many of these as specified with the int parameter.
*
* @param numberPlaceholders Number of placeholders ('?')
* to include in WHERE clause of constructed query.
* @return SQL Query that has provided number of '?" placeholders.
*/
private String buildQuery(final int numberPlaceholders)
{
final StringBuilder builder = new StringBuilder();
builder.append("SELECT region_id FROM countries WHERE ");
for (int count=0; count < numberPlaceholders-1; count++)
{
builder.append("region_id = ? OR ");
}
builder.append("region_id = ?");
return builder.toString();
}
/**
* Execute the provided query and populate a PreparedStatement
* wrapping this query with the number of integers provided
* as the second method argument.
*
* @param query Query to be executed.
* @param numberValues Number of placeholders to be set in the
* instance of {@code PreparedStatement} used to execute the
* provided query.
*/
private void executeQuery(final String query, final int numberValues)
{
try (final Connection connection = getDatabaseConnection();
final PreparedStatement statement = connection.prepareStatement(query))
{
for (int count = 0; count < numberValues; count++)
{
statement.setInt(count+1, count+1);
}
final ResultSet rs = statement.executeQuery();
while (rs.next())
{
out.println("Region ID: " + rs.getLong(1));
}
}
catch (SQLException sqlException)
{
out.println("ERROR: Unable to execute query - " + sqlException);
}
}
参考：https://dzone.com/articles/too-many-preparedstatement-placeholders-in-oracle
