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.Database.normalConnect(Database.java:415)
    at org.pentaho.di.core.database.Database.connect(Database.java:353)
    at org.pentaho.di.core.database.Database.connect(Database.java:306)
    at org.pentaho.di.core.database.Database.connect(Database.java:294)
    at org.pentaho.di.core.database.DatabaseFactory.getConnectionTestReport(DatabaseFactory.java:84)
    at org.pentaho.di.core.database.DatabaseMeta.testConnection(DatabaseMeta.java:2459)
    at org.pentaho.ui.database.event.DataHandler.testDatabaseConnection(DataHandler.java:541)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.pentaho.ui.xul.impl.AbstractXulDomContainer.invoke(AbstractXulDomContainer.java:329)
    at org.pentaho.ui.xul.impl.AbstractXulComponent.invoke(AbstractXulComponent.java:139)
    at org.pentaho.ui.xul.impl.AbstractXulComponent.invoke(AbstractXulComponent.java:123)
    at org.pentaho.ui.xul.swt.tags.SwtButton.access$500(SwtButton.java:26)
    at org.pentaho.ui.xul.swt.tags.SwtButton$4.widgetSelected(SwtButton.java:121)
    at org.eclipse.swt.widgets.TypedListener.handleEvent(Unknown Source)
    at org.eclipse.swt.widgets.EventTable.sendEvent(Unknown Source)
    at org.eclipse.swt.widgets.Widget.sendEvent(Unknown Source)
    at org.eclipse.swt.widgets.Display.runDeferredEvents(Unknown Source)
    at org.eclipse.swt.widgets.Display.readAndDispatch(Unknown Source)
    at org.eclipse.jface.window.Window.runEventLoop(Window.java:820)
    at org.eclipse.jface.window.Window.open(Window.java:796)
    at org.pentaho.ui.xul.swt.tags.SwtDialog.show(SwtDialog.java:375)
    at org.pentaho.ui.xul.swt.tags.SwtDialog.show(SwtDialog.java:301)
    at org.pentaho.di.ui.core.database.dialog.XulDatabaseDialog.open(XulDatabaseDialog.java:115)
    at org.pentaho.di.ui.core.database.dialog.DatabaseDialog.open(DatabaseDialog.java:62)
    at org.pentaho.di.ui.spoon.delegates.SpoonDBDelegate.newConnection(SpoonDBDelegate.java:493)
    at org.pentaho.di.ui.spoon.delegates.SpoonDBDelegate.newConnection(SpoonDBDelegate.java:478)
    at org.pentaho.di.ui.spoon.Spoon.doubleClickedInTree(Spoon.java:2885)
    at org.pentaho.di.ui.spoon.Spoon.access$2300(Spoon.java:332)
    at org.pentaho.di.ui.spoon.Spoon$27.widgetDefaultSelected(Spoon.java:5657)
    at org.eclipse.swt.widgets.TypedListener.handleEvent(Unknown Source)
    at org.eclipse.swt.widgets.EventTable.sendEvent(Unknown Source)
    at org.eclipse.swt.widgets.Widget.sendEvent(Unknown Source)
    at org.eclipse.swt.widgets.Display.runDeferredEvents(Unknown Source)
    at org.eclipse.swt.widgets.Display.readAndDispatch(Unknown Source)
    at org.pentaho.di.ui.spoon.Spoon.readAndDispatch(Spoon.java:1227)
    at org.pentaho.di.ui.spoon.Spoon.waitForDispose(Spoon.java:7368)
    at org.pentaho.di.ui.spoon.Spoon.start(Spoon.java:8673)
    at org.pentaho.di.ui.spoon.Spoon.main(Spoon.java:625)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.pentaho.commons.launcher.Launcher.main(Launcher.java:134)
Caused by: org.pentaho.di.core.exception.KettleDatabaseException: 
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.Database.connectUsingClass(Database.java:474)
    at org.pentaho.di.core.database.Database.normalConnect(Database.java:399)
    ... 45 more
