Apache NiFi DBCPConnectionPool ORA-00933: SQL command not properly ended for 'select 1 from dual;'

I am running a simple query with ExecuteSQL to Oracle 12c on NiFi:

SELECT 1 FROM DUAL; https://pasteboard.co/JCkMVmR.png

With configuration - DBCPConnectionPool 1.11.4: https://pasteboard.co/JCkiOnr.png

and getting following error:

org.apache.nifi.processor.exception.ProcessException: java.sql.SQLException: Cannot create PoolableConnectionFactory (ORA-00933: SQL command not properly ended
)
    at org.apache.nifi.dbcp.DBCPConnectionPool.getConnection(DBCPConnectionPool.java:444)
    at org.apache.nifi.dbcp.DBCPService.getConnection(DBCPService.java:55)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.apache.nifi.controller.service.StandardControllerServiceInvocationHandler.invoke(StandardControllerServiceInvocationHandler.java:87)
    at com.sun.proxy.$Proxy138.getConnection(Unknown Source)
    at org.apache.nifi.processors.standard.AbstractExecuteSQL.onTrigger(AbstractExecuteSQL.java:236)
    at org.apache.nifi.processor.AbstractProcessor.onTrigger(AbstractProcessor.java:27)
    at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1176)
    at org.apache.nifi.controller.tasks.ConnectableTask.invoke(ConnectableTask.java:213)
    at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:117)
    at org.apache.nifi.engine.FlowEngine$2.run(FlowEngine.java:110)
    at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
    at java.util.concurrent.FutureTask.runAndReset(Unknown Source)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(Unknown Source)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
    at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLException: Cannot create PoolableConnectionFactory (ORA-00933: SQL command not properly ended
)
    at org.apache.commons.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:669)
    at org.apache.commons.dbcp2.BasicDataSource.createDataSource(BasicDataSource.java:544)
    at org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:753)
    at org.apache.nifi.dbcp.DBCPConnectionPool.getConnection(DBCPConnectionPool.java:440)
    ... 20 common frames omitted
Caused by: java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended

    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:494)
    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:446)

NiFi nifi-1.11.4-RC1
Oracle 12.2.0.1.0
ojdbc8.jar (12.2.0.1)
DBCPConnectionPool 1.11.4

Any suggestion for this? Thanks in advance!

edit

As suggested in comments, tried the version without semicolon; and got different error: org.apache.nifi.processor.exception.ProcessException: org.apach e.avro.SchemaParseException: Illegal initial character: 1

Solution

To use dual you need to set a name for the returned value so that the result can map to a valid avro schema. e.g.

select 1 as test from dual

which returns test: 1 rather than just 1.