PutSQL error: java.sql.SQLException: Missing IN or OUT parameter at index:: 1

[#1]

I notice that my NiFi flow for loading into DB fails to load some records. The error message in nifi-app.log below. Forwarding the insert statements to retry does not help either...

2017-03-01 05:42:39,287 ERROR [Timer-Driven Process Thread-17] o.apache.nifi.processors.standard.PutSQL PutSQL[id=6f0f11db-015a-1000-ffff-ffffd3720836] Failed to update database for [StandardFlowFileRecord[uuid=d5fd46d2-3d33-4ed3-84f1-c5028106041a,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1488339757266-557, container=content1, section=557], offset=175256, length=302],offset=0,name=NPS_173_20170228.csv,size=302], StandardFlowFileRecord[uuid=93fe79f1-95ea-4d1e-b119-d678d75cb9a0,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1488339757266-557, container=content1, section=557], offset=175558, length=305],offset=0,name=NPS_173_20170228.csv,size=305], StandardFlowFileRecord[uuid=6af94c1e-379a-436b-a2cc-f068b3ed4751,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1488339757266-557, container=content1, section=557], offset=175863, length=304],offset=0,name=NPS_173_20170228.csv,size=304], StandardFlowFileRecord[uuid=5c792aac-6d7c-4099-b591-c0b49eb09105,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1488339757266-557, container=content1, section=557], offset=176167, length=301],offset=0,name=NPS_173_20170228.csv,size=301], StandardFlowFileRecord[uuid=51bf67f3-1b8f-4c87-ae8a-5085a95e4980,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1488339757266-557, container=content1, section=557], offset=176468, length=304],offset=0,name=NPS_173_20170228.csv,size=304], StandardFlowFileRecord[uuid=a06cb53b-bc96-49a8-a2a2-6e65e39b0ad0,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1488339757266-557, container=content1, section=557], offset=176772, length=305],offset=0,name=NPS_173_20170228.csv,size=305], StandardFlowFileRecord[uuid=ec9356fe-c0fb-4d06-bc61-6d2211de4367,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1488339757266-557, container=content1, section=557], offset=177077, length=301],offset=0,name=NPS_173_20170228.csv,size=301], StandardFlowFileRecord[uuid=9d3b1554-88f0-411b-bf2c-a51f8a90a06a,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1488339757266-557, container=content1, section=557], offset=177378, length=301],offset=0,name=NPS_173_20170228.csv,size=301], StandardFlowFileRecord[uuid=40137316-28a3-4262-a386-92226273605e,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1488339757266-557, container=content1, section=557], offset=177679, length=303],offset=0,name=NPS_173_20170228.csv,size=303]] due to java.sql.SQLException: Missing IN or OUT parameter at index:: 1; it is possible that retrying the operation will succeed, so routing to retry: java.sql.SQLException: Missing IN or OUT parameter at index:: 1
2017-03-01 05:42:39,296 ERROR [Timer-Driven Process Thread-14] o.apache.nifi.processors.standard.PutSQL
java.sql.SQLException: Missing IN or OUT parameter at index:: 1
        at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:1878) ~[na:na]
        at oracle.jdbc.driver.OraclePreparedStatement.addBatch(OraclePreparedStatement.java:10836) ~[na:na]
        at oracle.jdbc.driver.OraclePreparedStatementWrapper.addBatch(OraclePreparedStatementWrapper.java:1369) ~[na:na]
        at org.apache.commons.dbcp.DelegatingPreparedStatement.addBatch(DelegatingPreparedStatement.java:175) ~[na:na]
        at org.apache.commons.dbcp.DelegatingPreparedStatement.addBatch(DelegatingPreparedStatement.java:175) ~[na:na]
        at org.apache.nifi.processors.standard.PutSQL.onTrigger(PutSQL.java:276) ~[nifi-standard-processors-1.0.0.2.0.0.0-579.jar:1.0.0.2.0.0.0-579]
        at org.apache.nifi.processor.AbstractProcessor.onTrigger(AbstractProcessor.java:27) [nifi-api-1.0.0.2.0.0.0-579.jar:1.0.0.2.0.0.0-579]
        at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1064) [nifi-framework-core-1.0.0.2.0.0.0-579.jar:1.0.0.2.0.0.0-579]
        at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:136) [nifi-framework-core-1.0.0.2.0.0.0-579.jar:1.0.0.2.0.0.0-579]
        at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:47) [nifi-framework-core-1.0.0.2.0.0.0-579.jar:1.0.0.2.0.0.0-579]
        at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:132) [nifi-framework-core-1.0.0.2.0.0.0-579.jar:1.0.0.2.0.0.0-579]
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) [na:1.8.0_77]
        at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308) [na:1.8.0_77]
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180) [na:1.8.0_77]
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294) [na:1.8.0_77]
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [na:1.8.0_77]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [na:1.8.0_77]
        at java.lang.Thread.run(Thread.java:745) [na:1.8.0_77]
2017-03-01 05:42:39,296 ERROR [Timer-Driven Process Thread-17] o.apache.nifi.processors.standard.PutSQL

[#2]

What does the SQL statement look like? For the error rows, what are the flow file's attributes set to?

[#3]

There was a special character (') in one of columns that made the insert statements fail. A ReplaceText processor was used to replace the special character with an empty string before processing the insert statements. Issue resolved.

insert into TABLE(col1,col2,col3,col4,col5, col6)
values ('${csv.1}','${csv.2}',  to_timestamp(' ${csv.3}', 'YYYY-MM-DD HH24:MI:SS,FF9'),'${csv.4}','${source}', to_timestamp(' ${current_timestamp}', 'YYYY-MM-DD HH24:MI:SS,FF9'))

[#4]

@joshua_adeleke --How to check the sql queries generated. i am also facing same issue

[#5]

@Adyant001, Welcome to the Cloudera Community. As this is an older post, you would have a better chance of receiving a resolution by starting a new thread. This will also be an opportunity to provide details specific to your environment that could aid others in assisting you with a more accurate answer to your question. You can link this thread as a reference in your new post.