SQL FROM CLAUSE DELIMITED BY BIND VARIABLE GIVES ORA-01745 (Doc ID 731966.1)

Last updated on SEPTEMBER 01, 2014

Applies to:

JDBC - Version 10.2.0.3 and later
Information in this document applies to any platform.

Symptoms

Testcase JDBC code shows that an insert statement using a subquery with bind variables fails with
an ORA-01745. This query works when connected to  RDBMS 9.2.0.8 but fails with RDBMS 10.2.0.4

This reproduces with all the JDBC drivers tested including JDBC/thin 10.2.0.4 and 11.1.0.6

import java.sql.*;
import oracle.jdbc.OracleDriver;

public class Class1 {
public static void main(String[] args) throws Exception {

  Connection conn = getConnection();
  PreparedStatement ps = conn.prepareStatement("select ?from dept");
  ps.setInt(1,10);
  ps.execute(); // ORA-01745 when connecting to 10.2.0.4
  // - no error when connected to 9.2.0.8
  conn.close();
}

public static Connection getConnection() throws SQLException {
  String username = "scott";
  String password = "tiger";
  String thinConn = "jdbc:oracle:thin:@//host:1521/ORCL";
  DriverManager.registerDriver(new OracleDriver());
  Connection conn = DriverManager.getConnection(thinConn,username,password);
  conn.setAutoCommit(false);
  return conn;
}

Result when running against RDBMS 10.2.0.4:
Exception in thread "main" java.sql.SQLException: ORA-01745: invalid host/bind variable name

at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:74)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:110)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:171)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1030)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:194)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:785)
at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:860)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1186)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3381)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3482)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1085)
at Class1.main(Class1.java:10)




Changes

Upgrade of RDBMS.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms