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).

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 source_platform: oracle in the validation workflow YAML.

Validation behavior¶

Empty string semantics: Oracle treats an empty string ('') as NULL on character types. Snowflake does not. This can surface as L2/L3 differences on VARCHAR2, NVARCHAR2, and related text columns.
Fixed-width character types: Oracle CHAR and NCHAR values are space-padded. L3 normalization applies RTRIM when comparing to Snowflake VARCHAR targets.
LOB types: CLOB, NCLOB, and LONG can trigger ORA-00932 when L2 aggregate functions run over LOB columns, so L2 is often disabled for those columns.
Binary large objects: L3 row validation hashes only the first 4000 bytes of a BLOB.
TIMESTAMP WITH LOCAL TIME ZONE: L3 row validation is excluded when the Worker uses the oracledb thin driver (DPY-3022).
INTERVAL types: Compared as normalized text with a known sign-stripping limitation in the interval formatter.
JSON and XMLTYPE: Limited validation coverage. XMLTYPE mapped to native Snowflake tables is typically limited to L1 schema checks.
SDO_GEOMETRY: Requires Oracle Spatial. Geometry validation is not supported end to end in Cloud Data Validation today.

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

Example workflow excerpt:

source_platform: oracle
target_database: TARGET_DB
schema_mappings:
  HR: HR
validation_configuration:
  schema_validation: true
  metrics_validation: true
  row_validation: false
tables:
  - fully_qualified_name: HR.EMPLOYEES
    column_names_to_partition_by:
      - EMPLOYEE_ID
  - fully_qualified_name: HR.DEPARTMENTS
    column_names_to_partition_by:
      - DEPARTMENT_ID
    index_column_list:
      - DEPARTMENT_ID
    validation_configuration:
      row_validation: 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	TEXT	Partial	Metrics comparison not supported
LONG	VARCHAR	Partial	Metrics and row comparison not supported
RAW, BLOB	BINARY	Partial	Row comparison covers only 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	VARCHAR	Yes	Known limitation comparing interval sign
JSON, XMLTYPE	VARIANT	Partial	XML supports schema-level comparison only
SDO_GEOMETRY	GEOMETRY	No	Requires Oracle Spatial

Platform-specific suggestions¶

When validating VARCHAR2 / NVARCHAR2 columns, investigate empty-string versus NULL mismatches before treating results as data errors.
For CHAR / NCHAR, expect padding differences unless L3 RTRIM normalization applies.
For CLOB, NCLOB, LONG, and large BLOB columns, plan on L1 and selective L3 where supported rather than relying on L2 metrics.
If you use TIMESTAMP WITH LOCAL TIME ZONE and need L3, confirm your Worker ODBC/driver configuration avoids DPY-3022 on the thin driver path.
Anti-locking: AIM DMV adds an automatic PARALLEL optimizer hint on large Oracle tables. Override with queryModifiers.selectModifier, or set selectModifier to "NONE" to disable. See Anti-locking and query modifiers.