Data Type Mapping
Relevant source files
The following files were used as context for generating this wiki page:
The Data Type Mapping system in flext-target-oracle is responsible for translating Singer-standard JSON schemas into Oracle-compatible database structures. This process ensures that incoming data streams are correctly typed, validated, and persisted according to Oracle's relational requirements, while also supporting modern semi-structured data patterns such as JSON storage modes.
The system acts as a bridge between the flexible, schema-less nature of JSON and the strict, strongly-typed environment of an Oracle Database. It handles the identification of primary keys, the normalization of timestamps for Oracle's TIMESTAMP format, and the dynamic generation of DDL (Data Definition Language) to ensure target tables match the source stream's structure.
Sources: src/flext_target_oracle/_utilities/loader.py:1-40, examples/01_basic_usage.py:70-95
Schema-to-Oracle Translation Flow
The mapping logic is primarily housed within the FlextTargetOracleLoader class, which utilizes the flext-db-oracle API as the source of truth for database-specific type definitions. When a SCHEMA message is received, the loader initiates a multi-step transformation process.
The diagram above illustrates the decision-making process for determining the final table structure based on the configured storage mode and incoming Singer schema properties.
Sources: src/flext_target_oracle/_utilities/loader.py:115-160, src/flext_target_oracle/_utilities/client.py:195-210
Key Components
| Component | Responsibility |
|---|---|
map_singer_schema | Core service that converts JSON schema types (string, integer, number, boolean) to Oracle types. |
_loader_columns | Orchestrates the extraction of properties and construction of DbOracle.Column objects. |
_append_schema_column | Handles the individual mapping of a single Singer field to an Oracle column, respecting ignored columns and mappings. |
_sdc_columns | Standardizes the inclusion of metadata fields _SDC_EXTRACTED_AT and _SDC_LOADED_AT. |
Sources: src/flext_target_oracle/_utilities/loader.py:105-130, src/flext_target_oracle/_utilities/loader.py:195-215
Core Type Mappings
The system supports standard Singer types and maps them to appropriate Oracle counterparts. If a type cannot be explicitly determined or is not supported as a relational column, it defaults to a flexible string format.
Supported Primitive Mappings
| Singer Type | Oracle Target Type | Logic / Notes |
|---|---|---|
integer | NUMBER | Handled via map_singer_schema service. |
number | NUMBER | Often used for floating point or decimal values. |
string | VARCHAR2 | Default type; length is typically managed by the underlying DB API. |
string (date-time) | TIMESTAMP | Normalized via _oracle_timestamp_text. |
boolean | NUMBER(1) | Conventionally stored as 1 (True) or 0 (False). |
object / array | CLOB (JSON) | Only preserved in JSON/Hybrid storage modes; skipped in flattened mode. |
Sources: src/flext_target_oracle/_utilities/loader.py:180-205, tests/integration/test_oracle.py:130-160, tests/artifacts/oracle-init/01-create-test-schema.sql:20-40
Timestamp Normalization
Oracle requires a specific string format for TIMESTAMP binds. The loader utility _oracle_timestamp_text ensures that ISO-formatted strings are converted to YYYY-MM-DD"T"HH24:MI:SS.FF6.
def _oracle_timestamp_text(value: t.JsonValue) -> str:
if isinstance(value, str) and value:
try:
parsed: datetime = datetime.fromisoformat(value)
return parsed.replace(tzinfo=None).strftime("%Y-%m-%dT%H:%M:%S.%f")
except ValueError:
return value
# ... fallback to current UTC ...
Sources: src/flext_target_oracle/_utilities/loader.py:75-90
Storage Modes and Handling
The mapping behavior changes significantly based on the storage_mode setting. This allows users to choose between strict relational structures and flexible document-style storage.
Relational (Flattened) Mode
In this mode, every top-level property in the Singer schema is mapped to a distinct Oracle column. Complex types (objects and arrays) are generally excluded unless they can be represented as strings.
Sources: src/flext_target_oracle/_utilities/loader.py:200-210, tests/integration/test_oracle.py:240-270
JSON and Hybrid Modes
When storage_mode is set to json or hybrid, a specialized CLOB column (default name JSON_DATA) is added to the table.
- JSON Mode: Only primary keys and the JSON blob column are created.
- Hybrid Mode: Mapped scalar columns are created alongside the full JSON blob.
Column Ordering Logic
The system provides deterministic column ordering to ensure database tables are human-readable and follow organizational standards. By default, it supports "alphabetical" ordering with priority groups.
| Priority Group | Column Types | Default Order |
|---|---|---|
primary_keys | Fields identified in key_properties. | 1 |
regular_columns | Standard data fields from the schema. | 2 |
audit_columns | Fields ending in _AT or timestamp types. | 3 |
sdc_columns | _SDC_EXTRACTED_AT, _SDC_LOADED_AT. | 4 |
Sources: src/flext_target_oracle/_utilities/loader.py:220-255
Error Handling in Mapping
Type mapping errors, such as invalid Singer schemas or incompatible type transformations, are handled via the FlextTargetOracleExceptions hierarchy.
SchemaError: Raised when the incoming Singer schema fails validation or contains structures that cannot be translated to Oracle.ValidationError: Used when column mappings or ignored column configurations conflict with the database reality.
Sources: src/flext_target_oracle/_utilities/errors.py:110-145, src/flext_target_oracle/_utilities/loader.py:100-115
The Data Type Mapping module ensures that the bridge between Singer streams and Oracle is robust, supporting both traditional relational loads and modern JSON-centric architectures while maintaining strict auditability through SDC columns.