Skip to main content

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

  • December 14, 2021
  • 0 replies
  • 887 views

Rishi
StreamSets Employee
Forum|alt.badge.img
  • StreamSets Employee
  • 96 replies
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.

Did this topic help you find an answer to your question?

0 replies

Be the first to reply!