Spark - Oracle timezone error

2
votes

I am running a spark job to load to Oracle. But I am getting following error.

java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1

ORA-01882: timezone region not found

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:392)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:385)
at oracle.jdbc.driver.T4CTTIfun.processError(T4CTTIfun.java:1018)
at oracle.jdbc.driver.T4CTTIoauthenticate.processError(T4CTTIoauthenticate.java:501)

Here is what I have in my code

val oracleProps = new java.util.Properties() 

oracleProps.put("driver", oracleDriver)
oracleProps.put("driver", oracleDriver)
oracleProps.put("user", oracleUser)
oracleProps.put("password", oraclePwd)
oracleProps.put("batchsize", oracleBatchSize)

dataframe.write.mode("overwrite").jdbc(oracleUrl, oracleBaseTable, oracleProps)

The same code works from Spark-Shell but not from spark-submit. The same spark-submit works on other clusters. Appreciate you help!

2
oracleapache-sparkapache-spark-sql

2 Answers

1
votes

I had this error in pyspark oracle jdbc connection - "ORA-01882: timezone region not found". I was able to connect after setting oracle.jdbc.timezoneAsRegion to false.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0. JDBC driver used - ojdbc8.jar

df.write \
.format("jdbc") \
.option("url", "JDBC_URL") \
.option('driver', 'oracle.jdbc.driver.OracleDriver') \
.option("oracle.jdbc.timezoneAsRegion", "false") \
.option("dbtable", "SCHEMA.TABLE") \
.option("user", "USERID") \
.option("password", "PASSWORD") \
.mode("overwrite") \
.save()
0
votes

I write a program to insert data from a file to oracle database using Spark [version 2.3.0.cloudera3]. As per my program, Oracle database version is "Oracle Database 11g Enterprise Edition Release 11.2.0.1.0".

I was using Oracle JDBC ojdbc8.jar. So I encountered the following problem : 

java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1 
ORA-01882: timezone region not found.

Now I change my Oracle JDBC to:ojdbc6.jar, which is compatible with Oracle 11.2.0.1.0. And now it is working perfectly.