aidp-oracle-db

# `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`](../aidp-alh/SKILL.md). Autonomous always uses TCPS + wallet (or IAM DB-Token) — `aidp-oracle-db` won't work. - For Exadata Cloud Service → [`aidp-exacs`](../aidp-exacs/SKILL.md). 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) ```python 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) ```python 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: ```python 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()`: ```python 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 ```python 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 ```python 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](../../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`](https://github.com/oracle-samples/oracle-aidp-samples/blob/main/data-engineering/ingestion/Read_Write_Oracle_Ecosystem_Connectors/Oracle_Database.ipynb)