Xiao Li created SPARK-27596:
-------------------------------
Summary: 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
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: [email protected]
For additional commands, e-mail: [email protected]
