JDBC Oracle Connection change Container Statement

[Splush]

Hey,

Im running into a weird issue while running the following code:

def getDf(query, preamble_sql=None):
        jdbc_url = f"jdbc:oracle:thin:@//{host}:{port}/{service_name}"
        request = spark.read \
            .format("jdbc") \
            .option("driver", "oracle.jdbc.driver.OracleDriver") \
            .option("url", jdbc_url) \
            .option("query", query) \
            .option("user", username) \
            .option("password", password)
        if preamble_sql is not None:
            request = request.option("sessionInitStatement", preamble_sql)
        df = request.load()
        return df

display(getDf(
    "SELECT sys_context('USERENV','CON_NAME') AS container_name FROM dual",
    "ALTER SESSION SET CONTAINER = CDB$ROOT"
))

The query returns "CDB$ROOT" as expected.

I would assume that I can run Statements on the root container now. But when I switch the statement to the required logminer query:

display(getDf(
    "SELECT * FROM SYS.V_$ARCHIVED_LOG",
    "ALTER SESSION SET CONTAINER = CDB$ROOT"
))

Im getting a java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist error.

This query works perfectly fine in DBeaver though.

I can only believe that there is some Spark pre-checks going on that check for the table schema for example - which explains that the first query is working.

So is there any way to make this work?

Best regards

Samuel

[BigRoux]

Here is something to consider:

The issue you're experiencing likely stems from differences in behavior when accessing Oracle database objects via Spark JDBC versus other database clients like DBeaver. Specifically, Spark's JDBC interface may perform pre-checks or validation processes on the table schema before executing queries, which could trigger errors such as java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist. These checks can occur irrespective of the context or session settings that you apply, such as switching to the root container with ALTER SESSION SET CONTAINER = CDB$ROOT.

The behavior you're observing, where a straightforward query (SELECT sys_context('USERENV','CON_NAME') AS container_name FROM dual) works successfully, but other queries like SELECT * FROM SYS.V_$ARCHIVED_LOG fail, aligns with Spark's JDBC interface potentially validating table existence in a manner distinct from DBeaver. The table SYS.V_$ARCHIVED_LOG may not be directly accessible due to permissions or session scoping issues when accessing schema objects through Spark

Given this, to address your issue:

• Ensure that the user credentials provided in the JDBC connection have sufficient permissions to access SYS.V_$ARCHIVED_LOG within the specified container.

Hope this helps, Lou.