Exception in ExecuteSQL processor

[#1]

After fixing the empty schema bug, when trying to pull data from Oracle server to PutFile I get the following exception:

2015-10-20 15:59:59,859 ERROR [Timer-Driven Process Thread-9] o.a.nifi.processors.standard.ExecuteSQL ExecuteSQL[id=f92d313d-fc87-42a9-ac24-ce7d9b6972c9] ExecuteSQL[id=f92d313d-fc87-42a9-ac24-ce7d9b6972c9] failed to process due to org.apache.avro.file.DataFileWriter$AppendWriteException: java.lang.ClassCastException: java.math.BigDecimal cannot be cast to java.lang.CharSequence; rolling back session: org.apache.avro.file.DataFileWriter$AppendWriteException: java.lang.ClassCastException: java.math.BigDecimal cannot be cast to java.lang.CharSequence
2015-10-20 15:59:59,860 ERROR [Timer-Driven Process Thread-9] o.a.nifi.processors.standard.ExecuteSQL ExecuteSQL[id=f92d313d-fc87-42a9-ac24-ce7d9b6972c9] ExecuteSQL[id=f92d313d-fc87-42a9-ac24-ce7d9b6972c9] failed to process session due to org.apache.avro.file.DataFileWriter$AppendWriteException: java.lang.ClassCastException: java.math.BigDecimal cannot be cast to java.lang.CharSequence: org.apache.avro.file.DataFileWriter$AppendWriteException: java.lang.ClassCastException: java.math.BigDecimal cannot be cast to java.lang.CharSequence
2015-10-20 15:59:59,860 WARN [Timer-Driven Process Thread-9] o.a.nifi.processors.standard.ExecuteSQL ExecuteSQL[id=f92d313d-fc87-42a9-ac24-ce7d9b6972c9] Processor Administratively Yielded for 1 sec due to processing failure
2015-10-20 15:59:59,860 WARN [Timer-Driven Process Thread-9] o.a.n.c.t.ContinuallyRunProcessorTask Administratively Yielding ExecuteSQL[id=f92d313d-fc87-42a9-ac24-ce7d9b6972c9] due to uncaught Exception: org.apache.avro.file.DataFileWriter$AppendWriteException: java.lang.ClassCastException: java.math.BigDecimal cannot be cast to java.lang.CharSequence
2015-10-20 15:59:59,864 WARN [Timer-Driven Process Thread-9] o.a.n.c.t.ContinuallyRunProcessorTask
org.apache.avro.file.DataFileWriter$AppendWriteException: java.lang.ClassCastException: java.math.BigDecimal cannot be cast to java.lang.CharSequence
  at org.apache.avro.file.DataFileWriter.append(DataFileWriter.java:296) ~[na:na]
  at org.apache.nifi.processors.standard.util.JdbcCommon.convertToAvroStream(JdbcCommon.java:87) ~[na:na]
  at org.apache.nifi.processors.standard.ExecuteSQL$1.process(ExecuteSQL.java:142) ~[na:na]
  at org.apache.nifi.controller.repository.StandardProcessSession.write(StandardProcessSession.java:1937) ~[nifi-framework-core-0.3.0.jar:0.3.0]
  at org.apache.nifi.processors.standard.ExecuteSQL.onTrigger(ExecuteSQL.java:136) ~[na:na]
  at org.apache.nifi.processor.AbstractProcessor.onTrigger(AbstractProcessor.java:27) ~[nifi-api-0.3.0.jar:0.3.0]
  at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1077) ~[nifi-framework-core-0.3.0.jar:0.3.0]
  at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:127) [nifi-framework-core-0.3.0.jar:0.3.0]
  at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:49) [nifi-framework-core-0.3.0.jar:0.3.0]
  at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:119) [nifi-framework-core-0.3.0.jar:0.3.0]
  at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471) [na:1.7.0_79]
  at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:304) [na:1.7.0_79]
  at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:178) [na:1.7.0_79]
  at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293) [na:1.7.0_79]
  at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [na:1.7.0_79]
  at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [na:1.7.0_79]
  at java.lang.Thread.run(Thread.java:745) [na:1.7.0_79]
Caused by: java.lang.ClassCastException: java.math.BigDecimal cannot be cast to java.lang.CharSequence
  at org.apache.avro.generic.GenericDatumWriter.writeString(GenericDatumWriter.java:213) ~[na:na]
  at org.apache.avro.generic.GenericDatumWriter.writeString(GenericDatumWriter.java:208) ~[na:na]
  at org.apache.avro.generic.GenericDatumWriter.write(GenericDatumWriter.java:76) ~[na:na]
  at org.apache.avro.generic.GenericDatumWriter.writeField(GenericDatumWriter.java:114) ~[na:na]
  at org.apache.avro.generic.GenericDatumWriter.writeRecord(GenericDatumWriter.java:104) ~[na:na]
  at org.apache.avro.generic.GenericDatumWriter.write(GenericDatumWriter.java:66) ~[na:na]
  at org.apache.avro.generic.GenericDatumWriter.write(GenericDatumWriter.java:58) ~[na:na]
  at org.apache.avro.file.DataFileWriter.append(DataFileWriter.java:290) ~[na:na]

[#2]

Can you please share the source table DDL?

[#3]

CREATE TABLE PDMS.PROTOCOL

(

FLDNUM NUMBER NOT NULL,

PROTOCOL VARCHAR2(130 CHAR),

ON_PDMS VARCHAR2(130 CHAR),

TITLE VARCHAR2(550 CHAR),

CHAIRMAN NUMBER,

MANAGER NUMBER,

MULTICENTER_TRIAL VARCHAR2(130 CHAR),

OB_BLIND_STUDY_YN VARCHAR2(130 CHAR),

CREATE_DTM DATE,

CREATE_USER VARCHAR2(30 BYTE),

LAST_UPDATED_DTM DATE,

LAST_UPDATE_USER VARCHAR2(30 BYTE),

CONSTRAINT PROTOCOL_PK PRIMARY KEY(FLDNUM) ENABLE

)

[#4]

I think the error is coming from a NUMBER with unlimited precision being treated as java.math.BigDecimal. Will ask a developer to take a loot next.

[#5 · Accepted solution]

Vedant,

I have submitted a patch for this issue here: https://issues.apache.org/jira/browse/NIFI-1056

Unfortunately, I do not have a way to test against Oracle database because it requires licenses. However, I did create a unit test to return a BigDecimal for a NUMERIC type, verified that it failed, and addressed the issue so that it now works. If you have a chance, please give it a try!

Thanks

-Mark

[#6]

For the record, the patch has now made it into the next NiFi release.