I am trying to execute a stored procedure with Knime using Oracle 12.2 as the Database.
The DB SQL Injector looks like this (ignore the Oracle Procedure syntax):
DROP PROCEDURE simpleproc;
CREATE PROCEDURE simpleproc AS begin SELECT count(*) FROM CONTEXTS;
The above executes successfully and I see the Procedure is created.
However it is when using DB Query Reader to execute this procedure that things go wrong:
CALL simpleproc
The error in Knime console is:
ERROR DB Query Reader 3:772 Execute failed: ORA-06576: not a valid function or procedure name
This is just a sample, I have other Procedures to execute that already exist on Oracle side. But to start, I followed a Knime forum advice and implemented what was provided there on my installation but with Oracle as DB. I have also tried calling an existing procedure that is used in packages already, directly from Knime, like above, and that too failed with the same error.
The Knime Logs:
2021-04-29 19:44:18,362 : ERROR : KNIME-Worker-148-DB Query Reader 3:772 : : Node : DB Query Reader : 3:772 : Execute failed: ORA-06576: not a valid function or procedure name
java.sql.SQLException: ORA-06576: not a valid function or procedure name
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:494)
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:446)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1054)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:623)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:252)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:612)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:213)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:37)
at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:896)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1119)
at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1276)
at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:366)
at org.knime.database.connection.wrappers.AbstractStatementWrapper.executeQuery(AbstractStatementWrapper.java:92)
at org.knime.database.connection.wrappers.AbstractStatementWrapper.executeQuery(AbstractStatementWrapper.java:92)
at org.knime.database.connection.wrappers.AbstractStatementWrapper.executeQuery(AbstractStatementWrapper.java:92)
at org.knime.database.connection.impl.monitored.MonitoredStatement.executeQuery(MonitoredStatement.java:81)
at org.knime.database.connection.wrappers.AbstractStatementWrapper.executeQuery(AbstractStatementWrapper.java:92)
at org.knime.database.agent.reader.impl.DefaultDBReader.read(DefaultDBReader.java:147)
at org.knime.database.node.io.reader.query.DBQueryReaderNodeModel.execute(DBQueryReaderNodeModel.java:165)
at org.knime.core.node.NodeModel.executeModel(NodeModel.java:576)
at org.knime.core.node.Node.invokeFullyNodeModelExecute(Node.java:1245)
at org.knime.core.node.Node.execute(Node.java:1025)
at org.knime.core.node.workflow.NativeNodeContainer.performExecuteNode(NativeNodeContainer.java:558)
at org.knime.core.node.exec.LocalNodeExecutionJob.mainExecute(LocalNodeExecutionJob.java:95)
at org.knime.core.node.workflow.NodeExecutionJob.internalRun(NodeExecutionJob.java:201)
at org.knime.core.node.workflow.NodeExecutionJob.run(NodeExecutionJob.java:117)
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: Error : 6576, Position : 5, Sql = call simpleproc, OriginalSql = call simpleproc, Error Msg = ORA-06576: not a valid function or procedure name
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:498)
... 31 more
Iād be really grateful if anyone would spare some time. I have tried multiple knime & stackoverflow forums but havenāt helped me so far.