I try to retrieve the joined content of two tables in an Oracle Database with a very easy SQL query:
SELECT OWNER.CONTENT.ID, OWNER.CONTENT.TITLE, OWNER.BODYCONTENT.BODY
FROM OWNER.CONTENT, OWNER.BODYCONTENT
WHERE OWNER.CONTENT.ID = OWNER.BODYCONTENT.ID
AND OWNER.CONTENT.ID < 7
The query runs using Oracle SQL Developer.
I am using the ojdbc7.jar driver in NiFi. Simpler SQL queries not containing a join work with "ExecuteSQL", e. g.
SELECT OWNER.CONTENT.ID, OWNER.CONTENT.TITLE
FROM OWNER.CONTENT
WHERE OWNER.CONTENT.ID < 7
I have also tried other SQL statements (e. g. INNER JOIN). The queries work as expected using Oracle SQL Developer but not in NiFi. Can someone help me?
ERROR Message in NiFi:
2016-05-03 11:30:02,413 ERROR [Timer-Driven Process Thread-6] o.a.nifi.processors.standard.ExecuteSQL org.apache.nifi.processor.exception.ProcessException: java.sql.SQLFeatureNotSupportedException: Unsupported feature at org.apache.nifi.processors.standard.ExecuteSQL$1.process(ExecuteSQL.java:160) ~[nifi-standard-processors-0.6.1.jar:0.6.1] at org.apache.nifi.controller.repository.StandardProcessSession.write(StandardProcessSession.java:1954) ~[na:na] at org.apache.nifi.processors.standard.ExecuteSQL.onTrigger(ExecuteSQL.java:152) ~[nifi-standard-processors-0.6.1.jar:0.6.1] at org.apache.nifi.processor.AbstractProcessor.onTrigger(AbstractProcessor.java:27) [nifi-api-0.6.1.jar:0.6.1] at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1059) [nifi-framework-core-0.6.1.jar:0.6.1] at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:136) [nifi-framework-core-0.6.1.jar:0.6.1] at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:47) [nifi-framework-core-0.6.1.jar:0.6.1] at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:123) [nifi-framework-core-0.6.1.jar:0.6.1] at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471) [na:1.7.0_60] at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:304) [na:1.7.0_60] at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:178) [na:1.7.0_60] at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293) [na:1.7.0_60] at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [na:1.7.0_60] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [na:1.7.0_60] at java.lang.Thread.run(Thread.java:745) [na:1.7.0_60] Caused by: java.sql.SQLFeatureNotSupportedException: Unsupported feature at oracle.jdbc.driver.ClobAccessor.getBytes(ClobAccessor.java:534) ~[na:na] at oracle.jdbc.driver.GeneratedStatement.getBytes(GeneratedStatement.java:151) ~[na:na] at oracle.jdbc.driver.GeneratedScrollableResultSet.getBytes(GeneratedScrollableResultSet.java:326) ~[na:na] at org.apache.commons.dbcp.DelegatingResultSet.getBytes(DelegatingResultSet.java:241) ~[na:na] at org.apache.commons.dbcp.DelegatingResultSet.getBytes(DelegatingResultSet.java:241) ~[na:na] at org.apache.nifi.processors.standard.util.JdbcCommon.convertToAvroStream(JdbcCommon.java:105) ~[nifi-standard-processors-0.6.1.jar:0.6.1] at org.apache.nifi.processors.standard.util.JdbcCommon.convertToAvroStream(JdbcCommon.java:72) ~[nifi-standard-processors-0.6.1.jar:0.6.1] at org.apache.nifi.processors.standard.ExecuteSQL$1.process(ExecuteSQL.java:158) ~[nifi-standard-processors-0.6.1.jar:0.6.1] ... 14 common frames omitted
