Fabric notebook - Oracle connection error: java.lang.ClassNotFoundException

I am trying to connect to an Oracle database, but the oracle driver is not being found by Pyspark.

First I uploaded the oracle jdbc driver v.11 in the builtin folder:

Code:

!java -version

 Output:

openjdk version "11.0.22" 2024-01-16 LTS

OpenJDK Runtime Environment Microsoft-8909545 (build 11.0.22+7-LTS)

OpenJDK 64-Bit Server VM Microsoft-8909545 (build 11.0.22+7-LTS, mixed mode, sharing)

from pyspark.sql import SparkSession

spark = SparkSession.builder \
    .appName("Iniciando com Spark") \
    .config("spark.driver.extraClassPath", "/builtin/ojdbc11.jar") \
    .getOrCreate()

properties = {
    'driver': 'oracle.jdbc.driver.OracleDriver',
    'url': "jdbc:oracle:thin://hostname:1521/db_name",
    'user': "username",
    'password': "password",
    'dbtable': '(select id from dt.users) as t'
}

df1 = spark.read.format('jdbc') \
    .option('driver', properties['driver']) \
    .option('url', properties['url']) \
    .option('user', properties['user']) \
    .option('password', properties['password']) \
    .option('dbtable', properties['dbtable']) \
     .load()

Output:

File ~/cluster-env/trident_env/lib/python3.10/site-packages/py4j/java_gateway.py:1322, in JavaMember.__call__(self, *args)

1316 command = proto.CALL_COMMAND_NAME +\

1317 self.command_header +\

1318 args_command +\

1319 proto.END_COMMAND_PART

1321 answer = self.gateway_client.send_command(command) ->

1322 return_value = get_return_value(

1323 answer, self.gateway_client, self.target_id, self.name)

1325 for temp_arg in temp_args:

1326 if hasattr(temp_arg, "_detach"

File /opt/spark/python/lib/pyspark.zip/pyspark/errors/exceptions/captured.py:169, in capture_sql_exception.<locals>.deco(*a, **kw) 167 def deco(*a: Any, **kw: Any) -> Any:

168 try: -->

169 return f(*a, **kw)

170 except Py4JJavaError as e:

171 converted = convert_exception(e.java_exception)

File ~/cluster-env/trident_env/lib/python3.10/site-packages/py4j/protocol.py:326, in get_return_value(answer, gateway_client, target_id, name)

324 value = OUTPUT_CONVERTER[type](answer[2:], gateway_client) 325 if answer[1] == REFERENCE_TYPE: -->

326 raise Py4JJavaError(

327 "An error occurred while calling {0}{1}{2}.\n".

328 format(target_id, ".", name), value) 329 else:

330 raise Py4JError( 331 "An error occurred while calling {0}{1}{2}. Trace:\n{3}\n".

332 format(target_id, ".", name, value))

Py4JJavaError: An error occurred while calling o4395.load.

: java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver

at java.base/java.net.URLClassLoader.findClass(URLClassLoader.java:476)

at java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:594) at java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:527) at org.apache.spark.sql.execution.datasources.jdbc.DriverRegistry$.register(DriverRegistry.scala:46)

at org.apache.spark.sql.execution.datasources.jdbc.JDBCOptions.$anonfun$driverClass$1(JDBCOptions.scala:103)

at org.apache.spark.sql.execution.datasources.jdbc.JDBCOptions.$anonfun$driverClass$1$adapted(JDBCOptions.scala:103)

at scala.Option.foreach(Option.scala:407)

at org.apache.spark.sql.execution.datasources.jdbc.JDBCOptions.<init>(JDBCOptions.scala:103)

at org.apache.spark.sql.execution.datasources.jdbc.JDBCOptions.<init>(JDBCOptions.scala:41)

at org.apache.spark.sql.execution.datasources.jdbc.JdbcRelationProvider.createRelation(JdbcRelationProvider.scala:34)

at org.apache.spark.sql.execution.datasources.DataSource.resolveRelation(DataSource.scala:346)

at org.apache.spark.sql.DataFrameReader.loadV1Source(DataFrameReader.scala:236)

at org.apache.spark.sql.DataFrameReader.$anonfun$load$2(DataFrameReader.scala:219) at scala.Option.getOrElse(Option.scala:189) at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:219)

at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:174)

at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.base/java.lang.reflect.Method.invoke(Method.java:566) at py4j.reflection.MethodInvoker.invoke(MethodInvoker.java:244)

at py4j.reflection.ReflectionEngine.invoke(ReflectionEngine.java:374) at py4j.Gateway.invoke(Gateway.java:282)

at py4j.commands.AbstractCommand.invokeMethod(AbstractCommand.java:132)

at py4j.commands.CallCommand.execute(CallCommand.java:79) at py4j.GatewayConnection.run(GatewayConnection.java:238)

at java.base/java.lang.Thread.run(Thread.java:829)

I have also tried to use this code below, but the error persists:

spark = SparkSession.builder \
    .appName("Iniciando com Spark") \
    .config("spark.jars", "/builtin/ojdbc11.jar") \
    .getOrCreate()

What is wrong?

Thanks!