`aidp-oracle-db` — Generic Oracle Database via AIDP…

Stats

Actions

Tags

`aidp-oracle-db` — Generic Oracle Database via AIDP…

`aidp-oracle-db` — Generic Oracle Database via AIDP `aidataplatform`

For non-Autonomous Oracle DBs — Oracle on Compute, Base DB, on-prem, customer-managed Oracle 19c/21c/23ai/26ai. Auth is plain user/password over TCP 1521.

When to use

User wants to read or write a non-Autonomous Oracle Database from an AIDP notebook.
User mentions: "Oracle Database", "generic Oracle DB", "on-prem Oracle", "Oracle 19c", "Oracle 21c", "Oracle 23ai non-Autonomous", "Base DB", "Oracle on Compute".
User has a host/port + plain user/password (no wallet, no IAM DB-Token).

When NOT to use

For Autonomous DB family (ALH/ADW/ATP) → aidp-alh. Autonomous always uses TCPS + wallet (or IAM DB-Token) — aidp-oracle-db won't work.
For Exadata Cloud Service → aidp-exacs. ExaCS has its own NNE pattern.
For PeopleSoft / Siebel — those run on Oracle DB but have their own dedicated skills with the right schema defaults.

Prerequisites in the AIDP notebook

Helpers on sys.path (run aidp-connectors-bootstrap first).
Network: cluster must reach the Oracle DB host on the listener port (typically 1521). For private DBs in customer VCNs, VCN peering is required.
Env vars / OCI Vault secrets:
- ORADB_HOST, ORADB_PORT (typically 1521)
- ORADB_DATABASE_NAME (Oracle SID or service name)
- ORADB_USER, ORADB_PASSWORD
- ORADB_SCHEMA, ORADB_TABLE

Read (inline options)

import os
from oracle_ai_data_platform_connectors.aidataplatform import (
    AIDP_FORMAT, aidataplatform_options,
)

opts = aidataplatform_options(
    type="ORACLE_DB",
    host=os.environ["ORADB_HOST"],
    port=int(os.environ.get("ORADB_PORT", "1521")),
    database_name=os.environ["ORADB_DATABASE_NAME"],
    user=os.environ["ORADB_USER"],
    password=os.environ["ORADB_PASSWORD"],
    schema=os.environ["ORADB_SCHEMA"],
    table=os.environ["ORADB_TABLE"],
)
df = spark.read.format(AIDP_FORMAT).options(**opts).load()
df.show(10)

Write (inline options)

opts = aidataplatform_options(
    type="ORACLE_DB",
    host=os.environ["ORADB_HOST"],
    port=int(os.environ.get("ORADB_PORT", "1521")),
    database_name=os.environ["ORADB_DATABASE_NAME"],
    user=os.environ["ORADB_USER"],
    password=os.environ["ORADB_PASSWORD"],
    schema=os.environ["ORADB_SCHEMA"],
    table=os.environ["ORADB_TARGET_TABLE"],
    extra={"write.mode": "APPEND"},   # CREATE | APPEND | OVERWRITE
)
df.write.format(AIDP_FORMAT).options(**opts).save()

Read via existing external catalog (`catalog.id`)

If your AIDP workspace already has the Oracle DB registered as an external catalog, drop the host/port/credentials entirely and reference the catalog by id:

opts = aidataplatform_options(
    type="ORACLE_DB",
    schema=os.environ["ORADB_SCHEMA"],
    table=os.environ["ORADB_TABLE"],
    extra={"catalog.id": os.environ["ORADB_CATALOG_ID"]},
)
df = spark.read.format(AIDP_FORMAT).options(**opts).load()
df.show(10)

Or use three-part naming via spark.table():

df = spark.table(f"{os.environ['ORADB_CATALOG_ID']}.{os.environ['ORADB_SCHEMA']}.{os.environ['ORADB_TABLE']}")
df.show(10)
df.write.mode("overwrite").saveAsTable(
    f"{os.environ['ORADB_CATALOG_ID']}.{os.environ['ORADB_SCHEMA']}.{os.environ['ORADB_TARGET_TABLE']}"
)

Pushdown SQL

opts = aidataplatform_options(
    type="ORACLE_DB",
    host=os.environ["ORADB_HOST"],
    port=int(os.environ.get("ORADB_PORT", "1521")),
    database_name=os.environ["ORADB_DATABASE_NAME"],
    user=os.environ["ORADB_USER"],
    password=os.environ["ORADB_PASSWORD"],
    extra={
        "pushdown.sql": (
            "SELECT department_id, COUNT(*) AS headcount, SUM(salary) AS total "
            "FROM HR.EMPLOYEES "
            "WHERE hire_date >= DATE '2024-01-01' "
            "GROUP BY department_id"
        ),
    },
)
df = spark.read.format(AIDP_FORMAT).options(**opts).load()
df.show()

Gotchas

database.name is the Oracle SID or service name, not the schema. Easy mix-up.
Plain user/password only. Wallets and IAM DB-Tokens are Autonomous-DB-only — for those, use aidp-alh.
Network reachability is the most common failure. From the cluster: socket.create_connection((host, 1521), timeout=8). Failure = network problem (NSG / route table / DNS), not auth.
Write modes — CREATE (fail if exists), APPEND, OVERWRITE. Use MERGE only via pushdown.sql (MERGE INTO ... WHEN MATCHED ...) or three-part saveAsTable with write.merge.keys.
NLS settings. Default Oracle dates can come back with timezone surprises. Set extra={"oracle.jdbc.timezoneAsRegion": "false"} if you see TZ drift.

References