"java.sql.SQLSyntaxErrorException: ORA-00911: invalid character" with Transformer JDBC query Origin

Transformer JDBC query origin with oracle database fails with below error when the underlying Spark version is < 2.4.4Transformer version 4.x Spark version < 2.4.4

Error message stack trace: java.sql.SQLSyntaxErrorException: ORA-00911: invalid character

at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:494) at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:446) at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1052) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:537) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:255) at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:610) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:253) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:86) at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:765) at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:921) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1099) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3640) at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1384) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3687) at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1165) at org.apache.spark.sql.execution.datasources.jdbc.JDBCRDD$.resolveTable(JDBCRDD.scala:61) at org.apache.spark.sql.execution.datasources.jdbc.JDBCRelation$.getSchema(JDBCRelation.scala:210) at org.apache.spark.sql.execution.datasources.jdbc.JdbcRelationProvider.createRelation(JdbcRelationProvider.scala:35) at org.apache.spark.sql.execution.datasources.DataSource.resolveRelation(DataSource.scala:317) at org.apache.spark.sql.DataFrameReader.loadV1Source(DataFrameReader.scala:225) at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:213) at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:169) at com.streamsets.pipeline.spark.origin.jdbc.query.JdbcQueryOrigin.generate(JdbcQueryOrigin.scala:77) at com.streamsets.datatransformer.api.operator.Origin.lambda$getOrCreate$0(Origin.java:29) at com.streamsets.datatransformer.api.operator.Operator.generateDF(Operator.java:103) at com.streamsets.datatransformer.api.operator.Operator.lambda$getOrCreate$0(Operator.java:99) at java.util.Optional.orElseGet(Optional.java:267) at com.streamsets.datatransformer.api.operator.Operator.getOrCreate(Operator.java:99) at com.streamsets.datatransformer.api.operator.Origin.getOrCreate(Origin.java:27) at com.streamsets.datatransformer.api.spark.SparkOrigin.getOrCreate(SparkOrigin.java:39) at com.streamsets.datatransformer.dag.BaseBatchDAGRunner$$anonfun$generateDataRefs$1$$anonfun$10.apply(BaseBatchDAGRunner.scala:620) at com.streamsets.datatransformer.dag.BaseBatchDAGRunner$$anonfun$generateDataRefs$1$$anonfun$10.apply(BaseBatchDAGRunner.scala:614) at scala.collection.TraversableLike$$anonfun$map$1.apply(TraversableLike.scala:234) at scala.collection.TraversableLike$$anonfun$map$1.apply(TraversableLike.scala:234) at scala.collection.mutable.ResizableArray$class.foreach(ResizableArray.scala:59) at scala.collection.mutable.ArrayBuffer.foreach(ArrayBuffer.scala:48) at scala.collection.TraversableLike$class.map(TraversableLike.scala:234) at scala.collection.AbstractTraversable.map(Traversable.scala:104) at com.streamsets.datatransformer.dag.BaseBatchDAGRunner$$anonfun$generateDataRefs$1.apply(BaseBatchDAGRunner.scala:614) at com.streamsets.datatransformer.dag.BaseBatchDAGRunner$$anonfun$generateDataRefs$1.apply(BaseBatchDAGRunner.scala:609) at scala.collection.mutable.LinkedHashMap.foreach(LinkedHashMap.scala:141) at com.streamsets.datatransformer.dag.BaseBatchDAGRunner.generateDataRefs(BaseBatchDAGRunner.scala:609) at com.streamsets.datatransformer.dag.BaseBatchDAGRunner.run(BaseBatchDAGRunner.scala:564) at com.streamsets.pipeline.spark.dag.SparkBatchDAGRunner.run(SparkBatchDAGRunner.scala:71) at com.streamsets.pipeline.spark.dag.SparkBatchDAGRunner.run(SparkBatchDAGRunner.scala:55) at com.streamsets.datatransformer.dag.runner.DataTransformerRunner.startDataTransformerDagRunner(DataTransformerRunner.java:494) at com.streamsets.datatransformer.dag.runner.DataTransformerRunner.start(DataTransformerRunner.java:274) at com.streamsets.datacollector.execution.runner.common.AsyncRunner.lambda$start$3(AsyncRunner.java:145) at com.streamsets.pipeline.lib.executor.SafeScheduledExecutorService$SafeCallable.lambda$call$0(SafeScheduledExecutorService.java:214) at com.streamsets.datacollector.security.GroupsInScope.execute(GroupsInScope.java:22) at com.streamsets.pipeline.lib.executor.SafeScheduledExecutorService$SafeCallable.call(SafeScheduledExecutorService.java:210) at com.streamsets.pipeline.lib.executor.SafeScheduledExecutorService$SafeCallable.lambda$call$0(SafeScheduledExecutorService.java:214) at com.streamsets.datacollector.security.GroupsInScope.execute(GroupsInScope.java:22) at com.streamsets.pipeline.lib.executor.SafeScheduledExecutorService$SafeCallable.call(SafeScheduledExecutorService.java:210) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293) at com.streamsets.datacollector.metrics.MetricSafeScheduledExecutorService$MetricsTask.run(MetricSafeScheduledExecutorService.java:88) 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: Error : 911, Position : 60, Sql = SELECT * FROM (SELECT * FROM CAMPTAB.CHEETAH_UNSUBSR_EMAIL) __SPARK_GEN_JDBC_SUBQUERY_NAME_0 WHERE 1=0, OriginalSql = SELECT * FROM (SELECT * FROM $DB.$TABLE) __SPARK_GEN_JDBC_SUBQUERY_NAME_0 WHERE 1=0, Error Msg = ORA-00911: invalid character

With spark < 2.4.4 it generates the SQL query as below which has incorrect syntax incompatible with oracle

SELECT * FROM (select * from sdc.dbo) __SPARK_GEN_JDBC_SUBQUERY_NAME_0 WHERE 1=0

With Spark 2.4.4+ version with correct syntax.

SELECT * FROM (select * from (select * from sdc.dbo)) SPARK_GEN_SUBQ_0 WHERE 1=0,

Solution: Please use spark version 2.4.4 or later to read from Oracle databases with the JDBC Query origin.
 

Note: To read from most database vendors, the origin requires that Apache Spark version 2.4.0 or later is installed on the Transformer machine and on each node in the cluster. To read from Oracle databases only, the JDBC Query origin requires Spark version 2.4.4 or later.