Oracle DB & WMS Integration
Relevant source files
The following files were used as context for generating this wiki page:
Oracle DB and Oracle WMS integration within the FLEXT ecosystem provides a standardized, containerized infrastructure for managing enterprise data workflows. This integration facilitates the development, testing, and deployment of connectors for Oracle Databases and Warehouse Management Systems (WMS), ensuring consistency across local development and CI/CD environments.
The integration scope includes specialized domain packages such as flext-db-oracle and flext-oracle-wms, supported by centralized Docker configurations. These configurations allow for the simulation of real-world Oracle environments, including schema initialization and API-driven WMS interactions, all governed by the flext_tests framework and the tk container management API.
Sources: README.md:21-25, docker/README.md:14-17, docker/docker-compose.oracle-wms.yml:1-7
Architecture and Infrastructure
The integration relies on a centralized Docker infrastructure managed within the ~/flext/docker/ directory. This architecture enforces strict separation between the standard Oracle Database services and the Oracle WMS application services.
Container Ecosystem
The FLEXT platform categorizes Oracle-related services into distinct container types to support specific development and testing needs:
- Standard Oracle Database (
flext-oracle-db-test): A containerized Oracle instance used for general database operations, schema management, and testing of Oracle-based taps and targets. - Oracle WMS (
flext-oracle-wms-main): A specialized environment supporting real Oracle WMS connectivity, API validation, and functionality demonstrations.
Sources: docker/README.md:126-135, docker/docker-compose.oracle-wms.yml:11-15
Network and Connectivity
Services communicate over a dedicated bridge network, typically named flext-network or flext-oracle-wms-network. Standard ports are assigned to prevent conflicts across the ecosystem.
| Service Type | Default Port | Protocol | Purpose |
|---|---|---|---|
| Oracle Database | 1522 | SQL*Net | Standard database connectivity for flext-db-oracle |
| Oracle WMS | 8080 | HTTP/REST | Web interface and API endpoint access |
| Oracle XE (Meltano) | 1522 | SQL*Net | Testing for Meltano data integration pipelines |
Sources: docker/README.md:131, docker/docker-compose.oracle-wms.yml:58, docker/docker-compose.meltano-test.yml:68
Infrastructure Component Flow
The following diagram illustrates the relationship between the centralized Docker management, the Oracle Database, and the WMS application services.
The tk API acts as the primary orchestrator for the lifecycle of these containers, ensuring clean states for every test run.
Sources: docker/README.md:65-75, docker/docker-compose.oracle-wms.yml:11-96
Oracle Database Initialization
For testing environments, Oracle databases are initialized with a specific FLEXT_TEST schema. This process is handled via an init.sql script executed during the container startup sequence within the FLEXTDB pluggable database (PDB).
Schema Structure
The initialization script creates a foundational set of tables to support integration testing:
- Employees: Primary data table for human resources entities.
- Departments: Organizational structure entities.
- Jobs: Job definition records.
Sources: docker/oracle-db/init.sql:5-25
Database Configuration Options
Configuration is managed through environment variables, primarily defined in .env files and referenced within docker-compose.db-oracle.yml or specialized testing files.
| Parameter | Default/Example | Description |
|---|---|---|
ORACLE_PASSWORD | Oracle123Test | Password for system/admin users |
ORACLE_DATABASE | XEPDB1 | Name of the pluggable database instance |
ORACLE_CHARACTERSET | AL32UTF8 | Encoding for the Oracle instance |
ORACLE_SERVICE_NAME | XE | Service identifier for connections |
Sources: docker/docker-compose.meltano-test.yml:69-74, docker/oracle-db/init.sql:5
Oracle WMS Integration
The Oracle WMS module is designed for continuous validation of WMS API connectivity and functionality. It supports several operational modes, including a main service for development, a testing service for automated validation, and an examples service for demonstration.
WMS Configuration Parameters
The WMS client requires specific configuration for authentication and endpoint targeting.
# Environment variables used in flext-oracle-wms
- ORACLE_WMS_BASE_URL=${ORACLE_WMS_BASE_URL}
- ORACLE_WMS_USERNAME=${ORACLE_WMS_USERNAME}
- ORACLE_WMS_PASSWORD=${ORACLE_WMS_PASSWORD}
- ORACLE_WMS_ENVIRONMENT=${ORACLE_WMS_ENVIRONMENT}
- ORACLE_WMS_API_VERSION=${ORACLE_WMS_API_VERSION:-v10}
- ORACLE_WMS_TIMEOUT=${ORACLE_WMS_TIMEOUT:-30}
- ORACLE_WMS_ENABLE_RATE_LIMITING=true
Sources: docker/docker-compose.oracle-wms.yml:16-32
Integration Lifecycle
The integration between the FLEXT platform and Oracle WMS follows a request-response cycle mediated by the FlextOracleWmsClient.
The integration emphasizes enterprise features such as rate limiting (default 60 requests per minute) and mandatory request logging.
Sources: docker/docker-compose.oracle-wms.yml:19-35
Lifecycle and Quality Gates
All Oracle integrations are subject to the FLEXT "Continuous Green" policy. Containers are managed using the tk manager, which handles automatic cleanup and "dirty" state tracking to prevent configuration drift between test suites.
Maintenance and Testing Workflow
- Boot: Containers are started using centralized compose files (e.g.,
docker-compose.oracle-wms.yml). - Healthcheck: The system validates availability by attempting to instantiate the
FlextOracleWmsClientor runningsqlpluscommands. - Validation:
pytestruns against thetests/directory within the container, generating coverage reports inreports/. - Cleanup:
tkreverts volume changes or recreates containers if marked "dirty."
Sources: docker/README.md:167-183, docker/docker-compose.oracle-wms.yml:61-75
Quality Control Commands
| Command | Purpose |
|---|---|
poetry run pytest tests/ | Executes the standard test suite with coverage |
docker_mgr.mark_container_dirty() | Forces a container recreation on the next run |
make val | Runs global quality gates (Ruff, Mypy, Pyright) |
Sources: docker/README.md:112-118, docker/docker-compose.meltano-test.yml:45
Conclusion
The Oracle DB & WMS Integration provides a robust, enterprise-grade framework for managing complex Oracle data environments. By leveraging centralized Docker configurations and the tk management API, FLEXT ensures that developers can interact with Oracle Database and WMS instances through a consistent, type-safe, and highly automated interface. This structure minimizes the complexity of managing local Oracle installations while maximizing test fidelity against real API behaviors.