I finally managed to create reproducible test case for this.
See attached.
The trick seems to be that the failed operation needs to be a DDL statement.
Any insights into this will be greatly appreciated.
Tim
package foo;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.DriverManagerConnectionFactory;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.PoolingDataSource;
import org.apache.commons.pool.impl.GenericObjectPool;
public class Ora01453Example {
private static final String URL =
"jdbc:oracle:thin:@192.168.56.101:1521:orcl";
private static final String USERNAME = "ijc";
private static final String PASSWORD = "ijc";
private PoolingDataSource dataSource;
public static void main(String[] args) throws SQLException {
Ora01453Example instance = new Ora01453Example();
instance.run();
}
Ora01453Example() {
GenericObjectPool connectionPool = new GenericObjectPool(null);
connectionPool.setMaxActive(5);
connectionPool.setMaxIdle(2);
connectionPool.setMaxWait(10000);
ConnectionFactory connectionFactory = new
DriverManagerConnectionFactory(URL, USERNAME, PASSWORD);
PoolableConnectionFactory poolableConnectionFactory = new
PoolableConnectionFactory(
connectionFactory, connectionPool, null, null, false, true);
dataSource = new PoolingDataSource(connectionPool);
dataSource.setAccessToUnderlyingConnectionAllowed(true);
}
void run() throws SQLException {
System.out.println("Running...");
// get the connection
Connection con = getConnection();
try {
// this will fail, either first time or second
executeSql(con, "create table qwerty (id varchar2(100))");
executeSql(con, "create table qwerty (id varchar2(100))");
} catch (SQLException e) {
System.out.println("Failed as expected");
} finally {
// close connection so it goes back to pool
con.close();
}
// get a connection from pool again.
con = getConnection();
System.out.println("Setting transaction level");
// try to set isolation level - will fail (assuming same connection is
retrieved)
try {
con.setAutoCommit(false);
con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
} finally {
con.close();
}
}
Connection getConnection() throws SQLException {
Connection con = dataSource.getConnection();
System.out.println(
"Got Connection: " + con.hashCode()
+ " autoCommit=" + con.getAutoCommit()
+ " isolation=" + con.getTransactionIsolation());
return con;
}
void executeSql(Connection con, String sql) throws SQLException {
Statement stmt = con.createStatement();
try {
stmt.execute(sql);
} finally {
stmt.close();
}
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
