Unable to connect to oracle via Pentaho. 'oracle.jdbc.driver.OracleDriver' could not be found

Question

Hi I've just installed Pentaho Data Integration v. 5.01 and I'm trying to connect to a database. However, I'm unable to test the connection as I get the error (pasted below). I have JDK 1.7 installed on the system as well, however I can't seem to locate ojdbc14.jar anywhere. I downloaded that and pasted it in pentaho's \lib folder but I still get the same error. Any solutions to this problem? ``` Error connecting to database [test] : org.pentaho.di.core.exception.KettleDatabaseException: Error occured while trying to connect to the database Driver class 'oracle.jdbc.driver.OracleDriver' could not be found, make sure the 'Oracle' driver (jar file) is installed. oracle.jdbc.driver.OracleDriver org.pentaho.di.core.exception.KettleDatabaseException: Error occured while trying to connect to the database Driver class 'oracle.jdbc.driver.OracleDriver' could not be found, make sure the 'Oracle' driver (jar file) is installed. oracle.jdbc.driver.OracleDriver at org.pentaho.di.core.database.Dat…

Accepted Answer

Here's my setup. I had this problem and managed to fix it as follows.

I have SQLDeveloper running on my machine and it works OK, even under VPN. However, I was running Spoon and noticed that it wouldn't work if I use VPN.

I have tried to put Oracle JDBC drivers shipped with SQLDeveloper into Pentaho. That didn't work.

However, I noticed that one big difference between SQLDeveloper and Spoon is that they use different Java runtimes and JDBC Oracle drivers! In particular, my version of SQLDeveloper is running on 32-bit JRE version 1.6.0_11, whereas my system-wide JDK is 64-bit version 1.7.0_03.

I have found the JDBC drivers that SQLDeveloper uses and used them as a replacement for the driver that is shipped with Spoon -- no change.

Then I retargeted Spoon to use the Java runtime used by SQLDeveloper.

I set two environment variables: PENTAHO_JAVA and PENTAHO_JAVA_HOME -- and it works OK now!

For reference, here are my envvars:

```
PENTAHO_JAVA=C:\opt\sqldeveloper\jdk\jre\bin\java.exe
PENTAHO_JAVA_HOME=c:\opt\sqldeveloper\jdk\jre\
```

If that is relevant, I am on Windows 7 x64.

Unable to connect to oracle via Pentaho. 'oracle.jdbc.driver.OracleDriver' could not be found

Solution

Problem