Oracle Database Client

Relevant source files

The following files were used as context for generating this wiki page:

• src/flext_target_oracle/_utilities/client.py
• src/flext_target_oracle/_utilities/loader.py
• src/flext_target_oracle/_utilities/errors.py
• CONTRIBUTING.md
• examples/01_basic_usage.py
• tests/conftest.py

The Oracle Database Client within the flext-target-oracle project serves as the primary interface for loading data into an Oracle database following the Singer specification. It encapsulates the complexities of Oracle-specific DDL (Data Definition Language) and DML (Data Manipulation Language) operations, providing a unified interface for Singer messages such as SCHEMA, RECORD, and STATE.

Architecturally, the client is divided into a high-level facade (FlextTargetOracle) and a core data loading engine (FlextTargetOracleLoader). The loader leverages the flext-db-oracle API to perform database operations, ensuring strict separation of concerns and adherence to "Railway-Oriented Programming" patterns via the r result type.
Sources: src/flext_target_oracle/_utilities/loader.py:20-25, CONTRIBUTING.md:144-165

Core Architecture and Components

The Oracle Database Client architecture relies on several key modules that handle configuration, database interaction, and error management.

Component Relationship

The following diagram illustrates the relationship between the target client, the loader, and the external database API.

The diagram shows the hierarchical structure where FlextTargetOracle orchestrates the Loader, which in turn interacts with the database via FlextDbOracleApi.
Sources: src/flext_target_oracle/_utilities/client.py:28-40, src/flext_target_oracle/_utilities/loader.py:34-55

Key Classes

Class	Responsibility	Source
FlextTargetOracle	The main Singer target implementation. Handles message routing and state management.	src/flext_target_oracle/_utilities/client.py
FlextTargetOracleLoader	Core data loading logic, including DDL/DML operations and schema flattening.	src/flext_target_oracle/_utilities/loader.py
FlextTargetOracleExceptions	Custom exception hierarchy for connection, authentication, and processing errors.	src/flext_target_oracle/_utilities/errors.py
FlextTargetOracleSettings	Pydantic-based configuration model for database credentials and feature flags.	CONTRIBUTING.md:160-165

Data Flow and Processing Logic

The client processes data in a structured sequence: initializing the connection, verifying target tables, and buffering records for bulk insertion.

Record Processing Sequence

This sequence diagram shows the flow of a Singer message through the system.

The diagram depicts the lifecycle of SCHEMA and RECORD messages, highlighting the buffering mechanism used for performance.
Sources: examples/01_basic_usage.py:165-200, src/flext_target_oracle/_utilities/loader.py:465-515

Loading Strategies

The loader supports multiple data loading methods, configured via load_method:

• INSERT: Standard row-by-row or batch insertion.
• BULK_INSERT: High-performance bulk loading using execute_many.
• MERGE / BULK_MERGE: Logic to update existing records based on primary keys (SDC mode).
  Sources: src/flext_target_oracle/_utilities/loader.py:655-680, tests/conftest.py:280-300

Database Schema Management

The FlextTargetOracleLoader dynamically generates Oracle-compatible schema definitions from Singer JSON schemas.

Column Generation Rules

The client applies specific logic to map Singer types to Oracle types and handle audit columns:

1. SDC Columns: Automatically appends _SDC_EXTRACTED_AT and _SDC_LOADED_AT (TIMESTAMP).
2. JSON Storage: Supports HYBRID or JSON storage modes using CLOB columns for nested data.
3. Column Ordering: By default, orders columns by: Primary Keys -> Regular Columns -> Audit Columns -> SDC Columns.
   Sources: src/flext_target_oracle/_utilities/loader.py:205-275

Schema Mapping Example

The following table summarizes how internal fields are mapped during table creation.

Internal Category	Field Example	Data Type	Requirement
Primary Key	ID	NUMBER	NOT NULL
Standard Data	NAME	VARCHAR2	NULLABLE (default)
Audit Column	UPDATED_AT	TIMESTAMP	NULLABLE
SDC Metadata	_SDC_LOADED_AT	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP
Sources: src/flext_target_oracle/_utilities/loader.py:225-245, tests/conftest.py:230-245

Error Handling Pattern

The project utilizes "Railway-Oriented Programming" where operations return a Result object (r[T]) rather than throwing raw exceptions for expected failures.

# Example of handling a loader operation
result = loader.ensure_table_exists(stream_name, schema)
if result.failure:
    # Logic for failure path
    logger.error(f"Failed: {result.error}")
    return result.fail_op("table_creation")
# Success path
return r[bool].ok(True)

Sources: src/flext_target_oracle/_utilities/loader.py:380-405, examples/01_basic_usage.py:175-185

Structured Exceptions

When critical errors occur, the FlextTargetOracleExceptions class provides structured context:

• OracleConnectionError: Includes dsn, user, and service_name.
• SchemaError: Includes stream_name, table_name, and validation_errors.
• ProcessingError: Includes record_count and operation type.
  Sources: src/flext_target_oracle/_utilities/errors.py:75-145

Summary

The Oracle Database Client provides a robust, type-safe, and high-performance mechanism for ingesting data into Oracle environments. By combining a buffered loading strategy with automated DDL management and structured error handling, it ensures reliability and developer productivity within the FLEXT ecosystem. The separation between the high-level FlextTargetOracle and the specialized FlextTargetOracleLoader allows for clean architecture and easy extensibility for new database features.
Sources: CONTRIBUTING.md:144-155, src/flext_target_oracle/_utilities/loader.py:530-550