There are multiple causes of the ORA-01745 (“invalid host/bind variable name error”) error when using an Oracle database. The Oracle 9i documentation on errors ORA-01500 through ORA-02098 provides more details regarding ORA-01745. It states that the “Cause” is “A colon in a bind variable or INTO specification was followed by an inappropriate name, perhaps a reserved word.” It also states that the “Action” is “Change the variable name and retry the operation.” In the same Oracle 12g documentation, however, there is no description of “cause” or “action” for ORA-01745, presumably because there are multiple causes and multiple corresponding actions associated with this message. In this post, I will focus on one of the perhaps less obvious causes and the corresponding action for that cause.
Some of the common causes for ORA-01745 that I will NOT be focusing on in this post include using an Oracle database reserved name (reserved word) as an identifier, extraneous or missing colon or comma, or attempting to bind structure names (rather than variables) to the placeholders.
In addition to the causes just listed and likely in addition to other potential causes of ORA-01745, another situation that can cause the ORA-01745 error is using too many ? placeholders in a JDBC PreparedStatement with the Oracle database. I will demonstrate in this post that the number of ? placeholders in a PreparedStatement that cause this ORA-01745 is 65536 (216).
I have blogged previously on the ORA-01795 error that occurs when one attempts to include more than 1000 values in an Oracle SQL IN condition. There are multiple ways to deal with this limitation and one of the alternative approaches might be to use multiple ORs to “OR” together more than 1000 values. This will typically be implemented with a PreparedStatement and with a ? placeholder placed in the SQL statement for each value being OR-ed. This PreparedStatement-based alternate approach employing ? placeholders will only work as long as the number of vales being OR-ed together is smaller than 65536.
The code listing that follows demonstrates how a SQL query against the Oracle HR schema can be generated to make it easy to reproduce the ORA-01745 error with too many ? placeholders (full code listing is available on GitHub).
Building Up Prepared Statement with Specified Number of ? Placeholders
/**
* 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();
}The next code listing demonstrates building a PreparedStatement based on the query constructed in the last code listing and setting its placeholders with a number of consecutive integers that match the number of ? placeholders.
Configuring PreparedStatement‘s ? Placeholders
/**
* 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);
}
}The next screen snapshot shows the ORA-01745 error occurring when the number of ? placeholders applied is 65536.
This example shows that there is a maximum number of ? placeholders that can be used in an Oracle SQL statement. Fortunately, there are other ways to accomplish this type of functionality that do not have this ORA-01475 limit of 65536 ? placeholders or the 1000 IN elements limit that causes an ORA-01795 error
| Published on Java Code Geeks with permission by Dustin Marx, partner at our JCG program. See the original article here: Too Many PreparedStatement Placeholders in Oracle JDBC Opinions expressed by Java Code Geeks contributors are their own. |
