Oracle Support

[joshua-stauffer]

Problem

Oracle has been a community ask since 2018 (#393), and GX has carried partial Oracle support since April 2021 (#2609), but today that support is inconsistent. It's untested in CI, doesn't have an official install path, and is broken in significant places against enterprise deployments. This RFC proposes to close that gap.

Community Need

At the GX community meetup on August 20th, 2026, @AlmeidaJoao raised that significant parts of GX functionality are broken against enterprise Oracle, most notably that daily or monthly BatchDefinitions are unusable. They aren’t the first to raise this gap; Oracle support is a consistent community ask.

Live demand, current API

• Discourse #2349 — Oracle Support
• Discussion #10369 — How to use GX with Oracle database
• Discussion #12072 — Roadmap & Community Meetup

Oracle-specific bug reports

• Issue #10948 — GX Core is unusable on ORACLE using SQLAlchemy — Query assets wrapped in a subselect get FROM DUAL appended, producing ORA-00907.
• Issue #8618 — Query Assets don’t work for Oracle (previously reported two years earlier against 0.17.12)
• Discourse #1432 — Oracle Query Asset adds FROM DUAL clause
• PR #11447 — Community PR to fix Oracle, closed unmerged
• PR #11538 — Fix to stop wrapping unpartitioned query assets

Modernization

• Issue #9899 — Update from cx_Oracle to oracledb

Pre-v1 tail

• Issue #393 — SQLAlchemy error connecting to Oracle
• Discourse #681 — Onboarding friction setting up GX with Oracle
• Discourse #976 — Oracle syntax error: Generated WHERE TRUE predicates rejected by Oracle's grammar.
• Issue #2596 — Oracle syntax error: Temp-table creation missing the GLOBAL keyword Oracle requires.
• Issue #2861 — Temp tables persist across sessions (Oracle < 18c)
• Issue #5224 — Oracle syntax error when creating temp table
• Issue #6089 — Regex-based Expectations fail for MSSQL & Oracle

GX Cloud (obsolete)

• Discourse #1796 — Request for Oracle support in GX Cloud.
• Discourse #2093 — Another request for Oracle support in GX Cloud.

Current State

Oracle-specific code paths exist in GX, but there is no registered SqlDatasourceTestConfig, no CI lane, and no requirements file. At the public API surface, there’s no add_oracle fluent data source method, and no extra install pip install great_expectations[oracle].

The Implementation Gap

PR #12085 adds Oracle to the tiered suite. Six of the eight case groups pass today, and the following two fail:

Gap 1: Regex expectations raise NotImplementedError due to a missing dialect branch. Function get_dialect_regex_expression in expectations/metrics/util.py is a chain of per-dialect branches ending in return None; Oracle takes the fallthrough, and the caller raises NotImplementedError, surfaced as a MetricResolutionError. The failure masquerades as a data-quality finding: success is False and the real error is buried in exception_info. Fix is additive — an Oracle branch rendering REGEXP_LIKE, plus the negated sibling.

Gap 2: Query-metric expectations fail with ORA-00933/00907 — hardcoded AS on a derived table. The query-metric providers string-format f"({selectable}) AS subselect" / AS substituted_batch_subquery; Oracle's grammar accepts AS before a column alias but rejects it before a table alias. Isolated to the grammar rule with a two-line raw oracledb probe (... AS subselect → ORA-00933; same query without AS → succeeds). This blocks UnexpectedRowsExpectation and everything routing through _get_substituted_batch_subquery_from_query_and_batch_selectable. To fix this we render the alias without AS when compiling for Oracle.

Since the natural workaround for Gap 1 is an UnexpectedRowsExpectation blocked by Gap 2, there is currently no working regex path on Oracle at all.

A spike revealed two further failures outside of the tiered test suite. The partitioner failure (Gap 3) is a blocker for this work; the sampler failure (Gap 4) can be deferred, since sampling isn’t exposed through the BatchDefinition API.

Gap 3: As reported by @AlmeidaJoao, monthly/daily date partitioning fails with ORA-00906 — unlengthed VARCHAR2 cast. The multi-date-part branch of SqlAlchemyDataPartitioner casts each extracted part to sa.String with no length; SQLAlchemy's Oracle dialect compiles that to bare VARCHAR2, which Oracle rejects inside CAST. BatchDefinitions created with add_batch_definition_monthly/_daily fail, while add_batch_definition_yearly works. The fix is an explicit length (VARCHAR2(4000)) or TO_CHAR.

Gap 4: The MD5 sampler emits three constructs Oracle doesn't have — RIGHT (ORA-00904), MD5 (ORA-00904), and CAST AS CLOB (ORA-00932), all three independently invalid, so any fix needs all of them (SUBSTR/STANDARD_HASH/TO_CLOB) or an explicit unsupported guard. This is probably a broader problem across data sources, and isn’t necessarily specific to Oracle.

Proposed change

This RFC proposes to bring Oracle under test in CI, fix the implementation gaps that keep Oracle from passing the suite, and ship Oracle as a supported extra dependency pip install great_expectations[oracle], which installs oracledb.

Under this proposal, Oracle enters the curated sql tier (i.e. it’s used via the generic add_sql API, no add_oracle public API method), and CI runs against an Oracle 21c image, which is the oldest freely containerizable XE image.

Release Requirements

GX will not ship an [oracle] extra until these requirements are met:

• The curated sql test suite must pass with no carveouts (Gaps 1–2).
• CI must run tests proving that daily and monthly BatchDefinitions work (Gap 3).
• Documentation must be updated with actual support.
• This RFC is approved.

Impact on public API or architecture

This proposal adds one public install surface: the pip install great_expectations[oracle] extra, which installs oracledb. There is deliberately no add_oracle fluent data source method — Oracle enters the curated sql tier and is used via the generic add_sql API, so no new public API methods are introduced. No existing public API changes. oracledb is the only supported driver — there is no attempt at cx_Oracle compatibility. Regression risk to other backends is low: each gap fix is dialect-gated, following the existing per-dialect branching pattern (as in get_dialect_regex_expression), so rendered SQL for existing backends is unchanged. CI verifies Oracle 21c; 19c compatibility is expected but not CI-verified.

Alternatives considered

A dedicated add_oracle fluent method. Oracle is instead used via the generic add_sql API, consistent with the other data sources in the curated sql tier; a dedicated fluent method can still be added later, without breaking changes, if demand warrants it.

CI against 23ai instead of 21c. 21c is chosen over 23ai so that the tested surface stays close to the 19c still common in enterprise deployments. 19c compatibility is expected but not CI-verified.

Fixing the MD5 sampler now (Gap 4). Gap 4 is deferred rather than fixed here: sampling isn’t exposed through the BatchDefinition API, and the failure is probably a broader problem across data sources rather than something Oracle-specific.

RFC Process Notes

This is the first published RFC after the contributor docs rewrite in July 2026, and helps to establish the intended pattern. The weight of an RFC scales with how open its design questions are; this is a low-complexity example. RFCs should generally be approved prior to implementation. This RFC posts concurrently with its implementation, due to the following circumstances:

• Design risk was minimized by harness evidence in PR [MAINTENANCE] Add Oracle to the SQL test harness with live curated coverage #12085.
• Oracle has long had some level of support within GX, so this is improving an existing data source, not adding a net-new one.

Per the documented convention in CONTRIBUTORS.md, the comment window stays open for the full two-week minimum, and the public API change (#12091) will be held until the window closes.