aidp-oracle-db

name	aidp-oracle-db
description	Read or write an Oracle Database (Compute / Base DB / on-prem / Oracle 19c, 21c, 23ai, 26ai non-Autonomous) from an AIDP notebook via the AIDP `aidataplatform` Spark format handler. Use when the user mentions Oracle Database, generic Oracle DB, on-prem Oracle, plain Oracle JDBC, port 1521, non-Autonomous Oracle. Read-write. Auth is host/port + database name + user/password.
allowed-tools	Read, Write, Edit, Bash

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

1. Helpers on sys.path (run aidp-connectors-bootstrap first).
2. Network: cluster must reach the Oracle DB host on the listener port (typically 1521). For private DBs in customer VCNs, VCN peering is required.
3. Env vars / OCI Vault secrets:
   • ORADB_HOST, ORADB_PORT (typically 1521)
   • ORADB_DATABASE_NAME (Oracle service name) or ORADB_DATABASE_SID
   • 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"],
    extra={
        # Optional official connector options:
        # "database.sid": os.environ["ORADB_DATABASE_SID"],
        # "row.limit": "1000",
        # "fetch.size": "10000",
        # "partition.column": "ID",
        # "partition.num": "8",
        # "partition.lower": "1",
        # "partition.upper": "1000000",
    },
)
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 | MERGE
        # "write.merge.keys": "ID",
        # "write.batch.size": "10000",
        # "write.empty.value.as.null": "true",
        # "preserve.oracle.column.types": "EMBEDDING VECTOR(512, FLOAT32), DOC JSON",
        # "oracle.write.native.boolean": "true",
        # "oracle.append.hint.enabled": "true",
    },
)
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()

External catalog pushdown

df = spark.read.table(
    f"{os.environ['ORADB_CATALOG_ID']}.{os.environ['ORADB_SCHEMA']}.{os.environ['ORADB_TABLE']}"
)
df.select("DEPARTMENT_ID", "SALARY").filter("DEPARTMENT_ID = 10").limit(100).show()

df.write.option("write.mode", "MERGE").option("write.merge.keys", "EMPLOYEE_ID").insertInto(
    f"{os.environ['ORADB_CATALOG_ID']}.{os.environ['ORADB_SCHEMA']}.{os.environ['ORADB_TARGET_TABLE']}"
)

Gotchas

• database.name is the Oracle service name; database.sid is the SID. Both are supported by the official sample option table; don't confuse either with schema.
• Wallet options exist for Oracle DB in the AIDP connector (wallet.content, wallet.path, wallet.password), but plain user/password is the default sample path. For Autonomous DB, 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, APPEND, OVERWRITE, and MERGE. For MERGE, pass write.merge.keys.
• Oracle write controls — the sample exposes preserve.oracle.column.types, oracle.write.native.boolean, oracle.append.hint.enabled, overwrite.with.recreate, and merge filters. Surface these when the user asks about vectors, JSON, booleans, append performance, or upserts.
• NLS settings. Default Oracle dates can come back with timezone surprises. Set extra={"oracle.jdbc.timezoneAsRegion": "false"} if you see TZ drift.

References

• Helper: scripts/oracle_ai_data_platform_connectors/aidataplatform.py
• Official sample: oracle-samples/oracle-aidp-samples → data-engineering/ingestion/Read_Write_Oracle_Ecosystem_Connectors/Oracle_Database.ipynb