Add Oracle Database Support

[peter7775]

Feature Request: Oracle Database Support

Summary

Add comprehensive Oracle Database support to the SQL Graph Visualizer, extending the existing multi-database architecture to include one of the world's most widely used enterprise database systems.

Motivation

Oracle Database is the leading enterprise database system globally. Adding Oracle support would:

• Target enterprise market - Oracle dominates large enterprise environments
• Enable complex legacy transformation - Many mission-critical systems run on Oracle
• Complete enterprise database coverage - Alongside SQL Server for full enterprise reach
• Handle complex schemas - Oracle's advanced features require sophisticated handling
• Open high-value opportunities - Oracle environments typically have larger budgets

Technical Requirements

Core Implementation

• Oracle Driver Integration: Implement Oracle database/sql driver with connection pooling
• Repository Implementation: Create internal/infrastructure/persistence/oracle/ package
• Port Adaptation: Implement Oracle-specific database port interface
• Factory Integration: Update database factory to support Oracle selection
• Configuration Support: Add Oracle configuration with TNS and service name support

Oracle Specific Features

• TNS Connection Support: Handle Oracle TNS names and connection descriptors
• Service Names & SIDs: Support both connection methods
• Oracle Data Types: Handle Oracle-specific types (NUMBER, VARCHAR2, CLOB, BLOB, etc.)
• PL/SQL Support: Basic support for Oracle stored procedures and functions
• Schema & User Concepts: Handle Oracle's schema/user model properly
• Wallet Authentication: Support Oracle Wallet for secure connections

Configuration Example

database:
  type: "oracle"
  oracle:
    # TNS connection method
    dsn: "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XEPDB1)))"
    
    # Or simple connection method
    host: "localhost"
    port: 1521
    service_name: "XEPDB1"  # or sid: "XE"
    
    authentication:
      username: "hr"
      password: "password"
    
    connection_params:
      max_open_conns: 10
      max_idle_conns: 5
      conn_max_lifetime: "1h"
      timezone: "UTC"

Testing & Validation

• Unit Tests: Oracle repository and connection tests
• Integration Tests: Full transformation pipeline with Oracle
• Docker Environment: Oracle Database XE container setup for development
• Sample Database: Oracle HR sample schema or similar
• Performance Tests: Handle Oracle's query optimization patterns

Implementation Approach

Phase 1: Core Infrastructure

1. Add Oracle driver dependency (github.com/godror/godror)
2. Create basic repository structure
3. Implement connection management with TNS support
4. Add configuration support for Oracle-specific options

Phase 2: Schema & Query Support

1. Implement Oracle schema discovery (ALL_TABLES, ALL_COLUMNS, etc.)
2. Handle Oracle-specific SQL syntax and functions
3. Add proper data type conversion for Oracle types
4. Support for Oracle's dual table and other system objects

Phase 3: Advanced Features

1. PL/SQL procedure support in transformation rules
2. Oracle-specific query optimizations
3. Handle Oracle's case sensitivity rules
4. Support for Oracle schemas/users model

Phase 4: Testing & Documentation

1. Comprehensive test suite with Oracle XE
2. Docker development environment
3. Documentation and configuration examples
4. Performance benchmarking against other databases

Success Criteria

• Can connect to Oracle Database instances (local, remote, cloud)
• Successfully transforms Oracle tables to Neo4j nodes
• Handles Oracle-specific data types correctly (NUMBER, VARCHAR2, CLOB, etc.)
• Supports both TNS and simple connection methods
• Maintains performance comparable to other database implementations
• Comprehensive test coverage (>80%)
• Complete documentation with Oracle-specific examples

Technical Considerations

Challenges

• Complex Connection Strings: TNS descriptors can be very complex
• Oracle Data Types: Unique types like NUMBER with precision/scale
• Licensing: Oracle XE for testing, considerations for production
• Case Sensitivity: Oracle's unusual case handling rules
• Schema Model: Oracle's user=schema concept differs from other databases
• Performance: Oracle's cost-based optimizer behavior

Dependencies

• github.com/godror/godror - Pure Go Oracle driver
• github.com/sijms/go-ora/v2 - Alternative pure Go driver (no Oracle client required)
• Oracle Instant Client (for godror) or pure Go approach

Driver Decision

Recommended: github.com/sijms/go-ora/v2

• Pure Go (no Oracle client installation required)
• Easier Docker setup
• Better for contributors (no Oracle client dependencies)
• Active development and good performance

Priority

High - Oracle is the #1 enterprise database, this would significantly expand enterprise market reach.

Labels

enhancement, database, backend, feature, priority-high, level-advanced

Related

• Follows the pattern established in Implement PostgreSQL Support #7 (PostgreSQL Support) and Add Microsoft SQL Server Database Support #15 (SQL Server Support)
• Part of the enterprise database coverage strategy
• Could enable future Oracle Cloud integration

---

Technical Complexity: High - Oracle has unique characteristics and enterprise-grade complexity
Expected Impact: Very High - Oracle dominates enterprise market
Community Benefit: High - many enterprise developers work with Oracle daily
Business Value: Very High - Oracle environments typically have substantial budgets