PutSQL: java.sql.BatchUpdateException: invalid batch command

[#1]

Hello All,Can someone please help in understanding what I am doing wrong here?I am passing following values to PutSQL processor

sql.args.1.type

2005

sql.args.1.value

V99|1.0| E99|BILLING|1234567|11049532|350|201706|201709| R03|350|11049532|4| |KK|174|A|07/27/1948| |322| |0|F| | |201712|201709|0|168900.56|0|0|0|0|40134|201712|0|0|316| |200402|0| | | | | | | | | |POPR_CUPDT| | | | | | | | | | | |0|0|2|200402|0| | | | P03|0|1|0|0|199404|317|201711|08/17/2017| |0|201712|08/22/2017|322|0| K06|201706|111.53|111.53|14.8|96.73|111.53|15||0|0| K06|201707|117.48|117.48|15.85|101.63|117.48|15||0|0| K06|201708|227.98|227.98|44.34|183.64|227.98|30||0|0|

SQL query is:

select obds_loader.getmain(?) from dual

PutSQL processor is throwing below error.

2017-10-12 13:56:39,129 ERROR [Timer-Driven Process Thread-1] o.apache.nifi.processors.standard.PutSQL PutSQL[id=0beb1a31-980d-38f9-fc0e-6c0ac7a68984] Failed to update database due to a failed batch update, java.sql.BatchUpdateException: invalid batch command. There were a total of 1 FlowFiles that failed, 0 that succeeded, and 0 that were not execute and will be routed to retry; : java.sql.BatchUpdateException: invalid batch command
java.sql.BatchUpdateException: invalid batch command
at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:10358)
at oracle.jdbc.driver.OracleStatementWrapper.executeBatch(OracleStatementWrapper.java:230)
at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)
at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)
at org.apache.nifi.processors.standard.PutSQL.lambda$null$10(PutSQL.java:348)
at org.apache.nifi.processor.util.pattern.ExceptionHandler.execute(ExceptionHandler.java:127)
at org.apache.nifi.processors.standard.PutSQL.lambda$new$12(PutSQL.java:346)
at org.apache.nifi.processor.util.pattern.PutGroup.putFlowFiles(PutGroup.java:94)
at org.apache.nifi.processor.util.pattern.Put.onTrigger(Put.java:101)
at org.apache.nifi.processors.standard.PutSQL.lambda$onTrigger$20(PutSQL.java:554)
at org.apache.nifi.processor.util.pattern.PartialFunctions.onTrigger(PartialFunctions.java:114)
at org.apache.nifi.processor.util.pattern.RollbackOnFailure.onTrigger(RollbackOnFailure.java:184)
at org.apache.nifi.processors.standard.PutSQL.onTrigger(PutSQL.java:554)
at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1119)
at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:147)
at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:47)
at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:128)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:748)

Thanks and Regards

[#2 · Accepted solution]

Are you expecting a result set from the SELECT statement? If so then you might be better off with building a SQL query with the CLOB value inline (probably quoted and/or padded with rpad I imagine) and using ExecuteSQL vs PutSQL. The former is for queries that return result sets, the latter is for statements that don't (like INSERT).

[#3]

Thanks Matt.