Validating Data from Oracle¶

This page covers Oracle-specific setup for Data validation. For workflow field definitions, see Data validation configuration reference.

Prerequisites¶

Oracle Instant Client and ODBC driver on Workers (same as Migrating Data from Oracle).
L3 object storage (Recommended): Use DBMS_CLOUD (extraction.strategy: dbms_cloud) so signatures land on the same object store and Snowflake external stage as migration (the URI in dbms_cloud_file_uri_prefix). Schema and metrics validation still read Oracle over SQL. See Object storage backends, External stage and storage integration for that URI, and Server-side export with dbms_cloud.

Connectivity¶

Reuse [connections.source.oracle] Worker TOML from data migration:

[connections.source.oracle]
oracle_connection_mode = "basic"
username = "scott"
password = "your_password"
database = "ORCL"
host = "db.example.com"
port = "1521"

Set sourcePlatform: oracle in the validation workflow YAML.

For partitioning, indexColumnList, and other workflow settings shared across platforms, see Data validation configuration reference.

Example workflow excerpt:

sourcePlatform: oracle
targetDatabase: TARGET_DB
schemaMappings:
  HR: HR
validationConfiguration:
  schemaValidation: true
  metricsValidation: true
  rowValidation: false
tables:
  - fullyQualifiedName: HR.EMPLOYEES
    columnNamesToPartitionBy:
      - EMPLOYEE_ID
  - fullyQualifiedName: HR.DEPARTMENTS
    columnNamesToPartitionBy:
      - DEPARTMENT_ID
    indexColumnList:
      - DEPARTMENT_ID
    validationConfiguration:
      rowValidation: true

Data type mappings¶

Oracle type	Snowflake target type	Supported for validation	Notes
NUMBER, FLOAT, integers, DECIMAL	NUMBER / FLOAT	Yes
VARCHAR2, NVARCHAR2, VARCHAR	VARCHAR	Yes	Oracle treats empty string as NULL
CHAR, NCHAR	VARCHAR	Yes	Trailing spaces ignored in comparison
CLOB, NCLOB	VARCHAR	Partial	Metrics comparison not supported
LONG	VARCHAR	Partial	Metrics and row comparison not supported
RAW, BLOB	BINARY	Yes	Row comparison covers the first 4000 bytes
DATE, TIMESTAMP types	TIMESTAMP_NTZ / TIMESTAMP_TZ	Yes
TIMESTAMP WITH LOCAL TIME ZONE	TIMESTAMP_LTZ	Partial	Row comparison not supported
INTERVAL types	INTERVAL	Yes	Native `INTERVAL` comparison by default; known limitation comparing interval sign. See INTERVAL data type handling.
XMLTYPE	VARIANT	Partial	Schema-level comparison only
JSON	VARIANT	Yes	Requires Oracle 21c or later
BOOLEAN	BOOLEAN	Yes	Requires Oracle 23ai or later
VECTOR	VECTOR(element_type, n)	Yes	Requires Oracle 23ai or later. Row-level validation compares element by element
SDO_GEOMETRY	GEOGRAPHY	Yes	Requires Oracle Spatial. Compared as WKT, truncated at 4000 characters in row-level validation

Platform-specific considerations¶

Starting validation: Ask the agent to generate a validation workflow with the depth you need (schema validation, metrics validation, and row-level validation, per table).

Prompt:
```
Run cloud data validation for my Oracle tables, with schema and metrics validation on all tables and row-level validation on EMPLOYEES
```
Performance hints: AIM DMV adds an automatic PARALLEL optimizer hint on large Oracle tables to speed up scans. Override with queryModifiers.selectModifier, or set selectModifier to "NONE" to disable. See Anti-locking and query modifiers.