What I am wanting to do is open a connection to an Oracle Database 12c server using an OracleTranslatingConnection object so I can take advantage of the SQL Translation Framework that I have setup on a local 12c database.
The application I am working on uses Java 7 and Tomcat 7.
If I execute the snippet below outside of Tomcat, a connection is made, simpleQuery is properly translated, and I am given the correct results from the database.
However, if I execute the same snippet using Tomcat (excluding everything related to the Context) I receive the following exception once I reach the line conn = ods.getConnection().
I have ojdbc7.jar and asm-3.3.1.jar (OJDBC needs ClassWriter?) in my classpath when I executed outside of Tomcat. When using Tomcat, the jars are in Tomcat's lib folder and they must be visible because I am able to instantiate those Oracle objects.
I started with Oracle's API reference and ended up on Google with no success at finding a solution. The unhelpful solutions I have found so far involved a ClassNotFoundException with the necessary jars not in the right place, or reconfiguring Tomcat's server/web XML files (which I shouldn't need since I'm creating a data source myself, not looking it up through the Context), or connecting to the database in a way that is not using an OracleDataSource. I need that particular object so I can set the properties of the translation feature (see the line props.put(...)).
Would anyone be able to shed some light on what I'm missing here?
Exception
java.lang.NoClassDefFoundError: oracle/jdbc/internal/OracleConnection (wrong name: oracle/jdbc/proxy/oracle$1jdbc$1babelfish$1BabelfishConnection$2oracle$1jdbc$1internal$1OracleConnection$$$Proxy)
at java.lang.ClassLoader.defineClass1(Native Method)
at java.lang.ClassLoader.defineClass(Unknown Source)
at java.lang.ClassLoader.defineClass(Unknown Source)
at oracle.jdbc.proxy.ClassGenerator$2.findClass(ClassGenerator.java:326)
at java.lang.ClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.defineClass1(Native Method)
at java.lang.ClassLoader.defineClass(Unknown Source)
at java.lang.ClassLoader.defineClass(Unknown Source)
at oracle.jdbc.proxy.ClassGenerator$2.findClass(ClassGenerator.java:326)
at java.lang.ClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Unknown Source)
at oracle.jdbc.proxy.ClassGenerator.generate(ClassGenerator.java:319)
at oracle.jdbc.proxy.ProxyFactory.prepareProxy(ProxyFactory.java:517)
at oracle.jdbc.proxy.ProxyFactory.createProxy(ProxyFactory.java:479)
at oracle.jdbc.proxy.ProxyFactory.proxyForCache(ProxyFactory.java:272)
at oracle.jdbc.proxy.ProxyFactory.proxyFor(ProxyFactory.java:105)
at oracle.jdbc.proxy.ProxyFactory.proxyFor(ProxyFactory.java:91)
at oracle.jdbc.driver.OracleDriver.babelfishConnect(OracleDriver.java:657)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:561)
at oracle.jdbc.pool.OracleDataSource.getPhysicalConnection(OracleDataSource.java:317)
at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:241)
at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:184)
at com.example.OracleTest.testOracle(OracleTest.java:60)
Code Snippet
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.OracleTranslatingConnection;
import oracle.jdbc.pool.OracleDataSource;
public class OracleTest {
private static String TRANSLATION_PROFILE_NAME = "SQLSERVER_PROFILE";
private static void testOracle() throws SQLException {
String jdbcUrl = "jdbc:oracle:thin:@//server:1521/servicename";
String jdbcUserName = "user";
String jdbcPassword = "pass";
Properties props = new Properties();
OracleDataSource ods = null;
OracleTranslatingConnection trConn = null;
Statement trStmt = null;
Connection conn = null;
ResultSet trRs = null;
String simpleQuery = "SELECT TOP 1 column1 FROM test_table ORDER BY column1 DESC";
ods = new OracleDataSource();
ods.setURL(jdbcUrl);
ods.setUser(jdbcUserName);
ods.setPassword(jdbcPassword);
props.put(OracleConnection.CONNECTION_PROPERTY_SQL_TRANSLATION_PROFILE, TRANSLATION_PROFILE_NAME);
ods.setConnectionProperties(props);
conn = ods.getConnection();
trConn = (OracleTranslatingConnection) conn;
trStmt = trConn.createStatement(true);
trRs = trStmt.executeQuery(simpleQuery);
while (trRs.next()) {
System.out.println("column1: " + trRs.getString(1));
}
trRs.close();
trStmt.close();
conn.close();
}
}
Unfortunately, after a back and forth with Oracle developer support, it was determined that this was a bug to be fixed in a version after 12.1, and I was unable to utilize the query translation feature.