Spark-JDBC connection with Oracle Fails - java.sql.SQLSyntaxErrorException: ORA-00903: invalid table name

 

While connecting Spark with Oracle JDBC, one may observe exception like below - 

spark.read.format("jdbc").
option("url", "jdbc:oracle:thin:@//oraclehost:1521/servicename").
option("dbtable", "mytable").
option("user", "myuser").option("driver", "oracle.jdbc.driver.OracleDriver")
option("password", "mypassword").
load().write.parquet("/data/out")

java.sql.SQLSyntaxErrorException: ORA-00903: invalid table name at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:208)

The JDBC table that should be read from or written into. Note that when using it in the read path anything that is valid in a FROM clause of a SQL query can be used. For example, instead of a full table you could also use a subquery in parentheses. Like -  dbtable = '(SELECT owner, table_name FROM ALL_TABLES)' Refer following -  dbtable = 'ALL_TABLES'  -- Will Work dbtable = 'SELECT owner, table_name FROM ALL_TABLES'  -- Will NOT Work dbtable = '(SELECT owner, table_name FROM ALL_TABLES)'  -- Will Work