Hello Experts,

I am trying to use a Database Update Tool plugin in order to update a row in one Table and then insert a row in another table in a single Tool activity.

Here are my 2 queries:

a) Usage Number 1 (No semi-colon at the end of queries)

INSERT INTO APP_FD_ROUTE_NOTE(id, datecreated, datemodified, c_request_id1, c_status, c_name, c_userid, c_Role, c_notes)
VALUES(Lower(substr(SYS_GUID(),1,8)||'-'||substr(SYS_GUID(),1,8)||'-'||substr(SYS_GUID(),1,8)||'-'||substr(SYS_GUID(),1,8)),TO_TIMESTAMP(to_char(sysdate,'DD-Mon-YYYY hh24:MI:SS'),'DD-MON-RRHH24:MI:SS.FF'), TO_TIMESTAMP(to_char(sysdate,'DD-Mon-YYYY hh24:MI:SS'),'DD-MON-RRHH24:MI:SS.FF'), '#form.aas_routes.id#', 'Route deassigned from User', 'System Activity', 'System', 'System Tool', 'No Action on Route since last 24 hrs')

UPDATE APP_FD_AAS_ROUTES
SET c_currentUser='', c_status='SA_Submitted'
WHERE id='#form.aas_routes.id#'

I get an error when I use the above queries in the logs:

ERROR 19 Jun 2015 21:19:29 org.joget.apps.app.lib.DatabaseUpdateTool - Error executing plugin
java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended

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

a) Usage Number 2 (semi-colon at the end of each query)

INSERT INTO APP_FD_ROUTE_NOTE(id, datecreated, datemodified, c_request_id1, c_status, c_name, c_userid, c_Role, c_notes) 
VALUES(Lower(substr(SYS_GUID(),1,8)||'-'||substr(SYS_GUID(),1,8)||'-'||substr(SYS_GUID(),1,8)||'-'||substr(SYS_GUID(),1,8)),TO_TIMESTAMP(to_char(sysdate,'DD-Mon-YYYY hh24:MI:SS'),'DD-MON-RRHH24:MI:SS.FF'), TO_TIMESTAMP(to_char(sysdate,'DD-Mon-YYYY hh24:MI:SS'),'DD-MON-RRHH24:MI:SS.FF'), '#form.aas_routes.id#', 'Route deassigned from User', 'System Activity', 'System', 'System Tool', 'No Action on Route since last 24 hrs');

UPDATE APP_FD_AAS_ROUTES
SET c_currentUser='', c_status='SA_Submitted'
WHERE id='#form.aas_routes.id#';

I get an error when I use the above queries in the logs:

ERROR 19 Jun 2015 21:12:29 org.joget.apps.app.lib.DatabaseUpdateTool - Error executing plugin
java.sql.SQLSyntaxErrorException: ORA-00911: invalid character

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)

I can use both the queries successfully in 2 separate instances of Database Update Tool activity.