Oracle connection error

wujianjie · April 12, 2019, 12:41am

Hello
I had a problem

I can connect oracle by Toad, and I already did the next step:

1-register the oracle jdbc driver via the KNIME preferences
… It’s ok.
2-Add the Database Connector to your KNIME workflow
… It’s ok.
3-In the dialog of the Database Connector
… It’s ok.
select “oracle.jdbc.driver.OracleDriver” as database driver
4-Enter the valid jdbc url into the Database URL field e.g. jdbc:oracle:thin:@[HOST][:PORT]:SID or jdbc:oracle:thin:@//[HOST][:PORT]/SERVICE
… It’s ok.
5-Enter your username and password into the provided fields
… It’s ok.

but , the ERROR is coming.

“ERROR Database Connector 0:1 Execute failed: Could not create connection to database: Maximum number of retries to get a valid connection reached. JDBC URL: jdbc:oracle:thin:@10.60.20.101:1521:orcl”

I need you help .Thanks a lot.

by wujianjie

wujianjie · April 12, 2019, 1:15am

add more message:

Oracle ver. 10g
JODBC ver : ojdbc14.jar
KNIME-Database Connector–Connection settings–Connection–oracle.jdbc.driver.OracleDriver is OK.
Username & password is OK

quaeler · April 12, 2019, 2:57am

SID is case sensitive in certain platform deployments - are you sure the SID is “orcl” and not “ORCL”?

wujianjie · April 12, 2019, 3:29am

%E8%BF%9E%E6%8E%A5%E4%BF%A1%E6%81%AF

The sid is orcl .

Thanks for your reply.

wujianjie · April 12, 2019, 3:31am

By the way :“连接成功” means connection is success.

ipazin · April 12, 2019, 2:06pm

Hi there!

Have you tried with following format?

jdbc:oracle:thin:@<host>:<port>/<database_name>

Br,
Ivan

wujianjie · April 13, 2019, 1:16am

Thanks for your help, but the Error is not going.

ERROR Database Connector 0:1 Execute failed: Could not create connection to database: Maximum number of retries to get a valid connection reached. JDBC URL: jdbc:oracle:thin:@10.60.20.101:1521/orcl

ipazin · April 15, 2019, 11:22am

Hi there!

Not sure what is the problem. Check on your side if something is blocking connection request from KNIME.

Also you can check new Database Integration nodes. The plan is to release the production ready version with the next summer release so if just starting start with them

https://hub.knime.com/knime/nodes/DB_Connector_Labs*0oD8015Fv-UmISSY

Br,
Ivan

wujianjie · April 15, 2019, 5:34pm

I opened KNIME debug mode.
The knime.log massage is here:

