[
https://issues.apache.org/jira/browse/SPARK-27596?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Xiao Li resolved SPARK-27596.
-----------------------------
Resolution: Fixed
Fix Version/s: 2.4.4
3.0.0
> The JDBC 'query' option doesn't work for Oracle database
> --------------------------------------------------------
>
> Key: SPARK-27596
> URL: https://issues.apache.org/jira/browse/SPARK-27596
> Project: Spark
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 2.4.2
> Reporter: Xiao Li
> Assignee: Dilip Biswal
> Priority: Major
> Fix For: 3.0.0, 2.4.4
>
>
> For the JDBC option `query`, we use the identifier name to start with
> underscore: s"(${subquery})
> __SPARK_GEN_JDBC_SUBQUERY_NAME_${curId.getAndIncrement()}". This is not
> supported by Oracle.
> The Oracle doesn't seem to support identifier name to start with non-alphabet
> character (unless it is quoted) and has length restrictions as well.
> https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements008.htm
> {code:java}
> Nonquoted identifiers must begin with an alphabetic character from your
> database character set. Quoted identifiers can begin with any character as
> per below documentation -
> Nonquoted identifiers can contain only alphanumeric characters from your
> database character set and the underscore (_), dollar sign ($), and pound
> sign (#). Database links can also contain periods (.) and "at" signs (@).
> Oracle strongly discourages you from using $ and # in nonquoted identifiers.
> {code}
> The alias name '_SPARK_GEN_JDBC_SUBQUERY_NAME<int value>' should be fixed to
> remove "__" prefix ( or make it quoted.not sure if it may impact other
> sources) to make it work for Oracle. Also the length should be limited as it
> is hitting below error on removing the prefix.
> {code:java}
> java.sql.SQLSyntaxErrorException: ORA-00972: identifier is too long
> {code}
> It can be verified using below sqlfiddle link.
> http://www.sqlfiddle.com/#!4/9bbe9a/10050
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
