[SPARK-16625] Oracle JDBC table creation fails with ORA-00902: invalid datatype

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.6.2
• Fix Version/s: 2.1.0, 2.1.2
• Component/s: SQL
• Assignee: Yuming Wang
• Reporter: Daniel Darabos
• Created: 19/Jul/16 13:20
• Updated: 22/Sep/17 14:47
• Resolved: 05/Aug/16 15:12

Description

Unfortunately I know very little about databases, but I figure this is a bug.

I have a DataFrame with the following schema:

StructType(StructField(dst,StringType,true), StructField(id,LongType,true), StructField(src,StringType,true))

I am trying to write it to an Oracle database like this:

String url = "jdbc:oracle:thin:root/rootroot@<ip address>:1521:db";
java.util.Properties p = new java.util.Properties();
p.setProperty("driver", "oracle.jdbc.OracleDriver");
df.write().mode("overwrite").jdbc(url, "my_table", p);

And I get:

Exception in thread "main" java.sql.SQLSyntaxErrorException: ORA-00902: invalid datatype

	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:461)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:402)
	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1108)
	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:541)
	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:264)
	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:598)
	at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:213)
	at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:26)
	at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1241)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1558)
	at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:2498)
	at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:2431)
	at oracle.jdbc.driver.OracleStatementWrapper.executeUpdate(OracleStatementWrapper.java:975)
	at org.apache.spark.sql.DataFrameWriter.jdbc(DataFrameWriter.scala:302)

The Oracle server I am running against is the one I get on Amazon RDS for engine type oracle-se. The same code (with the right driver) against the RDS instance with engine type MySQL works.

The error message is the same as in https://issues.apache.org/jira/browse/SPARK-12941. Could it be that Long is also translated into the wrong data type? Thanks.

Issue Links

• links to: [Github] Pull Request #14377 (wangyum)
• links to: [Github] Pull Request #19259 (wangyum)