Blockquote
2019-04-16 00:58:44,785 : DEBUG : main : Node : Database Connector : 0:2 : reset
2019-04-16 00:58:44,785 : DEBUG : main : Node : Database Connector : 0:2 : clean output ports.
2019-04-16 00:58:44,796 : DEBUG : main : NodeContainer : Database Connector : 0:2 : Database Connector 0:2 has new state: IDLE
2019-04-16 00:58:44,817 : DEBUG : main : Node : Database Connector : 0:2 : Configure succeeded. (Database Connector)
2019-04-16 00:58:44,817 : DEBUG : main : NodeContainer : Database Connector : 0:2 : Database Connector 0:2 has new state: CONFIGURED
2019-04-16 00:58:47,535 : DEBUG : main : ExecuteAction : : : Creating execution job for 1 node(s)…
2019-04-16 00:58:47,535 : DEBUG : main : NodeContainer : : : Database Connector 0:2 has new state: CONFIGURED_MARKEDFOREXEC
2019-04-16 00:58:47,536 : DEBUG : main : NodeContainer : : : Database Connector 0:2 has new state: CONFIGURED_QUEUED
2019-04-16 00:58:47,536 : DEBUG : main : NodeContainer : : : KNIME_project 0 has new state: EXECUTING
2019-04-16 00:58:47,536 : DEBUG : KNIME-Worker-5 : WorkflowManager : Database Connector : 0:2 : Database Connector 0:2 doBeforePreExecution
2019-04-16 00:58:47,537 : DEBUG : KNIME-Worker-5 : NodeContainer : Database Connector : 0:2 : Database Connector 0:2 has new state: PREEXECUTE
2019-04-16 00:58:47,537 : DEBUG : KNIME-Worker-5 : WorkflowManager : Database Connector : 0:2 : Database Connector 0:2 doBeforeExecution
2019-04-16 00:58:47,537 : DEBUG : KNIME-Worker-5 : NodeContainer : Database Connector : 0:2 : Database Connector 0:2 has new state: EXECUTING
2019-04-16 00:58:47,541 : DEBUG : KNIME-Worker-5 : WorkflowDataRepository : Database Connector : 0:2 : Adding handler 53664258-5221-4a36-8832-db2f355aefaa (Database Connector 0:2: ) - 1 in total
2019-04-16 00:58:47,541 : DEBUG : KNIME-WFM-Parent-Notifier : NodeContainer : : : ROOT has new state: EXECUTING
2019-04-16 00:58:47,542 : DEBUG : KNIME-Worker-5 : LocalNodeExecutionJob : Database Connector : 0:2 : Database Connector 0:2 Start execute
2019-04-16 00:58:47,567 : DEBUG : KNIME-Worker-5 : NodeContext : Database Connector : 0:2 : Workflow user found: Administrator
2019-04-16 00:58:47,567 : DEBUG : KNIME-Worker-5 : RegisteredDriversConnectionFactory : Database Connector : 0:2 : Try to lock key to obtain connection: ConnectionKey [db-user=test, db-name=jdbc:oracle:thin:@10.60.20.101:1521:orcl, wf-user=Administrator]
2019-04-16 00:58:47,567 : DEBUG : KNIME-Worker-5 : RegisteredDriversConnectionFactory : Database Connector : 0:2 : Create new connection for key: ConnectionKey [db-user=test, db-name=jdbc:oracle:thin:@10.60.20.101:1521:orcl, wf-user=Administrator]
2019-04-16 00:58:47,567 : DEBUG : KNIME-Worker-5 : DatabaseDriverLoader : Database Connector : 0:2 : Database driver retrieved from user defined drivers: oracle.jdbc.driver.OracleDriverDriver info: Driver class name: oracle.jdbc.driver.OracleDriver major version: 1 minor version: 0 jdbc compliant: true
2019-04-16 00:58:47,567 : DEBUG : pool-5-thread-2 : RegisteredDriversConnectionFactory : Database Connector : 0:2 : Opening database connection to “jdbc:oracle:thin:@10.60.20.101:1521:orcl”…
2019-04-16 00:58:47,711 : DEBUG : KNIME-Worker-5 : RegisteredDriversConnectionFactory : Database Connector : 0:2 : Add connection to map for key: ConnectionKey [db-user=test, db-name=jdbc:oracle:thin:@10.60.20.101:1521:orcl, wf-user=Administrator]
2019-04-16 00:58:47,711 : DEBUG : KNIME-Worker-5 : NodeContext : Database Connector : 0:2 : Workflow user found: Administrator
2019-04-16 00:58:47,711 : DEBUG : KNIME-Worker-5 : DatabaseConnectionSettings : Database Connector : 0:2 : Try to lock key for stmt execution: ConnectionKey [db-user=test, db-name=jdbc:oracle:thin:@10.60.20.101:1521:orcl, wf-user=Administrator]
2019-04-16 00:58:47,712 : DEBUG : KNIME-Worker-5 : DatabaseConnectionSettings : Database Connector : 0:2 : Check connection for key: ConnectionKey [db-user=test, db-name=jdbc:oracle:thin:@10.60.20.101:1521:orcl, wf-user=Administrator]
2019-04-16 00:58:47,712 : DEBUG : KNIME-Worker-5 : OracleUtility : Database Connector : 0:2 : java.sql.Connection#isValid throws error: oracle.jdbc.driver.OracleConnection.isValid(I)Z. Executing simple “SELECT 1” statement to check validity from now on.
java.lang.AbstractMethodError: oracle.jdbc.driver.OracleConnection.isValid(I)Z
at org.knime.core.node.port.database.DatabaseUtility.isValid(DatabaseUtility.java:360)
at org.knime.core.node.port.database.DatabaseConnectionSettings.execute(DatabaseConnectionSettings.java:673)
at org.knime.base.node.io.database.connection.JDBCConnectorNodeModel.execute(JDBCConnectorNodeModel.java:113)
at org.knime.core.node.NodeModel.executeModel(NodeModel.java:567)
at org.knime.core.node.Node.invokeFullyNodeModelExecute(Node.java:1186)
at org.knime.core.node.Node.execute(Node.java:973)
at org.knime.core.node.workflow.NativeNodeContainer.performExecuteNode(NativeNodeContainer.java:559)
at org.knime.core.node.exec.LocalNodeExecutionJob.mainExecute(LocalNodeExecutionJob.java:95)
at org.knime.core.node.workflow.NodeExecutionJob.internalRun(NodeExecutionJob.java:179)
at org.knime.core.node.workflow.NodeExecutionJob.run(NodeExecutionJob.java:110)
at org.knime.core.util.ThreadUtils$RunnableWithContextImpl.runWithContext(ThreadUtils.java:328)
at org.knime.core.util.ThreadUtils$RunnableWithContext.run(ThreadUtils.java:204)
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)
2019-04-16 00:58:47,716 : DEBUG : KNIME-Worker-5 : DatabaseConnectionSettings : Database Connector : 0:2 : Invalid or closed connection found. Retry counter: 1. Retry to get valid connection for key: ConnectionKey [db-user=test, db-name=jdbc:oracle:thin:@10.60.20.101:1521:orcl, wf-user=Administrator]
2019-04-16 00:58:47,716 : DEBUG : KNIME-Worker-5 : NodeContext : Database Connector : 0:2 : Workflow user found: Administrator
2019-04-16 00:58:47,716 : DEBUG : KNIME-Worker-5 : RegisteredDriversConnectionFactory : Database Connector : 0:2 : Try to lock key to obtain connection: ConnectionKey [db-user=test, db-name=jdbc:oracle:thin:@10.60.20.101:1521:orcl, wf-user=Administrator]
2019-04-16 00:58:47,716 : DEBUG : KNIME-Worker-5 : RegisteredDriversConnectionFactory : Database Connector : 0:2 : Connection found for key: ConnectionKey [db-user=test, db-name=jdbc:oracle:thin:@10.60.20.101:1521:orcl, wf-user=Administrator]
2019-04-16 00:58:47,719 : DEBUG : KNIME-Worker-5 : RegisteredDriversConnectionFactory : Database Connector : 0:2 : Invalid connection found in cache with key: ConnectionKey [db-user=mj01, db-name=jdbc:oracle:thin:@10.60.20.101:1521:orcl, wf-user=Administrator]
2019-04-16 00:58:47,719 : DEBUG : KNIME-Worker-5 : RegisteredDriversConnectionFactory : Database Connector : 0:2 : Closing connection with key: ConnectionKey [db-user=test, db-name=jdbc:oracle:thin:@10.60.20.101:1521:orcl, wf-user=Administrator]
2019-04-16 00:58:47,721 : DEBUG : KNIME-Worker-5 : RegisteredDriversConnectionFactory : Database Connector : 0:2 : Removing closed connection from cache with key: ConnectionKey [db-user=test, db-name=jdbc:oracle:thin:@10.60.20.101:1521:orcl, wf-user=Administrator]
2019-04-16 00:58:47,721 : DEBUG : KNIME-Worker-5 : RegisteredDriversConnectionFactory : Database Connector : 0:2 : Create new connection for key: ConnectionKey [db-user=test, db-name=jdbc:oracle:thin:@10.60.20.101:1521:orcl, wf-user=Administrator]
2019-04-16 00:58:47,721 : DEBUG : KNIME-Worker-5 : DatabaseDriverLoader : Database Connector : 0:2 : Database driver retrieved from user defined drivers: oracle.jdbc.driver.OracleDriverDriver info: Driver class name: oracle.jdbc.driver.OracleDriver major version: 1 minor version: 0 jdbc compliant: true
2019-04-16 00:58:47,721 : DEBUG : pool-5-thread-2 : RegisteredDriversConnectionFactory : Database Connector : 0:2 : Opening database connection to “jdbc:oracle:thin:@10.60.20.101:1521:orcl”…
…

Blockquote

but ,I don’t known what’s the meaning of the DEBUG message.
Could you find the reason of the oracle connection error?

Thanks a lot.

tobias.koetter · April 17, 2019, 1:13pm

Hi,
please make sure to use driver for Java 8 e.g. the ojdbc8.jar driver since versions vor Java 1.4 e.g. ojdbc14.jar do not support the isValid method which we use to test the connection. You can download the latest version from here. The Oracle 12.2.0.1 JDBC Driver should be backward compatible and should work with your oracle 10G db.
Bye
Tobias.

wujianjie · April 17, 2019, 3:12pm

Thanks a lot.
Use the ojdbc8.jar is the right key.