[ https://issues.apache.org/jira/browse/NIFI-5643?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Diego Queiroz updated NIFI-5643: -------------------------------- Description: When using "Custom Query" to generate a query for Oracle, it wraps the query with: {noformat} SELECT * FROM ( your_query ) AS TABLE_NAME WHERE 1 = 0{noformat} The problem is that the " AS " sintax is invalid in Oracle (see [https://www.techonthenet.com/oracle/alias.php]). The correct syntax for Oracle is: {noformat} SELECT * FROM ( your_query ) TABLE_NAME WHERE 1 = 0{noformat} Apparently, this is already solved in *[NIFI-1706|https://github.com/apache/nifi/commit/82ac815536d53c00f848f2eae79474035a9eb126#diff-23f5f3a6652c943e1824efb3b9528d0d]* (so master seems to be OK), but if there are plans for a new release of 1.7.x, a fix for this would be great. I'll drop the log here to help people to find this thread: {noformat} 2018-09-27 17:41:45,293 ERROR [Timer-Driven Process Thread-82] o.a.n.p.standard.QueryDatabaseTable QueryDatabaseTable[id=5dfe3f5d-69b8-1df7-1379-140a9e518ac3] Failed to process session due to org.apache.nifi.processor.exception.ProcessException: Unable to communicate with database in order to determine column types: org.apache.nifi.processor.exception.ProcessException: Unable to communicate with database in order to determine column types org.apache.nifi.processor.exception.ProcessException: Unable to communicate with database in order to determine column types at org.apache.nifi.processors.standard.AbstractDatabaseFetchProcessor.setup(AbstractDatabaseFetchProcessor.java:309) at org.apache.nifi.processors.standard.AbstractDatabaseFetchProcessor.setup(AbstractDatabaseFetchProcessor.java:232) at org.apache.nifi.processors.standard.QueryDatabaseTable.onTrigger(QueryDatabaseTable.java:232) at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1165) at org.apache.nifi.controller.tasks.ConnectableTask.invoke(ConnectableTask.java:203) at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:117) 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:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:748) Caused by: java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396) at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227) at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531) at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:195) at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:876) at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1175) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1296) at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1498) at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:406) at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208) at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208) at org.apache.nifi.processors.standard.AbstractDatabaseFetchProcessor.setup(AbstractDatabaseFetchProcessor.java:269) ... 12 common frames omitted{noformat} was: When using "Custom Query" to generate a query for Oracle, it wraps the query with: {noformat} SELECT * FROM ( your_query ) AS TABLE_NAME WHERE 1 = 0{noformat} The problem is that the " AS " sintax is invalid in Oracle (see [https://www.techonthenet.com/oracle/alias.php]). The correct syntax for Oracle is: {noformat} SELECT * FROM ( your_query ) TABLE_NAME WHERE 1 = 0{noformat} Apparently, this is already solved in *[NIFI-1706|https://github.com/apache/nifi/commit/82ac815536d53c00f848f2eae79474035a9eb126#diff-23f5f3a6652c943e1824efb3b9528d0d]* (so master seems to be OK), but if there are plans for a new release of 1.7.x, a fix for this would be great. I'll drop the log here to help people to find this thread: {noformat} 2018-09-27 17:41:45,293 ERROR [Timer-Driven Process Thread-82] o.a.n.p.standard.QueryDatabaseTable QueryDatabaseTable[id=5dfe3f5d-69b8-1df7-1379-140a9e518ac3] Failed to process session due to org.apache.nifi.processor.exception.ProcessException: Unable to communicate with database in order to determine column types: org.apache.nifi.processor.exception.ProcessException: Unable to communicate with database in order to determine column types org.apache.nifi.processor.exception.ProcessException: Unable to communicate with database in order to determine column types at org.apache.nifi.processors.standard.AbstractDatabaseFetchProcessor.setup(AbstractDatabaseFetchProcessor.java:309) at org.apache.nifi.processors.standard.AbstractDatabaseFetchProcessor.setup(AbstractDatabaseFetchProcessor.java:232) at org.apache.nifi.processors.standard.QueryDatabaseTable.onTrigger(QueryDatabaseTable.java:232) at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1165) at org.apache.nifi.controller.tasks.ConnectableTask.invoke(ConnectableTask.java:203) at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:117) 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:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:748) Caused by: java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396) at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227) at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531) at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:195) at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:876) at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1175) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1296) at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1498) at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:406) at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208) at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208) at org.apache.nifi.processors.standard.AbstractDatabaseFetchProcessor.setup(AbstractDatabaseFetchProcessor.java:269) ... 12 common frames omitted{noformat} > QueryDatabaseTable custom query generates invalid Oracle queries > ---------------------------------------------------------------- > > Key: NIFI-5643 > URL: https://issues.apache.org/jira/browse/NIFI-5643 > Project: Apache NiFi > Issue Type: Bug > Components: Extensions > Affects Versions: 1.7.1 > Reporter: Diego Queiroz > Priority: Major > > When using "Custom Query" to generate a query for Oracle, it wraps the query > with: > {noformat} > SELECT * FROM ( your_query ) AS TABLE_NAME WHERE 1 = 0{noformat} > The problem is that the " AS " sintax is invalid in Oracle (see > [https://www.techonthenet.com/oracle/alias.php]). > The correct syntax for Oracle is: > {noformat} > SELECT * FROM ( your_query ) TABLE_NAME WHERE 1 = 0{noformat} > Apparently, this is already solved in > *[NIFI-1706|https://github.com/apache/nifi/commit/82ac815536d53c00f848f2eae79474035a9eb126#diff-23f5f3a6652c943e1824efb3b9528d0d]* > (so master seems to be OK), but if there are plans for a new release of > 1.7.x, a fix for this would be great. > > I'll drop the log here to help people to find this thread: > {noformat} > 2018-09-27 17:41:45,293 ERROR [Timer-Driven Process Thread-82] > o.a.n.p.standard.QueryDatabaseTable > QueryDatabaseTable[id=5dfe3f5d-69b8-1df7-1379-140a9e518ac3] Failed to process > session due to org.apache.nifi.processor.exception.ProcessException: Unable > to communicate with database in order to determine column types: > org.apache.nifi.processor.exception.ProcessException: Unable to communicate > with database in order to determine column types > org.apache.nifi.processor.exception.ProcessException: Unable to communicate > with database in order to determine column types > at > org.apache.nifi.processors.standard.AbstractDatabaseFetchProcessor.setup(AbstractDatabaseFetchProcessor.java:309) > at > org.apache.nifi.processors.standard.AbstractDatabaseFetchProcessor.setup(AbstractDatabaseFetchProcessor.java:232) > at > org.apache.nifi.processors.standard.QueryDatabaseTable.onTrigger(QueryDatabaseTable.java:232) > at > org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1165) > at > org.apache.nifi.controller.tasks.ConnectableTask.invoke(ConnectableTask.java:203) > at > org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:117) > 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:1149) > at > java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) > at java.lang.Thread.run(Thread.java:748) > Caused by: java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not > properly ended > at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447) > at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396) > at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951) > at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513) > at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227) > at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531) > at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:195) > at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:876) > at > oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1175) > at > oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1296) > at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1498) > at > oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:406) > at > org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208) > at > org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208) > at > org.apache.nifi.processors.standard.AbstractDatabaseFetchProcessor.setup(AbstractDatabaseFetchProcessor.java:269) > ... 12 common frames omitted{noformat} -- This message was sent by Atlassian JIRA (v7.6.3#76005)