AnsweredAssumed Answered

How can I connect to an Oracle DB using the TNS name instead of host and port number?

Question asked by Marco Cavalazzi on Oct 22, 2018
Latest reply on Nov 5, 2018 by Ana Gonzalez

I am trying to connect to two different Oracle DBs using Pentaho Data Integration and Pentaho Business Analytics on a CentOS7 operating system.

 

1) The first situation requires me to access the Oracle DB through normal JDBC DB connection.

Using Oracle SQL Developer I don't need those and I can connect to the DB easily using hostname, port number, my DB username and password.

On Pentaho Data Integration (PDI) I am able to successfully set up the DB connection ("Test" returns ok), but it later asks me the repository credentials.

- What are those?

- Why does PDI need them? and

- Where can I look for them?

 

2) The second one needs me to use the TNS name in order to reach it. If I do that with Oracle SQL Developer everything works fine. I read online that on PDI I need to use an OCI DB connection, inserting the TNS name of the DB as the SID, then username and password (that's it). When I do that, the system returns me:

 

Error connecting to database [Oracle Host Review DB connection] :org.pentaho.di.core.exception.KettleDatabaseException:

Error occurred while trying to connect to the database

 

Error connecting to database: (using class oracle.jdbc.driver.OracleDriver)

no ocijdbc11 in java.library.path

 

org.pentaho.di.core.exception.KettleDatabaseException:

Error occurred while trying to connect to the database

 

Error connecting to database: (using class oracle.jdbc.driver.OracleDriver)

no ocijdbc11 in java.library.path

 

at org.pentaho.di.core.database.Database.normalConnect(Database.java:472)

at org.pentaho.di.core.database.Database.connect(Database.java:370)

at org.pentaho.di.core.database.Database.connect(Database.java:341)

at org.pentaho.di.core.database.Database.connect(Database.java:331)

at org.pentaho.di.core.database.DatabaseFactory.getConnectionTestReport(DatabaseFactory.java:80)

at org.pentaho.di.core.database.DatabaseMeta.testConnection(DatabaseMeta.java:2786)

at org.pentaho.ui.database.event.DataHandler.testDatabaseConnection(DataHandler.java:619)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:498)

at org.pentaho.ui.xul.impl.AbstractXulDomContainer.invoke(AbstractXulDomContainer.java:313)

at org.pentaho.ui.xul.impl.AbstractXulComponent.invoke(AbstractXulComponent.java:157)

at org.pentaho.ui.xul.impl.AbstractXulComponent.invoke(AbstractXulComponent.java:141)

at org.pentaho.ui.xul.swt.tags.SwtButton.access$500(SwtButton.java:43)

at org.pentaho.ui.xul.swt.tags.SwtButton$4.widgetSelected(SwtButton.java:137)

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.di.ui.xul.KettleDialog.show(KettleDialog.java:80)

at org.pentaho.di.ui.xul.KettleDialog.show(KettleDialog.java:47)

at org.pentaho.di.ui.core.database.dialog.XulDatabaseDialog.open(XulDatabaseDialog.java:118)

at org.pentaho.di.ui.core.database.dialog.DatabaseDialog.open(DatabaseDialog.java:60)

at org.pentaho.di.ui.repo.controller.RepositoryConnectController.lambda$editDatabaseConnection$2(RepositoryConnectController.java:157)

at org.eclipse.swt.widgets.RunnableLock.run(Unknown Source)

at org.eclipse.swt.widgets.Synchronizer.runAsyncMessages(Unknown Source)

at org.eclipse.swt.widgets.Display.runAsyncMessages(Unknown Source)

at org.eclipse.swt.widgets.Display.readAndDispatch(Unknown Source)

at org.pentaho.di.ui.repo.dialog.RepositoryDialog.open(RepositoryDialog.java:99)

at org.pentaho.di.ui.repo.dialog.RepositoryDialog.open(RepositoryDialog.java:81)

at org.pentaho.di.ui.repo.dialog.RepositoryDialog.open(RepositoryDialog.java:77)

at org.pentaho.di.ui.repo.dialog.RepositoryDialog.openManager(RepositoryDialog.java:108)

at org.pentaho.di.ui.repo.menu.RepositoryConnectMenu$2$2.widgetSelected(RepositoryConnectMenu.java:180)

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:1375)

at org.pentaho.di.ui.spoon.Spoon.waitForDispose(Spoon.java:8104)

at org.pentaho.di.ui.spoon.Spoon.start(Spoon.java:9466)

at org.pentaho.di.ui.spoon.Spoon.main(Spoon.java:701)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:498)

at org.pentaho.commons.launcher.Launcher.main(Launcher.java:92)

Caused by: org.pentaho.di.core.exception.KettleDatabaseException:

Error connecting to database: (using class oracle.jdbc.driver.OracleDriver)

no ocijdbc11 in java.library.path

 

 

at org.pentaho.di.core.database.Database.connectUsingClass(Database.java:585)

at org.pentaho.di.core.database.Database.normalConnect(Database.java:456)

... 50 more

Caused by: java.lang.UnsatisfiedLinkError: no ocijdbc11 in java.library.path

at java.lang.ClassLoader.loadLibrary(ClassLoader.java:1867)

at java.lang.Runtime.loadLibrary0(Runtime.java:870)

at java.lang.System.loadLibrary(System.java:1122)

at oracle.jdbc.driver.T2CConnection$1.run(T2CConnection.java:3541)

at java.security.AccessController.doPrivileged(Native Method)

at oracle.jdbc.driver.T2CConnection.loadNativeLibrary(T2CConnection.java:3537)

at oracle.jdbc.driver.T2CConnection.logon(T2CConnection.java:269)

at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:553)

at oracle.jdbc.driver.T2CConnection.<init>(T2CConnection.java:165)

at oracle.jdbc.driver.T2CDriverExtension.getConnection(T2CDriverExtension.java:53)

at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:528)

at java.sql.DriverManager.getConnection(DriverManager.java:664)

at java.sql.DriverManager.getConnection(DriverManager.java:208)

at org.pentaho.di.core.database.Database.connectUsingClass(Database.java:580)

... 51 more

 

I can see that it asks for a new library. I looked for it and found the .dll and .so files. I tried storing the .so file in one of the folders in the java.library.path with no success. Rebooting Pentaho or the whole operating system didn't change anything. So my questions are:

- Where can I find the right library?

- Where do I need to store the library for Pentaho to recognize it?

Outcomes