Connecting to Oracle from Mac - Errors

[bdavid4]

I have just started getting back into KNIME after experimenting with it a little while ago. I had installed an older version (3.x) and used it to test some database interactions. I was able to connect to our Oracle databases onsite without issues.

I have since installed 4.1.2 and am unable to connect to any Oracle databases at this point. My Mac OS is version 10.14.6 I am using the 12.2.0 version of the ojdbc8.jar file from Oracle.

I can connect to all of my databases using JDBC with Oracle SQL Developer with no issues, I just can’t seem to make it work on KNIME. I keep getting a:

‘ERROR Oracle Connector 0:5 Execute failed: (“SQLException”): -1’

My configuration seems to be correct. I am trying both the Oracle Connector Node and the generic DB Connector Node and get the same result.

Any helpful suggestions would be great.

Thanks,

[ipazin]

Hi there @bdavid4,

welcome to KNIME Community!

Guess the difference is in older (3.x version) you were using what is now called legacy database framework compared to current database framework in newer versions.

Can you check steps in first example (Oracle connection example) from Database guide?
https://docs.knime.com/latest/db_extension_guide/index.html#connecting_examples

Br,
Ivan

[bdavid4]

Hi Ivan,

I have gone through the database guide many times and have the configuration set up as indicated in the guide. Still no luck connecting to Oracle.

Thanks for the info.

D

[tobias.koetter]

Hi bdavid4,
can you please post the full error message from the KNIME log file. But be careful not to send any confidential information such as the database host name etc.
Thanks
Tobias

[bdavid4]

Hi Tobias,

Here is the full error message. This same message happens with both the Oracle connector, and the generic DB connector.

2020-04-29 11:47:24,888 : ERROR : KNIME-Worker-6-Oracle Connector 2:1 : : Node : Oracle Connector : 2:1 : Execute failed: (“SQLException”): -1
java.sql.SQLException: -1
at org.knime.database.util.Futures.throwDatabaseExecutionException(Futures.java:112)
at org.knime.database.connection.impl.DBConnectionManager.initializeConnection(DBConnectionManager.java:473)
at org.knime.database.connection.impl.DBConnectionManager.(DBConnectionManager.java:254)
at org.knime.database.session.impl.DefaultDBSession.(DefaultDBSession.java:212)
at org.knime.database.session.impl.DBSessionFactory.create(DBSessionFactory.java:81)
at org.knime.database.session.DBSessionCache.getOrCreate(DBSessionCache.java:151)
at org.knime.database.node.connector.AbstractDBConnectorNodeModel.registerSession(AbstractDBConnectorNodeModel.java:388)
at org.knime.database.node.connector.AbstractDBConnectorNodeModel.execute(AbstractDBConnectorNodeModel.java:219)
at org.knime.core.node.NodeModel.executeModel(NodeModel.java:571)
at org.knime.core.node.Node.invokeFullyNodeModelExecute(Node.java:1236)
at org.knime.core.node.Node.execute(Node.java:1016)
at org.knime.core.node.workflow.NativeNodeContainer.performExecuteNode(NativeNodeContainer.java:557)
at org.knime.core.node.exec.LocalNodeExecutionJob.mainExecute(LocalNodeExecutionJob.java:95)
at org.knime.core.node.workflow.NodeExecutionJob.internalRun(NodeExecutionJob.java:218)
at org.knime.core.node.workflow.NodeExecutionJob.run(NodeExecutionJob.java:124)
at org.knime.core.util.ThreadUtils$RunnableWithContextImpl.runWithContext(ThreadUtils.java:334)
at org.knime.core.util.ThreadUtils$RunnableWithContext.run(ThreadUtils.java:210)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at org.knime.core.util.ThreadPool$MyFuture.run(ThreadPool.java:123)
at org.knime.core.util.ThreadPool$Worker.run(ThreadPool.java:246)
Caused by: java.lang.ArrayIndexOutOfBoundsException: -1
at oracle.jdbc.driver.T4CTTIoauthenticate.setSessionFields(T4CTTIoauthenticate.java:1500)
at oracle.jdbc.driver.T4CTTIoauthenticate.(T4CTTIoauthenticate.java:291)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:599)
at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:688)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:39)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:691)
at org.knime.database.connection.UrlDBConnectionController$ControlledDriver.connect(UrlDBConnectionController.java:95)
at org.knime.database.connection.UrlDBConnectionController.createConnection(UrlDBConnectionController.java:308)
at org.knime.database.connection.UserDBConnectionController.createConnection(UserDBConnectionController.java:249)
at org.knime.database.connection.AbstractConnectionProvider.createConnection(AbstractConnectionProvider.java:89)
at org.knime.database.connection.impl.DBConnectionManager.lambda$2(DBConnectionManager.java:458)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)

Thanks for your help.

D

[tobias.koetter]

Hello bdavid4,
thanks for the log file.This seems to be the same problem as described in this thread:

DB connector Execute failed: ("SQLException"): -1

