[SPARK-29085] spark jdbc query to oracle "__SPARK_GEN_JDBC_SUBQUERY_NAME_" "__" not suport by ORACLE

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Duplicate
• Affects Version/s: 2.4.3
• Component/s: SQL
• Assignee: Unassigned
• Reporter: leookok
• Created: 14/Sep/19 06:37
• Updated: 12/Dec/22 18:10
• Resolved: 16/Sep/19 01:12

Description

when i use spark sql that jdbc connect to query data from oracle,

 

// code placeholder
Dataset<Row> jdbcDF = sqlContext.read()
        .format("jdbc")
        .option("url", "jdbc:oracle:thin:@192.168.2.3/orcltest11g")
        .option("query", "select * from tdb.user u")
        .option("user", "tdb")
        .option("password", "tdb")
        .load();

will throw error exception

 

 

// code placeholder
Exception in thread "main" java.sql.SQLSyntaxErrorException: ORA-00911: invalid character

 

in debug model 

 

// code placeholder
(select * from tdb.user u) __SPARK_GEN_JDBC_SUBQUERY_NAME_0

copy this sql to oracle client shell

will return same error <__SPARK_GEN_JDBC_SUBQUERY_NAME_0>

 

 

 

 

Issue Links

• duplicates: SPARK-27596 The JDBC 'query' option doesn't work for Oracle database