Caused by: java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver
    at java.net.URLClassLoader$1.run(Unknown Source)
    at java.net.URLClassLoader$1.run(Unknown Source)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.net.URLClassLoader.findClass(Unknown Source)
    at java.lang.ClassLoader.loadClass(Unknown Source)
    at java.lang.ClassLoader.loadClass(Unknown Source)
    at org.pentaho.di.core.database.Database.connectUsingClass(Database.java:467)
    ... 46 more

score 23 · Answer 1 · edited Dec 26 '18 at 13:21

23

Try placing the odbc jar file (eg: ojdbc5-11.1.0.7.0.jar) inside the "data-integration/lib" directory of your installation. Don't forget to restart Pentaho to make it work.

edited Dec 26 '18 at 13:21

João V.

61
1
12

answered Jan 10 '14 at 22:54

user_sk

73
1
9

More details: http://www.knowpentaho.com/2013/12/pentaho-common-errors-driver-class.html – raittes Mar 13 '15 at 03:25

score 7 · Accepted Answer · edited May 12 '14 at 18:26

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.

Thanks for your response! I had found a work around. I already had a running version of Pentaho 4.1.0 on my system. It was 5.0.1 that was causing the problem. So what I did was, I copied all the /lib* folders from 4.1 and pasted it in the same location of 5.0.1. And it worked! The next time this happens, I'll try your method! — Joshua1729, Dec 11 '13 at 22:31

score 7 · Answer 3 · edited Feb 07 '22 at 13:43

1. First go to Oracle Site:

http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html

2. Download 1 file: ojdbc6.jar (2,739,670 bytes) - (SHA1 Checksum: a483a046eee2f404d864a6ff5b09dc0e1be3fe6c) Classes for use with JDK 1.6. It contains the JDBC driver classes except classes for NLS support in Oracle Object and Collection types.

3. Copy it into Pentaho folder: C:\Program Files\Pentaho\data-integration\lib

4. Close Spoon if it is open and re-open again. (transferred from the comment of @mishkin - thanks him)

That works on my Windows 7 X64 Pro with latest Pentaho 5.3 Stable.

4. close Spoon if it is open and re-open again. this was the missing step for me — mishkin, Jun 29 '15 at 17:17

score 2 · Answer 4 · answered Mar 02 '16 at 15:34

2

In Pentaho version 6.0 put the driver (ojdbc6.jar) in: "PentahoInstallation"\design-tools\data-integration\lib and restart the Data Integration ide.

answered Mar 02 '16 at 15:34

Marcel Fernandez

41
5

score 1 · Answer 5 · answered Mar 28 '14 at 09:45

See, guys we doesn't need all these stuff. We need to paste oracle jdbc driver (ojdbc6.jar) in the following location.

C:\Program Files\pentaho\design-tools\report-designer\lib\jdbc
C:\Program Files\pentaho\design-tools\data-integration\lib
C:\Program Files\pentaho\design-tools\aggregation-designer\lib
C:\Program Files\pentaho\design-tools\schema-workbench\lib

score 0 · Answer 6 · answered Dec 05 '13 at 15:44

0

try to put ojdbc driver in libext/JDBC in your PDI path (location)

answered Dec 05 '13 at 15:44

DGA

258
3
15

score 0 · Answer 7 · answered Oct 05 '16 at 12:14

0

Try putting the jar on tomcat library folder, then restart Pentaho.

Should be here .../biserver-ce-6.0.0.0-353/biserver-ce/tomcat/lib

There are already some drivers here.

answered Oct 05 '16 at 12:14

Thomas Decaux

21,738
2
113
124

score 0 · Answer 8 · answered Jul 23 '18 at 05:57

0

I could solve this issue by selecting Oralce Native connection > Hostname as Ip address of DB server > instead of SN00XXXX I had used DB00XXX name for the Database name.

I hope this works for such issues with oracle connectivity.

answered Jul 23 '18 at 05:57

DJ6968

109
1
2

score 0 · Answer 9 · answered Jul 22 '21 at 20:05

0

In my case, the error was in Report designer

The fix: only copy ojdbc14.jar to

\prd-ce-5.0.1-stable\report-designer\lib

and restart the tool.

answered Jul 22 '21 at 20:05

Hernaldo Gonzalez

1,977
1
21
32

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

9 Answers9