Hi Amely, do you use the same driver file in KNIME 3.7 and KNIME 4.1? Maybe you need to register additional jars to connect to your Oracle database e.g. when using Oracle Wallet? If this is the case you can unzip the driver jar and all of its companion libs into a directory and then register the complete folder via the Add directory button as shown here [image] You can also add the jars separately via the Add file button like this: [image] Bye Tobias

Make sure to add all the companion libraries when registering the driver.
Bye
Tobias

[bdavid4]

Hi Tobias,

Thanks for your assistance. I have tried adding the entire directory /Library/Java/Extensions and registering it. I have tried putting the ojdbc8-full directory in a different location (as was suggested in the other thread) and created two different Oracle drivers in KNIME. One referencing the /Library/Java/Extensions and the other referencing the specific files in the directory and I am still unable to connect to oracle.

Here is my /Library/Java/Extensions directory

Here is one of my Oracle Drivers

Here is the other Oracle Driver

Here is my new error message.

java.sql.SQLException: IOExceptionnull
at org.knime.database.util.Futures.throwDatabaseExecutionException(Futures.java:112)
at org.knime.database.connection.impl.DBConnectionManager.initializeConnection(DBConnectionManager.java:473)
at org.knime.database.connection.impl.DBConnectionManager.(DBConnectionManager.java:254)
at org.knime.database.session.impl.DefaultDBSession.(DefaultDBSession.java:212)
at org.knime.database.session.impl.DBSessionFactory.create(DBSessionFactory.java:81)
at org.knime.database.session.DBSessionCache.getOrCreate(DBSessionCache.java:151)
at org.knime.database.node.connector.AbstractDBConnectorNodeModel.registerSession(AbstractDBConnectorNodeModel.java:388)
at org.knime.database.node.connector.AbstractDBConnectorNodeModel.execute(AbstractDBConnectorNodeModel.java:219)
at org.knime.core.node.NodeModel.executeModel(NodeModel.java:571)
at org.knime.core.node.Node.invokeFullyNodeModelExecute(Node.java:1236)
at org.knime.core.node.Node.execute(Node.java:1016)
at org.knime.core.node.workflow.NativeNodeContainer.performExecuteNode(NativeNodeContainer.java:557)
at org.knime.core.node.exec.LocalNodeExecutionJob.mainExecute(LocalNodeExecutionJob.java:95)
at org.knime.core.node.workflow.NodeExecutionJob.internalRun(NodeExecutionJob.java:218)
at org.knime.core.node.workflow.NodeExecutionJob.run(NodeExecutionJob.java:124)
at org.knime.core.util.ThreadUtils$RunnableWithContextImpl.runWithContext(ThreadUtils.java:334)
at org.knime.core.util.ThreadUtils$RunnableWithContext.run(ThreadUtils.java:210)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at org.knime.core.util.ThreadPool$MyFuture.run(ThreadPool.java:123)
at org.knime.core.util.ThreadPool$Worker.run(ThreadPool.java:246)
Caused by: java.lang.RuntimeException: IOExceptionnull
at oracle.i18n.text.converter.CharacterConverterOGS.readObj(CharacterConverterOGS.java:443)
at oracle.i18n.text.converter.CharacterConverterOGS.getInstance(CharacterConverterOGS.java:133)
at oracle.sql.converter.CharacterConverterFactoryOGS.make(CharacterConverterFactoryOGS.java:43)
at oracle.sql.CharacterSetWithConverter.getInstance(CharacterSetWithConverter.java:103)
at oracle.sql.CharacterSetFactoryThin.make(CharacterSetFactoryThin.java:135)
at java.util.concurrent.ConcurrentHashMap.computeIfAbsent(ConcurrentHashMap.java:1660)
at oracle.sql.CharacterSet.make(CharacterSet.java:524)
at oracle.jdbc.driver.DBConversion.init(DBConversion.java:177)
at oracle.jdbc.driver.DBConversion.(DBConversion.java:130)
at oracle.jdbc.driver.T4CConnection.doCharSetNegotiation(T4CConnection.java:1781)
at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1687)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:588)
at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:793)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:57)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:747)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:562)
at org.knime.database.connection.UrlDBConnectionController$ControlledDriver.connect(UrlDBConnectionController.java:95)
at org.knime.database.connection.UrlDBConnectionController.createConnection(UrlDBConnectionController.java:308)
at org.knime.database.connection.UserDBConnectionController.createConnection(UserDBConnectionController.java:249)
at org.knime.database.connection.AbstractConnectionProvider.createConnection(AbstractConnectionProvider.java:89)
at org.knime.database.connection.impl.DBConnectionManager.lambda$2(DBConnectionManager.java:458)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)

[tobias.koetter]

Hi,
can you also add the orai18n.jar file to the files in your Oracle2 driver.
Also the Extensions folder you register with the ORacleDriver id also contains the postgresql driver which you should avoid. If you want to register the driver via a directory please make sure that the directory only contains the files from the driver.
Bye
Tobias