I have workflows that submit UPDATE, INSERT or DELETE SQL statements to my oracle database by means of the database reader and the database looping nodes. Most of the time this works without any problems, but I notice that If I have had the KNIME program open for more than few hours, these nodes will start to fail with a "ERROR Database Looping 3:38:32 Execute failed: ORA-00900: invalid SQL statement" error.
If I save everything and restart KNIME, the problem goes away initially (using the exact same inputs) and then comes back once KNIME has been open for awhile. I can't get a reproducible set of circumstances to trigger this -- it really seems to be the case that it is dependent on how long KNIME has been open or perhaps how much toal KNIME activity has gone on since the last KNIME restart.
Is there anything intrisically different about using UPDATE or DELETE statements (as opposed to just SELECT) that could cause this behavior? I am using KNIME 3.1.2, but have observed this behavior for all versions back to 12.9.x.
[BTW: I am aware of the databse update and database delete nodes and I use those where appropriate, but sometimes you need to run an SQL update, insert or delete statement directly]
Hi,
one possible reason that comes to mind is that the connection has been closed by Oracle and KNIME hasn't detected it. Which is why it works after a restart. To get a better idea about what the problem is you could have a look into the KNIME log file where we log out all SQL statements that get send to the db for execution. Search for the error and have a look at the SQL statement which is executed before the error occurs. To view the log file in KNIME go to View->Open KNIME Log.
Bye
Tobias
Thanks Tobias. I had my log set at ERROR level only, so I don't see the extra information you suggested I look for. I have reset the log level and will wait for the next time this happens, then I'll post back what I find.
Thanks.
This error happened again today and I caught the detailed log info this time. The situation was a database looping node that had 11 rows to loop through. The error was thrown during the first row. The SQL is an insert statement that is perfectly valid. I saved the workflow, restarted KNIME and ran the Database looping node again and everything worked fine with the exact same SQL statement. The log trace is as follows (I replaced the SMILES string):
2016-06-22 00:28:44,056 : DEBUG : KNIME-Worker-53 : LocalNodeExecutionJob : Database Looping : 6:38:32 : Database Looping 6:38:32 Start execute
2016-06-22 00:28:44,056 : DEBUG : KNIME-Worker-53 : DatabaseDriverLoader : Database Looping : 6:38:32 : Database driver oracle.jdbc.driver.OracleDriver retrieved from driver map
2016-06-22 00:28:44,126 : DEBUG : KNIME-Worker-53 : DBReaderImpl : Database Looping : 6:38:32 : Executing SQL statement as executeQuery: INSERT INTO CHEMREG.CHEMREG_COMPOUND
(COMPOUND)
VALUES ( jc_molconvertb('VALID_SMILES_HERE', 'mol') )
2016-06-22 00:28:44,796 : DEBUG : KNIME-Worker-53 : DBReaderImpl : Database Looping : 6:38:32 : Reading meta data from database ResultSet...
2016-06-22 00:28:45,046 : DEBUG : KNIME-Worker-53 : Database Looping : Database Looping : 6:38:32 : reset
2016-06-22 00:28:45,056 : ERROR : KNIME-Worker-53 : Database Looping : Database Looping : 6:38:32 : Execute failed: ORA-00900: invalid SQL statement
2016-06-22 00:28:45,056 : DEBUG : KNIME-Worker-53 : Database Looping : Database Looping : 6:38:32 : Execute failed: ORA-00900: invalid SQL statement
java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:389)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:382)
at oracle.jdbc.driver.T4CTTIfun.processError(T4CTTIfun.java:675)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
at oracle.jdbc.driver.T4C8Odscrarr.doODNY(T4C8Odscrarr.java:98)
at oracle.jdbc.driver.T4CStatement.doDescribe(T4CStatement.java:808)
at oracle.jdbc.driver.OracleStatement.describe(OracleStatement.java:3991)
at oracle.jdbc.driver.OracleResultSetMetaData.<init>(OracleResultSetMetaData.java:55)
at oracle.jdbc.driver.OracleResultSetImpl.getMetaData(OracleResultSetImpl.java:182)
at org.knime.core.node.port.database.reader.DBReaderImpl.createRowIteratorConnection(DBReaderImpl.java:306)
at org.knime.core.node.port.database.reader.DBReaderImpl.createTable(DBReaderImpl.java:244)
at org.knime.core.node.port.database.reader.DBReader.createTable(DBReader.java:115)
at org.knime.base.node.io.database.DBReaderNodeModel.getResultTable(DBReaderNodeModel.java:150)
at org.knime.base.node.io.database.DatabaseLoopingNodeModel.execute(DatabaseLoopingNodeModel.java:240)
at org.knime.core.node.NodeModel.executeModel(NodeModel.java:563)
at org.knime.core.node.Node.invokeFullyNodeModelExecute(Node.java:1146)
at org.knime.core.node.Node.execute(Node.java:933)
at org.knime.core.node.workflow.NativeNodeContainer.performExecuteNode(NativeNodeContainer.java:556)
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)
2016-06-22 00:28:45,056 : DEBUG : KNIME-Worker-53 : WorkflowManager : Database Looping : 6:38:32 : Database Looping 6:38:32 doBeforePostExecution
2016-06-22 00:28:45,056 : DEBUG : KNIME-Worker-53 : NodeContainer : Database Looping : 6:38:32 : Database Looping 6:38:32 has new state: POSTEXECUTE
2016-06-22 00:28:45,056 : DEBUG : KNIME-Worker-53 : WorkflowManager : Database Looping : 6:38:32 : Database Looping 6:38:32 doAfterExecute - failure
2016-06-22 00:28:45,056 : DEBUG : KNIME-Worker-53 : Database Looping : Database Looping : 6:38:32 : reset
2016-06-22 00:28:45,056 : DEBUG : KNIME-Worker-53 : Database Looping : Database Looping : 6:38:32 : clean output ports.
2016-06-22 00:28:45,056 : DEBUG : KNIME-Worker-53 : WorkflowFileStoreHandlerRepository : Database Looping : 6:38:32 : Removing handler a32b317e-4ef0-4d66-b737-291f2f1b77fa (Database Looping 6:38:32: <no directory>) - 66 remaining
2016-06-22 00:28:45,056 : DEBUG : KNIME-Worker-53 : NodeContainer : Database Looping : 6:38:32 : Database Looping 6:38:32 has new state: IDLE
2016-06-22 00:28:45,056 : DEBUG : KNIME-Worker-53 : Database Looping : Database Looping : 6:38:32 : Configure succeeded. (Database Looping)
2016-06-22 00:28:45,056 : DEBUG : KNIME-Worker-53 : NodeContainer : Database Looping : 6:38:32 : Database Looping 6:38:32 has new state: CONFIGURED
2016-06-22 00:28:45,056 : DEBUG : KNIME-Worker-53 : Database Looping : Database Looping : 6:38:33 : Configure succeeded. (Database Looping)
2016-06-22 00:28:45,056 : DEBUG : KNIME-Worker-53 : NodeContainer : Database Looping : 6:38:32 : Create new compound entries 6:38 has new state: CONFIGURED
2016-06-22 00:28:45,056 : DEBUG : KNIME-Worker-53 : NodeContainer : Database Looping : 6:38:32 : Register TBS Panel v2 6 has new state: IDLE
2016-06-22 00:28:45,066 : DEBUG : KNIME-WFM-Parent-Notifier : NodeContainer : : : ROOT has new state: IDLE
Tobias,
After more thought, i think you are correct that this is due to a closed conenction. However resetting the looping node does not fix the problem (there is no discrete database conenction node in my setup). How else can I force a new connection without restarting KNIME?
Thanks.
Hi,
the ORA-00900 error is thrown by the database. So it seems to be a problem with the statement. Have you tried double quotes for the value as suggested here.
Bye
Tobias
I don't think this is the problem. I conclude this becuase after KNIME is restarted and I run the node again, the *exact* same SQL statement is submitted and it works fine with no error.
Here is the new twist: When KNIME returns the error on the first row in the loop, it claims the SQL is invalid and stops the workflow execution, but at the database level, the INSERT statement was successful and the data is correctly saved to the database -- yet somehow KNIME thinks that there was an error and stops the workflow.