Hi,
I'm getting an error when submitting my insert statement using the Database SQL Executor node (ERROR Database SQL Executor 0:354:356 Execute failed: ORA-00911: caractère non valide). I'm using a variable for this insert.
The same insert works fine when I run it from my SQL tool.
Any idea what's going wrong here?
Thanks for your help,
Cheers,
CL
Hi Claire,
Could you share mode details? (What is your query, what is your KNIME client version, platform, which database do you connect to?)
Thanks, gabor
I'm using Knime 2.12 (windows client) and trying to insert into an Oracle database. The insert statement is:
INSERT INTO PRINCIPALS (PRINCIPALID, PASSWORD, EMAIL, EXPIRED_PASSWORD, FULL_NAME, CUSTOMER_NAME_FK) VALUES ('ctoto','ABDWID+/OL==','toto.dupond@blabla.com',SYSDATE+300,'Toto DUPOND','XP');
Cheers,
Claire
I'm facing a rather similar problem. Im trying the following statement:
MERGE INTO STEA11_DEVICE_STATE dest
USING (SELECT "DEVICE_ID",
"MAX(INSERTED_TS)" AS "LAST_SEEN",
CASE
WHEN "MAX(INSERTED_TS)" >= TO_DATE('2015-10-26 00:00:00', 'yyyy-mm-dd HH24:MI:SS') THEN 'F'
ELSE 'T' END AS "IS_ACTIVE"
FROM (SELECT "DEVICE_ID", MAX("table_16"."INSERTED_TS") AS "MAX(INSERTED_TS)"
FROM (SELECT * FROM STEA05_MEASUREMENT WHERE INSERTED_TS >= TO_DATE('2015-10-25 00:00:00', 'yyyy-mm-dd HH24:MI:SS')) "table_16" GROUP BY "DEVICE_ID")) src
ON (src.DEVICE_ID = dest.DEVICE_ID)
WHEN matched THEN update set
dest.LAST_SEEN = src.LAST_SEEN,
dest.IS_ACTIVE = src.IS_ACTIVE
WHEN NOT matched THEN
INSERT (DEVICE_ID, LAST_SEEN, IS_ACTIVE)
VALUES (src.DEVICE_ID,src.LAST_SEEN,src.IS_ACTIVE)
/
which works fine when run outside of knime but with knime it does not.
I get an:
ERROR Database SQL Executor 0:109 Execute failed: ORA-00933: SQL-Befehl wurde nicht korrekt beendet
The Knime-Log looks like:
2016-08-09 12:05:56,589 : DEBUG : main : Database SQL Executor : Database SQL Executor : 0:109 : reset
2016-08-09 12:05:56,589 : DEBUG : main : Database SQL Executor : Database SQL Executor : 0:109 : clean output ports.
2016-08-09 12:05:56,589 : DEBUG : main : NodeContainer : Database SQL Executor : 0:109 : Database SQL Executor 0:109 has new state: IDLE
2016-08-09 12:05:56,589 : DEBUG : main : Database SQL Executor : Database SQL Executor : 0:109 : Configure succeeded. (Database SQL Executor)
2016-08-09 12:05:56,589 : DEBUG : main : NodeContainer : Database SQL Executor : 0:109 : Database SQL Executor 0:109 has new state: CONFIGURED
2016-08-09 12:05:57,260 : DEBUG : main : Database SQL Executor : Database SQL Executor : 0:109 : reset
2016-08-09 12:05:57,260 : DEBUG : main : Database SQL Executor : Database SQL Executor : 0:109 : clean output ports.
2016-08-09 12:05:57,260 : DEBUG : main : NodeContainer : Database SQL Executor : 0:109 : Database SQL Executor 0:109 has new state: IDLE
2016-08-09 12:05:57,260 : DEBUG : main : Database SQL Executor : Database SQL Executor : 0:109 : Configure succeeded. (Database SQL Executor)
2016-08-09 12:05:57,260 : DEBUG : main : NodeContainer : Database SQL Executor : 0:109 : Database SQL Executor 0:109 has new state: CONFIGURED
2016-08-09 12:05:59,677 : DEBUG : main : ExecuteAction : : : Creating execution job for 1 node(s)...
2016-08-09 12:05:59,677 : DEBUG : main : NodeContainer : : : Database SQL Executor 0:109 has new state: CONFIGURED_MARKEDFOREXEC
2016-08-09 12:05:59,677 : DEBUG : main : NodeContainer : : : Database SQL Executor 0:109 has new state: CONFIGURED_QUEUED
2016-08-09 12:05:59,677 : DEBUG : main : NodeContainer : : : STEA-15-Device-State 0 has new state: EXECUTING
2016-08-09 12:05:59,677 : DEBUG : KNIME-Worker-15 : WorkflowManager : Database SQL Executor : 0:109 : Database SQL Executor 0:109 doBeforePreExecution
2016-08-09 12:05:59,677 : DEBUG : KNIME-WFM-Parent-Notifier : NodeContainer : : : ROOT has new state: EXECUTING
2016-08-09 12:05:59,677 : DEBUG : KNIME-Worker-15 : NodeContainer : Database SQL Executor : 0:109 : Database SQL Executor 0:109 has new state: PREEXECUTE
2016-08-09 12:05:59,677 : DEBUG : KNIME-Worker-15 : WorkflowManager : Database SQL Executor : 0:109 : Database SQL Executor 0:109 doBeforeExecution
2016-08-09 12:05:59,677 : DEBUG : KNIME-Worker-15 : NodeContainer : Database SQL Executor : 0:109 : Database SQL Executor 0:109 has new state: EXECUTING
2016-08-09 12:05:59,677 : DEBUG : KNIME-Worker-15 : WorkflowFileStoreHandlerRepository : Database SQL Executor : 0:109 : Adding handler 40dfe8d8-0928-4fa8-ac5c-ddb9daee419d (Database SQL Executor 0:109: <no directory>) - 5 in total
2016-08-09 12:05:59,677 : DEBUG : KNIME-Worker-15 : LocalNodeExecutionJob : Database SQL Executor : 0:109 : Database SQL Executor 0:109 Start execute
2016-08-09 12:05:59,693 : DEBUG : KNIME-Worker-15 : DatabaseDriverLoader : Database SQL Executor : 0:109 : Database driver oracle.jdbc.OracleDriver retrieved from driver map
2016-08-09 12:05:59,709 : DEBUG : KNIME-Worker-15 : DatabaseConnectionSettings : Database SQL Executor : 0:109 : Executing SQL statement "MERGE INTO STEA11_DEVICE_STATE dest
USING (SELECT "DEVICE_ID",
"MAX(INSERTED_TS)" AS "LAST_SEEN",
CASE
WHEN "MAX(INSERTED_TS)" >= TO_DATE('2015-10-26 00:00:00', 'yyyy-mm-dd HH24:MI:SS') THEN 'F'
ELSE 'T' END AS "IS_ACTIVE"
FROM (SELECT "DEVICE_ID", MAX("table_16"."INSERTED_TS") AS "MAX(INSERTED_TS)"
FROM (SELECT * FROM STEA05_MEASUREMENT WHERE INSERTED_TS >= TO_DATE('2015-10-25 00:00:00', 'yyyy-mm-dd HH24:MI:SS')) "table_16" GROUP BY "DEVICE_ID")) src
ON (src.DEVICE_ID = dest.DEVICE_ID)
WHEN matched THEN update set
dest.LAST_SEEN = src.LAST_SEEN,
dest.IS_ACTIVE = src.IS_ACTIVE
WHEN NOT matched THEN
INSERT (DEVICE_ID, LAST_SEEN, IS_ACTIVE)
VALUES (src.DEVICE_ID,src.LAST_SEEN,src.IS_ACTIVE)
/"
2016-08-09 12:05:59,740 : DEBUG : KNIME-Worker-15 : Database SQL Executor : Database SQL Executor : 0:109 : reset
2016-08-09 12:05:59,740 : ERROR : KNIME-Worker-15 : Database SQL Executor : Database SQL Executor : 0:109 : Execute failed: ORA-00933: SQL-Befehl wurde nicht korrekt beendet
2016-08-09 12:05:59,740 : DEBUG : KNIME-Worker-15 : Database SQL Executor : Database SQL Executor : 0:109 : Execute failed: ORA-00933: SQL-Befehl wurde nicht korrekt beendet
java.sql.SQLSyntaxErrorException: ORA-00933: SQL-Befehl wurde nicht korrekt beendet
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:210)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:30)
at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:931)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1151)
at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1795)
at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1748)
at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:334)
at org.knime.core.node.port.database.DatabaseConnectionSettings.execute(DatabaseConnectionSettings.java:686)
at org.knime.base.node.io.database.DBSQLExecutorNodeModel.execute(DBSQLExecutorNodeModel.java:130)
at org.knime.core.node.NodeModel.executeModel(NodeModel.java:563)
at org.knime.core.node.Node.invokeFullyNodeModelExecute(Node.java:1136)
at org.knime.core.node.Node.execute(Node.java:932)
at org.knime.core.node.workflow.NativeNodeContainer.performExecuteNode(NativeNodeContainer.java:554)
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(Unknown Source)
at java.util.concurrent.FutureTask.run(Unknown Source)
at org.knime.core.util.ThreadPool$MyFuture.run(ThreadPool.java:123)
at org.knime.core.util.ThreadPool$Worker.run(ThreadPool.java:246)
2016-08-09 12:05:59,740 : DEBUG : KNIME-Worker-15 : WorkflowManager : Database SQL Executor : 0:109 : Database SQL Executor 0:109 doBeforePostExecution
2016-08-09 12:05:59,740 : DEBUG : KNIME-Worker-15 : NodeContainer : Database SQL Executor : 0:109 : Database SQL Executor 0:109 has new state: POSTEXECUTE
2016-08-09 12:05:59,740 : DEBUG : KNIME-Worker-15 : WorkflowManager : Database SQL Executor : 0:109 : Database SQL Executor 0:109 doAfterExecute - failure
2016-08-09 12:05:59,740 : DEBUG : KNIME-Worker-15 : Database SQL Executor : Database SQL Executor : 0:109 : reset
2016-08-09 12:05:59,740 : DEBUG : KNIME-Worker-15 : Database SQL Executor : Database SQL Executor : 0:109 : clean output ports.
2016-08-09 12:05:59,740 : DEBUG : KNIME-Worker-15 : WorkflowFileStoreHandlerRepository : Database SQL Executor : 0:109 : Removing handler 40dfe8d8-0928-4fa8-ac5c-ddb9daee419d (Database SQL Executor 0:109: <no directory>) - 4 remaining
2016-08-09 12:05:59,740 : DEBUG : KNIME-Worker-15 : NodeContainer : Database SQL Executor : 0:109 : Database SQL Executor 0:109 has new state: IDLE
2016-08-09 12:05:59,740 : DEBUG : KNIME-Worker-15 : Database SQL Executor : Database SQL Executor : 0:109 : Configure succeeded. (Database SQL Executor)
2016-08-09 12:05:59,740 : DEBUG : KNIME-Worker-15 : NodeContainer : Database SQL Executor : 0:109 : Database SQL Executor 0:109 has new state: CONFIGURED
2016-08-09 12:05:59,740 : DEBUG : KNIME-Worker-15 : NodeContainer : Database SQL Executor : 0:109 : STEA-15-Device-State 0 has new state: IDLE
2016-08-09 12:05:59,740 : DEBUG : KNIME-WFM-Parent-Notifier : NodeContainer : : : ROOT has new state: IDLE
A second question i would like to ask is how to get the SQL-Statement from a preceeding node:
SQL Statement:
SELECT "DEVICE_ID","MAX(INSERTED_TS)" AS "LAST_SEEN","IS_ACTIVE(INSERTED_TS)" AS "IS_ACTIVE" FROM (SELECT "DEVICE_ID", MAX("table_1662129960"."INSERTED_TS") AS "MAX(INSERTED_TS)", CASE WHEN MAX("table_1662129960"."INSERTED_TS") >= TO_DATE('2015-10-26 00:00:00', 'yyyy-mm-dd HH24:MI:SS') THEN 'F' ELSE 'T' END AS "IS_ACTIVE(INSERTED_TS)" FROM (SELECT * FROM STEA05_MEASUREMENT WHERE INSERTED_TS >= TO_DATE('2014-10-25 00:00:00', 'yyyy-mm-dd HH24:MI:SS')) "table_1662129960" GROUP BY "DEVICE_ID") "table_1063034961"
into the above SQL-statement something with: $$SQL_STATEMENT$$ or #TABLE# likne?
MERGE INTO STEA11_DEVICE_STATE dest
USING ($$SQL_STATEMENT$$) "table_16" GROUP BY "DEVICE_ID")) src
ON (src.DEVICE_ID = dest.DEVICE_ID)
WHEN matched THEN update set
dest.LAST_SEEN = src.LAST_SEEN,
dest.IS_ACTIVE = src.IS_ACTIVE
WHEN NOT matched THEN
INSERT (DEVICE_ID, LAST_SEEN, IS_ACTIVE)
VALUES (src.DEVICE_ID,src.LAST_SEEN,src.IS_ACTIVE)
/
Hello,
regarding the question "how to get the SQL-Statement from a preceeding node":
You can use the SQL Extract and SQL Inject node to extract a SQL statement, manipulate it with KNIME nodes e.g. >String Manipulator and execute it in the db. The extracted statement could be also injected in any other KNIME node via flow variables. Please see attached workflow as an example.
Regarding the SQL exception. It seems you have one bracket to much in the statement. Right befor the src you have two brackets where it seems you only need one.
Bye
Tobias
Thank you for the workflow and the comment on the String Manipulation.
For the first SQL-Statement which did not run, the actual problem was the "/" in the last line. Removing it did fix the issue.