---
title: "Migrating from Oracle to HeliosDB"
---

# Migrating from Oracle to HeliosDB

**Target Audience**: Database Administrators, Oracle DBAs, Architects
**Estimated Migration Time**: 4-12 weeks depending on complexity
**Difficulty**: Advanced

---

## Table of Contents

1. [Overview](#overview)
2. [Why Migrate to HeliosDB?](#why-migrate-to-heliosdb)
3. [Compatibility Assessment](#compatibility-assessment)
4. [Prerequisites](#prerequisites)
5. [Migration Strategies](#migration-strategies)
6. [Step-by-Step Migration](#step-by-step-migration)
7. [Feature Mapping](#feature-mapping)
8. [PL/SQL Migration](#step-4-plsql-migration)
9. [DBMS Package Usage](#dbms-package-usage)
10. [Performance Tuning](#performance-tuning)
11. [Troubleshooting](#troubleshooting)
12. [Best Practices](#best-practices)
13. [Rollback Plan](#rollback-plan)
14. [Case Studies](#case-studies)
15. [Next Steps](#next-steps)

---

## Overview

HeliosDB Full's **Oracle compatibility module implements a large part of Oracle 23ai's feature surface** (for what typical in-house applications can run unchanged through the Oracle listener today, see [Oracle compatibility for in-house applications](/docs/full/reference/protocols/oracle/inhouse_application_compatibility/)): core SQL features, PL/SQL procedures, and 19 DBMS packages (DBMS_SQL, DBMS_METADATA, DBMS_LOB, DBMS_OUTPUT, etc.). It is not a drop-in replacement, but HeliosDB enables migration of many Oracle workloads with moderate code changes and adds multi-protocol capabilities.

:::caution[Measured through the Oracle listener]
The category levels in this guide describe HeliosDB Full's Oracle compatibility module.
Through the Oracle listener today, PL/SQL blocks and procedures are not executed,
`MERGE` does not change rows, and several Oracle functions return wrong results; see
[Oracle compatibility for in-house applications](/docs/full/reference/protocols/oracle/inhouse_application_compatibility/)
for the results by usage area and the [Oracle Quick Start](/docs/full/quick-starts/protocols/oracle_quick_start/)
for what to rewrite.
:::

### What You Get

- **Core Oracle SQL**: DDL, DML, transactions, indexes, constraints (production in the [compatibility matrix](/docs/full/reference/protocols/oracle/compatibility/))
- **PL/SQL (roadmap)**: PL/SQL execution through the Oracle listener — procedures, functions, anonymous blocks and common DBMS_ packages — is on the roadmap. Today, procedures, functions, packages and triggers port to PL/pgSQL
- **19 DBMS Packages** (partial): DBMS_OUTPUT, DBMS_LOB, DBMS_SQL, DBMS_METADATA, etc.
- **Advanced Features**: Hierarchical queries (CONNECT BY), PIVOT/UNPIVOT, analytic functions
- **Multi-Protocol**: Combine Oracle workloads with PostgreSQL, MongoDB, Cassandra in one database
- **Licensing**: HeliosDB Full is commercially licensed (source-available under SSPL-1.0); contact [sales@heliosdb.com](mailto:sales@heliosdb.com) for pricing

## Why Migrate to HeliosDB?

### 1. Multi-Protocol Unification

**Oracle + Other Databases** (traditional):
```
Oracle (ERP, financial) → Data warehouse → PostgreSQL (web app) → MongoDB (logs)
```

**HeliosDB** (unified):
```
HeliosDB: Oracle SQL + PostgreSQL + MongoDB + Cassandra in one database
```

**Example**:
```python
import cx_Oracle  # Oracle client
import psycopg2   # PostgreSQL client

# Insert via Oracle TNS protocol
oracle_conn = cx_Oracle.connect('hr/password@heliosdb:1521/mydb')
oracle_cursor = oracle_conn.cursor()
oracle_cursor.execute("INSERT INTO employees (id, name) VALUES (:1, :2)", [101, 'Alice'])

# Query same data via PostgreSQL protocol
pg_conn = psycopg2.connect(host='heliosdb', port=5432, database='mydb', user='hr', password='password')
pg_cursor = pg_conn.cursor()
pg_cursor.execute("SELECT * FROM employees WHERE id = %s", [101])
print(pg_cursor.fetchone())  # (101, 'Alice')
```

---

### 2. Cloud-Native Architecture

**Oracle Challenges**:
- Complex RAC/Data Guard setup
- Manual scaling
- High cloud licensing costs

**HeliosDB Advantages**:
- Auto-scaling (0 to max compute units)
- Multi-cloud (AWS, Azure, GCP, on-prem)
- Scale-to-zero for dev/staging environments
- Intelligent tiering (NVMe → SSD → S3)

---

### 3. Better Analytics Performance

**Benchmark** (TPC-H Q1, 10M rows; measured on representative fixtures, reproduce on your own hardware):
- **Oracle**: 12.5 seconds
- **HeliosDB**: 3.2 seconds (3.9x faster)

**Why**: Hybrid row/columnar storage + vectorized execution + SIMD

---

## Compatibility Assessment

### Compatibility by Category

Levels from the [Oracle compatibility matrix](/docs/full/reference/protocols/oracle/compatibility/),
which also lists support per statement, function and package:

| Category | Status |
|----------|--------|
| Core SQL | Supported |
| DDL statements | Supported |
| DML statements | Supported |
| PL/SQL core | Beta |
| PL/SQL packages | Beta |
| DBMS packages | Partial |
| Hierarchical queries (CONNECT BY) | Supported |
| Analytic functions | Supported |
| JSON functions | Supported |
| XML functions | Alpha |
| Object types | Alpha |
| Collections | Beta |
| Triggers | Beta |
| Large objects | Beta |
| Oracle RAC, Flashback, Advanced Queuing | Not supported |

**Overall**: core SQL is supported in the module, PL/SQL and DBMS packages are beta or partial. Through the Oracle listener today, core data access runs unchanged and Oracle-specific syntax, most built-in functions and PL/SQL need rewriting; see [Oracle compatibility for in-house applications](/docs/full/reference/protocols/oracle/inhouse_application_compatibility/).

---

## Prerequisites

### 1. Oracle Version Requirements

**Supported Oracle Versions**:
- Oracle 11g R2 (11.2.0.4)
- Oracle 12c R1/R2 (12.1.0.2+, 12.2.0.1+)
- Oracle 18c (18.1.0+)
- Oracle 19c (19.3.0+)
- Oracle 21c (21.3.0+)
- Oracle 23ai (23.4.0+)

**Version Check**:
```sql
SELECT * FROM v$version;
```

---

### 2. Database Size Assessment

**Estimate Database Size**:
```sql
-- Total database size
SELECT SUM(bytes)/1024/1024/1024 AS size_gb
FROM dba_data_files;

-- Size by tablespace
SELECT tablespace_name,
       SUM(bytes)/1024/1024/1024 AS size_gb
FROM dba_data_files
GROUP BY tablespace_name
ORDER BY size_gb DESC;

-- Top 20 largest tables
SELECT owner, segment_name, segment_type,
       ROUND(bytes/1024/1024/1024, 2) AS size_gb
FROM dba_segments
WHERE segment_type IN ('TABLE', 'TABLE PARTITION')
ORDER BY bytes DESC
FETCH FIRST 20 ROWS ONLY;
```

---

### 3. Feature Usage Analysis

**Identify Used Features**:
```sql
-- Check PL/SQL objects
SELECT object_type, COUNT(*) AS count
FROM dba_objects
WHERE object_type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'TRIGGER')
  AND owner NOT IN ('SYS', 'SYSTEM', 'XDB', 'APEX_050000')
GROUP BY object_type;

-- Check DBMS package usage (scan code)
SELECT DISTINCT
  REGEXP_SUBSTR(text, 'DBMS_[A-Z_]+', 1, 1) AS dbms_package
FROM dba_source
WHERE REGEXP_LIKE(text, 'DBMS_[A-Z_]+')
  AND owner NOT IN ('SYS', 'SYSTEM')
ORDER BY dbms_package;

-- Check advanced features
SELECT DISTINCT feature_name
FROM dba_feature_usage_statistics
WHERE version = (SELECT version FROM v$instance)
  AND detected_usages > 0
ORDER BY feature_name;
```

**Save Results**:
```sql
-- Export to CSV
SPOOL /tmp/oracle_feature_usage.csv
-- Run queries above
SPOOL OFF
```

---

### 4. Compatibility Assessment Tool

**HeliosDB Compatibility Analyzer** (automated; provided during migration onboarding, contact [support@heliosdb.com](mailto:support@heliosdb.com)):
```bash

# Run against Oracle database
./oracle-compat-analyzer.sh \
  --host oracle.example.com \
  --port 1521 \
  --sid ORCL \
  --user system \
  --password <password> \
  --output compatibility-report.html

# Review report
firefox compatibility-report.html
```

**Example Report Output**:
```
=== HeliosDB Oracle Compatibility Report ===

Database: ORCL (Oracle 19c)
Analyzed: 2025-11-11 10:30:00

Overall Compatibility: Moderate

Schema Objects:
  ✓ Tables: 150 (supported)
  ✓ Indexes: 200 (supported, review bitmap/function-based)
  ✓ Views: 50 (supported)
  ~ Materialized Views: 10 (partial, basic refresh only)
  ✓ Sequences: 25 (supported)

PL/SQL Objects:
  ~ Procedures: 80 (partial, review required)
  ~ Functions: 60 (partial, review required)
  ~ Packages: 30 (partial, review required)
  ~ Triggers: 20 (partial, DML triggers only)

DBMS Package Usage:
  ~ DBMS_SQL: 45 calls (partial, review required)
  ✓ DBMS_OUTPUT: 120 calls (supported)
  ~ DBMS_LOB: 30 calls (partial, review required)
  ~ UTL_FILE: 15 calls (partial, review required)
  ✗ DBMS_FLASHBACK: 5 calls (not supported, needs redesign)

Advanced Features:
  ✓ Analytic Functions: Used (supported)
  ✓ CONNECT BY: Used (supported)
  ~ PIVOT/UNPIVOT: Used (partial, static pivot only)
  ✗ Flashback Query: Used (not supported, needs redesign)

Recommendations:
  1. Review DBMS_LOB usage (30 calls) - basic operations supported
  2. Redesign Flashback Query logic (5 instances) - not supported
  3. Test UTL_FILE usage (15 calls) - some limitations
  4. Overall migration: FEASIBLE with moderate code changes
```

---

## Migration Strategies

### Strategy 1: Phased Schema Migration (Recommended for Large Databases)

**Best For**: Large Oracle databases (>1 TB), complex schemas

**Timeline**: 8-16 weeks

**Phases**:
1. **Phase 1 (Week 1-2)**: Reference data (lookup tables, configurations)
2. **Phase 2 (Week 3-5)**: Transactional tables (orders, users, products)
3. **Phase 3 (Week 6-8)**: Historical data (archives, logs)
4. **Phase 4 (Week 9-12)**: PL/SQL code (procedures, functions, packages)
5. **Phase 5 (Week 13-16)**: Testing, validation, cutover

**Pros**:
- Gradual risk reduction
- Easier troubleshooting
- Can rollback individual phases

**Cons**:
- Longer timeline
- Complex dependency management

---

### Strategy 2: Data Pump Export/Import (Planned Downtime)

**Best For**: Medium databases (100 GB - 1 TB), scheduled maintenance

**Timeline**: 1-4 weeks

**Steps**:
1. Schedule maintenance window (4-24 hours)
2. Export Oracle database using Data Pump (expdp)
3. Transform export to HeliosDB format
4. Import to HeliosDB using custom loader
5. Validate and cutover

**Pros**:
- Simplest approach
- Oracle-native tooling
- Fast data transfer

**Cons**:
- Requires downtime
- Manual schema transformation

**Implementation**:
```bash
# Step 1: Export from Oracle using Data Pump
expdp system/password@ORCL \
  DIRECTORY=data_pump_dir \
  DUMPFILE=full_export_%U.dmp \
  LOGFILE=full_export.log \
  FULL=Y \
  PARALLEL=4 \
  COMPRESSION=ALL

# Step 2: Convert to HeliosDB format (custom tool)
heliosdb-oracle-converter \
  --input /data_pump/full_export_01.dmp \
  --output /heliosdb_import/ \
  --format sql

# Step 3: Import to HeliosDB
heliosdb import oracle \
  --input /heliosdb_import/ \
  --database mydb \
  --parallel 8
```

---

### Strategy 3: Golden Gate Replication (Minimal Downtime)

**Best For**: Mission-critical systems, <15 minute downtime requirement

**Timeline**: 4-8 weeks

**Steps**:
1. Set up Oracle Golden Gate
2. Configure extract/replicat to HeliosDB
3. Initial load (full sync)
4. Continuous replication (catch-up)
5. Cutover window (5-15 minutes)

**Pros**:
- Minimal downtime
- Continuous replication
- Oracle-validated tooling

**Cons**:
- Requires Golden Gate license
- Complex setup
- Higher cost

---

### Strategy 4: Application-Driven Dual-Write (Zero Downtime)

**Best For**: Microservices, gradual migration, service-by-service

**Timeline**: 12-24 weeks

**Steps**:
1. Identify service boundaries
2. Implement dual-write in application code
3. Migrate one service at a time
4. Validate and switch reads
5. Decommission Oracle gradually

**Pros**:
- Zero downtime
- Gradual migration
- Easy rollback per service

**Cons**:
- Requires application changes
- Longest timeline
- Complex orchestration

---

## Step-by-Step Migration

### Step 1: Connection Setup

**Oracle TNS Connection**:
```bash
# Oracle SQL*Plus
sqlplus hr/password@oracle.example.com:1521/ORCL
```

**HeliosDB TNS Connection** (compatible):
```bash
# Same SQL*Plus client!
sqlplus hr/password@heliosdb.example.com:1521/mydb
```

**TNS Names Configuration**:
```
# tnsnames.ora

ORACLE_DB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle.example.com)(PORT = 1521))
    (CONNECT_DATA = (SERVICE_NAME = ORCL))
  )

HELIOSDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = heliosdb.example.com)(PORT = 1521))
    (CONNECT_DATA = (SERVICE_NAME = mydb))
  )
```

**Application Code** (cx_Oracle):
```python
import cx_Oracle

# Oracle
oracle_conn = cx_Oracle.connect('hr/password@oracle.example.com:1521/ORCL')

# HeliosDB (just change TNS!)
heliosdb_conn = cx_Oracle.connect('hr/password@heliosdb.example.com:1521/mydb')
```

---

### Step 2: Schema Migration

**Export Oracle Schema**:
```sql
-- Export DDL using DBMS_METADATA
SELECT DBMS_METADATA.GET_DDL('TABLE', 'EMPLOYEES', 'HR') FROM DUAL;
SELECT DBMS_METADATA.GET_DDL('INDEX', 'EMP_EMAIL_UK', 'HR') FROM DUAL;
SELECT DBMS_METADATA.GET_DDL('SEQUENCE', 'EMP_SEQ', 'HR') FROM DUAL;
```

**Example Oracle DDL**:
```sql
CREATE TABLE employees (
  employee_id    NUMBER(6) PRIMARY KEY,
  first_name     VARCHAR2(20),
  last_name      VARCHAR2(25) NOT NULL,
  email          VARCHAR2(25) UNIQUE,
  phone_number   VARCHAR2(20),
  hire_date      DATE NOT NULL,
  job_id         VARCHAR2(10) NOT NULL,
  salary         NUMBER(8,2),
  commission_pct NUMBER(2,2),
  manager_id     NUMBER(6),
  department_id  NUMBER(4),
  CONSTRAINT emp_salary_min CHECK (salary > 0)
);

CREATE INDEX emp_name_idx ON employees (last_name, first_name);

CREATE SEQUENCE emp_seq
  START WITH 1000
  INCREMENT BY 1
  CACHE 20;
```

**HeliosDB DDL** (compatible, minor changes):
```sql
-- Same syntax works in HeliosDB!
CREATE TABLE employees (
  employee_id    NUMBER(6) PRIMARY KEY,
  first_name     VARCHAR2(20),
  last_name      VARCHAR2(25) NOT NULL,
  email          VARCHAR2(25) UNIQUE,
  phone_number   VARCHAR2(20),
  hire_date      DATE NOT NULL,
  job_id         VARCHAR2(10) NOT NULL,
  salary         NUMBER(8,2),
  commission_pct NUMBER(2,2),
  manager_id     NUMBER(6),
  department_id  NUMBER(4),
  CONSTRAINT emp_salary_min CHECK (salary > 0)
);

CREATE INDEX emp_name_idx ON employees (last_name, first_name);

CREATE SEQUENCE emp_seq
  START WITH 1000
  INCREMENT BY 1
  CACHE 20;
```

**Automated Schema Conversion**:
```bash
# HeliosDB schema converter
heliosdb-oracle-schema-converter \
  --input oracle_schema.sql \
  --output heliosdb_schema.sql \
  --dialect oracle23ai

# Review changes
diff oracle_schema.sql heliosdb_schema.sql

# Apply to HeliosDB
sqlplus hr/password@heliosdb.example.com:1521/mydb @heliosdb_schema.sql
```

---

### Step 3: Data Migration

**Option A: Data Pump + Custom Loader**

```bash
# 1. Export from Oracle
expdp hr/password@ORCL \
  TABLES=employees,departments,jobs \
  DIRECTORY=data_pump_dir \
  DUMPFILE=hr_tables.dmp \
  LOGFILE=hr_export.log

# 2. Convert dump to SQL inserts
heliosdb-datapump-converter \
  --input /data_pump/hr_tables.dmp \
  --output /heliosdb_import/hr_data.sql \
  --batch-size 1000

# 3. Load into HeliosDB
sqlplus hr/password@heliosdb/mydb @/heliosdb_import/hr_data.sql
```

---

**Option B: SQL*Loader Format**

```bash
# 1. Export to CSV from Oracle
sqlplus -s hr/password@ORCL <<EOF
SET COLSEP ','
SET PAGESIZE 0
SET TRIMSPOOL ON
SET HEADSEP OFF
SET LINESIZE 10000
SPOOL /tmp/employees.csv
SELECT * FROM employees;
SPOOL OFF
EXIT;
EOF

# 2. Load into HeliosDB using SQL*Loader compatible format
sqlldr hr/password@heliosdb/mydb \
  CONTROL=/tmp/employees.ctl \
  DATA=/tmp/employees.csv \
  LOG=/tmp/load.log

# employees.ctl
LOAD DATA
INFILE '/tmp/employees.csv'
INTO TABLE employees
FIELDS TERMINATED BY ','
(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary)
```

---

**Option C: Python Script (Flexible)**

```python
#!/usr/bin/env python3
# migrate_oracle_data.py

import cx_Oracle

# Connect to both databases
oracle_conn = cx_Oracle.connect('hr/password@oracle.example.com:1521/ORCL')
heliosdb_conn = cx_Oracle.connect('hr/password@heliosdb.example.com:1521/mydb')

oracle_cursor = oracle_conn.cursor()
heliosdb_cursor = heliosdb_conn.cursor()

# Get list of tables
oracle_cursor.execute("""
  SELECT table_name FROM user_tables
  WHERE table_name NOT LIKE 'BIN$%'
  ORDER BY table_name
""")

tables = [row[0] for row in oracle_cursor.fetchall()]

for table in tables:
    print(f"Migrating {table}...")

    # Get row count
    oracle_cursor.execute(f"SELECT COUNT(*) FROM {table}")
    row_count = oracle_cursor.fetchone()[0]
    print(f"  Rows: {row_count}")

    # Get column names
    oracle_cursor.execute(f"SELECT * FROM {table} WHERE 1=0")
    columns = [col[0] for col in oracle_cursor.description]
    column_list = ', '.join(columns)
    placeholders = ', '.join([f':{i+1}' for i in range(len(columns))])

    # Stream rows in batches
    batch_size = 1000
    oracle_cursor.execute(f"SELECT {column_list} FROM {table}")

    while True:
        rows = oracle_cursor.fetchmany(batch_size)
        if not rows:
            break

        # Insert into HeliosDB
        heliosdb_cursor.executemany(
            f"INSERT INTO {table} ({column_list}) VALUES ({placeholders})",
            rows
        )
        heliosdb_conn.commit()
        print('.', end='', flush=True)

    print(f"\n  ✓ {table} migrated")

# Verify counts
print("\nVerifying row counts...")
for table in tables:
    oracle_cursor.execute(f"SELECT COUNT(*) FROM {table}")
    oracle_count = oracle_cursor.fetchone()[0]

    heliosdb_cursor.execute(f"SELECT COUNT(*) FROM {table}")
    heliosdb_count = heliosdb_cursor.fetchone()[0]

    if oracle_count == heliosdb_count:
        print(f"  ✓ {table}: {oracle_count} rows")
    else:
        print(f"  ✗ {table}: Oracle={oracle_count}, HeliosDB={heliosdb_count}")

print("\nMigration complete!")
```

---

### Step 4: PL/SQL Migration

:::note[PL/SQL roadmap]
PL/SQL execution through the Oracle listener — procedures, functions, anonymous blocks and common DBMS_ packages — is on the roadmap. Today, port PL/SQL to [PL/pgSQL functions](/docs/full/migration/oracle-to-heliosdb/#step-2-convert-plsql) and run them through the PostgreSQL interface, or move the logic into the application tier.
:::

**PL/SQL Features in the Compatibility Module** (beta; see the compatibility matrix for per-feature coverage):

```sql
-- Procedures
CREATE OR REPLACE PROCEDURE raise_salary (
  p_emp_id IN NUMBER,
  p_pct IN NUMBER
) IS
BEGIN
  UPDATE employees
  SET salary = salary * (1 + p_pct / 100)
  WHERE employee_id = p_emp_id;

  COMMIT;
END raise_salary;
/

-- Functions
CREATE OR REPLACE FUNCTION get_salary (
  p_emp_id IN NUMBER
) RETURN NUMBER IS
  v_salary NUMBER;
BEGIN
  SELECT salary INTO v_salary
  FROM employees
  WHERE employee_id = p_emp_id;

  RETURN v_salary;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN NULL;
END get_salary;
/

-- Packages
CREATE OR REPLACE PACKAGE emp_pkg IS
  PROCEDURE hire_employee (
    p_first_name VARCHAR2,
    p_last_name VARCHAR2,
    p_email VARCHAR2,
    p_job_id VARCHAR2
  );

  FUNCTION get_employee_count RETURN NUMBER;
END emp_pkg;
/

CREATE OR REPLACE PACKAGE BODY emp_pkg IS
  PROCEDURE hire_employee (
    p_first_name VARCHAR2,
    p_last_name VARCHAR2,
    p_email VARCHAR2,
    p_job_id VARCHAR2
  ) IS
  BEGIN
    INSERT INTO employees (employee_id, first_name, last_name, email, job_id, hire_date)
    VALUES (emp_seq.NEXTVAL, p_first_name, p_last_name, p_email, p_job_id, SYSDATE);

    COMMIT;
  END hire_employee;

  FUNCTION get_employee_count RETURN NUMBER IS
    v_count NUMBER;
  BEGIN
    SELECT COUNT(*) INTO v_count FROM employees;
    RETURN v_count;
  END get_employee_count;
END emp_pkg;
/

-- Triggers
CREATE OR REPLACE TRIGGER emp_audit_trigger
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
  INSERT INTO emp_audit_log (emp_id, old_salary, new_salary, change_date)
  VALUES (:OLD.employee_id, :OLD.salary, :NEW.salary, SYSDATE);
END;
/
```

**These object types are the planned scope for PL/SQL execution through the Oracle listener.** Until then, port each object to PL/pgSQL and test it through the PostgreSQL interface; less common PL/SQL features need the most changes.

---

**Unsupported PL/SQL Features** (requires redesign):

```sql
-- ✗ Autonomous transactions (not supported)
PRAGMA AUTONOMOUS_TRANSACTION;

-- ✗ Pipelined functions (not supported)
CREATE FUNCTION my_func RETURN my_type PIPELINED IS ...

-- ✗ Object types (limited support)
CREATE TYPE address_type AS OBJECT (...);

-- Workaround: Use JSONB or separate tables
```

---

### Step 5: Application Migration

**Oracle Application Code** (Python):
```python
import cx_Oracle

# Oracle connection
conn = cx_Oracle.connect('hr/password@oracle.example.com:1521/ORCL')
cursor = conn.cursor()

# Query
cursor.execute("SELECT employee_id, first_name, last_name, salary FROM employees WHERE department_id = :1", [10])
for row in cursor:
    print(f"{row[0]}: {row[1]} {row[2]} - ${row[3]}")

# Insert
cursor.execute("""
  INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id)
  VALUES (:1, :2, :3, :4, :5, :6)
""", [101, 'John', 'Doe', 'jdoe@example.com', datetime.now(), 'IT_PROG'])
conn.commit()

# Call procedure
cursor.callproc('raise_salary', [101, 10])  # 10% raise

# Call function
result = cursor.callfunc('get_salary', cx_Oracle.NUMBER, [101])
print(f"New salary: ${result}")
```

**HeliosDB Application Code** (connection string changed; test before cutover):
```python
import cx_Oracle

# HeliosDB connection (just change TNS!)
conn = cx_Oracle.connect('hr/password@heliosdb.example.com:1521/mydb')
cursor = conn.cursor()

# Common queries and procedure calls usually run unchanged; test before cutover
cursor.execute("SELECT employee_id, first_name, last_name, salary FROM employees WHERE department_id = :1", [10])
for row in cursor:
    print(f"{row[0]}: {row[1]} {row[2]} - ${row[3]}")

# ... (same code)
```

**Configuration-Based Connection** (best practice):
```python
# config.py
import os

ORACLE_DSN = os.environ.get('ORACLE_DSN', 'oracle.example.com:1521/ORCL')
ORACLE_USER = os.environ.get('ORACLE_USER', 'hr')
ORACLE_PASSWORD = os.environ.get('ORACLE_PASSWORD')

# app.py
import cx_Oracle
import config

conn = cx_Oracle.connect(f'{config.ORACLE_USER}/{config.ORACLE_PASSWORD}@{config.ORACLE_DSN}')
```

**Switch to HeliosDB**:
```bash
# Oracle
export ORACLE_DSN=oracle.example.com:1521/ORCL

# HeliosDB (only change this!)
export ORACLE_DSN=heliosdb.example.com:1521/mydb
```

---

## Feature Mapping

### Data Type Mapping

| Oracle Type | HeliosDB Type | Notes |
|-------------|---------------|-------|
| VARCHAR2(n) | VARCHAR(n) | Identical |
| NVARCHAR2(n) | VARCHAR(n) | UTF-8 by default |
| CHAR(n) | CHAR(n) | Fixed-length |
| NCHAR(n) | CHAR(n) | UTF-8 by default |
| NUMBER | NUMERIC | Arbitrary precision |
| NUMBER(p,s) | NUMERIC(p,s) | Fixed precision |
| INTEGER | INTEGER | Alias for NUMBER(38) |
| FLOAT | DOUBLE PRECISION | 64-bit float |
| BINARY_FLOAT | REAL | 32-bit float |
| BINARY_DOUBLE | DOUBLE PRECISION | 64-bit float |
| DATE | DATE | Date only (no time) |
| TIMESTAMP | TIMESTAMP | Date + time |
| TIMESTAMP WITH TIME ZONE | TIMESTAMP WITH TIME ZONE | Timezone-aware |
| INTERVAL YEAR TO MONTH | INTERVAL | Year-month interval |
| INTERVAL DAY TO SECOND | INTERVAL | Day-second interval |
| CLOB | TEXT | Large text |
| NCLOB | TEXT | Large text (UTF-8) |
| BLOB | BYTEA | Binary large object |
| RAW(n) | BYTEA | Binary data |
| LONG | TEXT | Deprecated in Oracle |
| LONG RAW | BYTEA | Deprecated in Oracle |
| ROWID | CTID | Row identifier |
| UROWID | TEXT | Universal ROWID |
| BFILE | TEXT | File path (not binary) |
| XMLTYPE | JSONB or XML | XML storage |

---

### SQL Function Mapping

| Oracle Function | HeliosDB Equivalent | Notes |
|-----------------|---------------------|-------|
| SYSDATE | CURRENT_DATE or NOW() | Current date/time |
| SYSTIMESTAMP | CURRENT_TIMESTAMP | Current timestamp |
| TO_DATE | TO_DATE | Date conversion |
| TO_CHAR | TO_CHAR | Format conversion |
| TO_NUMBER | TO_NUMBER or CAST | Number conversion |
| NVL | COALESCE or NVL | Null handling |
| NVL2 | CASE WHEN...THEN...ELSE | Conditional |
| DECODE | CASE WHEN...THEN...ELSE | Conditional |
| ROWNUM | ROW_NUMBER() | Row numbering |
| ROWID | CTID | Row identifier |
| INSTR | POSITION | String position |
| SUBSTR | SUBSTR or SUBSTRING | Substring |
| LENGTH | LENGTH | String length |
| CONCAT | CONCAT or \|\| | String concatenation |
| UPPER/LOWER | UPPER/LOWER | Case conversion |
| TRIM/LTRIM/RTRIM | TRIM/LTRIM/RTRIM | Whitespace removal |
| LPAD/RPAD | LPAD/RPAD | Padding |
| REPLACE | REPLACE | String replacement |
| ADD_MONTHS | date + INTERVAL | Date arithmetic |
| MONTHS_BETWEEN | age() | Date difference |
| LAST_DAY | date_trunc('month', date) + INTERVAL '1 month' - INTERVAL '1 day' | Last day of month |
| ROUND (date) | date_trunc() | Date rounding |
| TRUNC (date) | date_trunc() | Date truncation |
| EXTRACT | EXTRACT | Date part extraction |

---

## DBMS Package Usage

### DBMS_OUTPUT (partial)

**Oracle**:
```sql
BEGIN
  DBMS_OUTPUT.ENABLE(1000000);
  DBMS_OUTPUT.PUT_LINE('Hello, World!');
  DBMS_OUTPUT.PUT_LINE('Employee count: ' || emp_pkg.get_employee_count);
END;
/
```

**HeliosDB** (same syntax):
```sql
BEGIN
  DBMS_OUTPUT.ENABLE(1000000);
  DBMS_OUTPUT.PUT_LINE('Hello, World!');
  DBMS_OUTPUT.PUT_LINE('Employee count: ' || emp_pkg.get_employee_count);
END;
/
```

---

### DBMS_SQL (partial)

**Dynamic SQL Example**:
```sql
DECLARE
  v_cursor NUMBER;
  v_sql VARCHAR2(1000);
  v_result NUMBER;
BEGIN
  -- Open cursor
  v_cursor := DBMS_SQL.OPEN_CURSOR;

  -- Parse SQL
  v_sql := 'SELECT COUNT(*) FROM employees WHERE department_id = :1';
  DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE);

  -- Bind variable
  DBMS_SQL.BIND_VARIABLE(v_cursor, ':1', 10);

  -- Define column
  DBMS_SQL.DEFINE_COLUMN(v_cursor, 1, v_result);

  -- Execute
  v_result := DBMS_SQL.EXECUTE(v_cursor);

  -- Fetch result
  IF DBMS_SQL.FETCH_ROWS(v_cursor) > 0 THEN
    DBMS_SQL.COLUMN_VALUE(v_cursor, 1, v_result);
    DBMS_OUTPUT.PUT_LINE('Count: ' || v_result);
  END IF;

  -- Close cursor
  DBMS_SQL.CLOSE_CURSOR(v_cursor);
END;
/
```

**DBMS_SQL is supported in HeliosDB.** Test dynamic SQL paths after migration.

---

### DBMS_LOB (partial)

**Supported Operations**:
```sql
DECLARE
  v_clob CLOB;
  v_amount NUMBER;
  v_buffer VARCHAR2(32767);
BEGIN
  -- Read CLOB
  SELECT resume INTO v_clob FROM employees WHERE employee_id = 101;

  -- Get length
  v_amount := DBMS_LOB.GETLENGTH(v_clob);
  DBMS_OUTPUT.PUT_LINE('CLOB length: ' || v_amount);

  -- Read portion
  v_amount := 100;
  DBMS_LOB.READ(v_clob, v_amount, 1, v_buffer);
  DBMS_OUTPUT.PUT_LINE('First 100 chars: ' || v_buffer);

  -- Append
  DBMS_LOB.APPEND(v_clob, TO_CLOB('Additional text'));

  -- Update back
  UPDATE employees SET resume = v_clob WHERE employee_id = 101;
END;
/
```

**Unsupported**:
- DBMS_LOB.OPEN/CLOSE (use implicit open)
- DBMS_LOB.COMPARE (use SQL comparison)
- DBMS_LOB.ISTEMPORARY (not applicable)

---

### DBMS_RANDOM (partial)

```sql
BEGIN
  -- Random number between 1 and 100
  DBMS_OUTPUT.PUT_LINE('Random: ' || DBMS_RANDOM.VALUE(1, 100));

  -- Random string (10 characters)
  DBMS_OUTPUT.PUT_LINE('Random string: ' || DBMS_RANDOM.STRING('A', 10));
END;
/
```

---

### DBMS_METADATA (partial)

**Extract DDL**:
```sql
-- Get table DDL
SELECT DBMS_METADATA.GET_DDL('TABLE', 'EMPLOYEES', 'HR') FROM DUAL;

-- Get index DDL
SELECT DBMS_METADATA.GET_DDL('INDEX', 'EMP_EMAIL_UK', 'HR') FROM DUAL;

-- Get all DDL for a schema
BEGIN
  FOR rec IN (SELECT object_name, object_type
              FROM dba_objects
              WHERE owner = 'HR'
                AND object_type IN ('TABLE', 'VIEW', 'PROCEDURE', 'FUNCTION'))
  LOOP
    DBMS_OUTPUT.PUT_LINE(DBMS_METADATA.GET_DDL(rec.object_type, rec.object_name, 'HR'));
  END LOOP;
END;
/
```

---

### DBMS_SCHEDULER (partial)

**Create Scheduled Job**:
```sql
BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'NIGHTLY_CLEANUP',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN cleanup_old_data; END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=2; BYMINUTE=0',
    enabled         => TRUE,
    comments        => 'Nightly cleanup job'
  );
END;
/

-- Check job status
SELECT job_name, enabled, state, last_start_date, next_run_date
FROM user_scheduler_jobs
WHERE job_name = 'NIGHTLY_CLEANUP';
```

---

For full DBMS package details, see:

---

## Performance Tuning

### 1. Optimizer Hints

**Oracle hints work in HeliosDB**:
```sql
-- Use index
SELECT /*+ INDEX(employees emp_name_idx) */
  employee_id, first_name, last_name
FROM employees
WHERE last_name = 'Smith';

-- Full table scan
SELECT /*+ FULL(employees) */
  *
FROM employees;

-- Join order
SELECT /*+ LEADING(d e) */
  d.department_name, e.first_name
FROM departments d
JOIN employees e ON d.department_id = e.department_id;
```

---

### 2. Execution Plan Analysis

**EXPLAIN PLAN**:
```sql
-- Oracle / HeliosDB (same syntax)
EXPLAIN PLAN FOR
SELECT e.first_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > 50000;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
```

---

### 3. Query Optimization

**Before (Oracle - slow)**:
```sql
-- Implicit conversion (slow)
SELECT * FROM employees WHERE employee_id = '101';  -- '101' is string, employee_id is NUMBER
```

**After (HeliosDB - fast)**:
```sql
-- Explicit type (fast)
SELECT * FROM employees WHERE employee_id = 101;  -- Correct type
```

---

### 4. Index Usage

**Create appropriate indexes**:
```sql
-- B-Tree index (most common)
CREATE INDEX emp_dept_idx ON employees (department_id);

-- Composite index
CREATE INDEX emp_dept_job_idx ON employees (department_id, job_id);

-- Unique index
CREATE UNIQUE INDEX emp_email_uk ON employees (email);

-- Function-based index
CREATE INDEX emp_upper_name_idx ON employees (UPPER(last_name));
```

---

## Troubleshooting

### Common Issues

#### 1. ORA-01017: invalid username/password

**Problem**: Authentication failure

**Solution**:
```sql
-- Check user exists
SELECT username FROM dba_users WHERE username = 'HR';

-- Reset password
ALTER USER hr IDENTIFIED BY new_password;
```

---

#### 2. ORA-00942: table or view does not exist

**Problem**: Schema not found or user lacks privileges

**Solution**:
```sql
-- Check table exists
SELECT owner, table_name FROM dba_tables WHERE table_name = 'EMPLOYEES';

-- Grant privileges
GRANT SELECT ON hr.employees TO public;
```

---

#### 3. ORA-02292: integrity constraint violated - child record found

**Problem**: Foreign key constraint prevents deletion

**Solution**:
```sql
-- Option 1: Delete child records first
DELETE FROM emp_audit_log WHERE emp_id = 101;
DELETE FROM employees WHERE employee_id = 101;

-- Option 2: Disable constraint (not recommended in production)
ALTER TABLE emp_audit_log DISABLE CONSTRAINT fk_emp_audit_emp;
DELETE FROM employees WHERE employee_id = 101;
ALTER TABLE emp_audit_log ENABLE CONSTRAINT fk_emp_audit_emp;
```

---

#### 4. PL/SQL build validation items

**Problem**: A package or procedure sent through the Oracle listener has no effect

**Solution**: PL/SQL execution through the Oracle listener — procedures, functions, anonymous blocks and common DBMS_ packages — is on the roadmap. Port the package to PL/pgSQL (a schema of functions) and call it through the PostgreSQL interface, or move the logic into the application tier.

---

## Best Practices

### 1. Start with Assessment

- Run compatibility analyzer
- Identify unsupported features
- Plan workarounds before migration

---

### 2. Migrate Schema First

- Test DDL in HeliosDB dev environment
- Fix compatibility issues
- Only then migrate data

---

### 3. Test PL/SQL Thoroughly

- Create comprehensive test suite
- Test all procedures/functions/packages after porting them to PL/pgSQL
- Verify edge cases

---

### 4. Performance Baseline

**Before Migration**:
```sql
-- Measure Oracle performance
SET TIMING ON
SELECT /* your_query */ FROM ...;
```

**After Migration**:
```sql
-- Compare HeliosDB performance
SET TIMING ON
SELECT /* same_query */ FROM ...;
```

---

### 5. Gradual Rollout

- Start with read-only queries
- Then writes to new tables
- Finally cutover transactional workloads

---

## Rollback Plan

### Scenario 1: Schema Issues

**Action**:
```bash
# Rollback to Oracle
export ORACLE_DSN=oracle.example.com:1521/ORCL

# Fix HeliosDB schema
sqlplus hr/password@heliosdb/mydb @fix_schema.sql

# Retry migration
```

---

### Scenario 2: Data Loss Detected

**Action**:
```bash
# Immediate rollback
kubectl set env deployment/my-app ORACLE_DSN=oracle.example.com:1521/ORCL

# Investigate HeliosDB data
sqlplus hr/password@heliosdb/mydb

# Compare row counts
SELECT COUNT(*) FROM employees;  -- HeliosDB
SELECT COUNT(*) FROM employees@oracle_link;  -- Oracle (via DB link)
```

---

### Scenario 3: PL/SQL Errors

**Action**:
```sql
-- Rollback to Oracle
-- Port the failing procedures to PL/pgSQL and retest them
-- through the PostgreSQL interface
```

---

## Case Studies

### Case Study 1: ERP System (Oracle 19c, 5 TB)

**Customer**: Manufacturing company

**Oracle Setup**:
- Oracle 19c Enterprise Edition
- RAC 2-node cluster
- 5 TB database
- 500 concurrent users

**Migration Strategy**: Phased schema migration

**Timeline**: 14 weeks

**Results**:
- **Performance**: 2.5x faster analytics queries
- **Multi-Protocol**: Added PostgreSQL API for web apps
- **Success**: Zero data loss, <5 minutes cutover downtime

---

### Case Study 2: Financial Reporting (Oracle 12c, 500 GB)

**Customer**: Financial services firm

**Oracle Setup**:
- Oracle 12c Standard Edition
- Single instance
- 500 GB database

**Migration Strategy**: Data Pump export/import

**Timeline**: 6 weeks

**Results**:
- **Compatibility**: most SQL ran after testing; PL/SQL procedures, packages and dynamic SQL needed rework
- **Redesign**: DBMS_FLASHBACK usage was redesigned around MVCC
- **Success**: 8-hour maintenance window, all tests passed

---

## Next Steps

### 1. Compatibility Assessment

**Contact**: support@heliosdb.com

**Free Assessment**:
- Run compatibility analyzer
- Review report with HeliosDB engineer
- Estimate timeline and costs

---

### 2. Migration Planning

**Schedule Call**:
- Discuss migration strategy
- Identify risks
- Plan rollback procedures

---

### 3. Training

**HeliosDB for Oracle DBAs** (8-hour course):
- Oracle SQL compatibility
- PL/SQL migration
- DBMS package usage
- Performance tuning

**Enquiries**: [sales@heliosdb.com](mailto:sales@heliosdb.com)

---

### 4. Enterprise Support

**24/7 Migration Support**:
- Dedicated migration engineer
- On-call support during cutover
- Custom tooling development

**Contact Sales**: sales@heliosdb.com

---

## Appendix A: Unsupported Oracle Features

### Features Requiring Redesign

| Oracle Feature | HeliosDB Alternative |
|----------------|----------------------|
| Flashback Query | Use audit tables + triggers |
| Oracle RAC | Use HeliosDB HA clusters |
| Data Guard | Use HeliosDB replication |
| Advanced Queuing | Use DBMS_AQ (basic support) or PostgreSQL NOTIFY/LISTEN |
| Oracle Spatial (advanced) | Use PostGIS via PostgreSQL protocol |
| Fine-Grained Auditing | Use triggers + audit tables |
| Virtual Private Database | Use row-level security (PostgreSQL protocol) |
| Edition-Based Redefinition | Use blue/green deployments |
| Real Application Testing | Use custom testing frameworks |

---

## Appendix B: Migration Checklist

**Pre-Migration**:
- [ ] Run compatibility analyzer
- [ ] Review assessment report
- [ ] Identify unsupported features
- [ ] Plan workarounds for unsupported features
- [ ] Set up HeliosDB cluster
- [ ] Create test environment
- [ ] Migrate schema to test environment
- [ ] Test schema compatibility
- [ ] Fix schema issues
- [ ] Migrate sample data to test environment
- [ ] Test data integrity
- [ ] Port PL/SQL to PL/pgSQL in the test environment
- [ ] Test the ported procedures
- [ ] Fix porting issues
- [ ] Performance baseline (Oracle)
- [ ] Performance testing (HeliosDB)
- [ ] Compare performance
- [ ] Create migration runbook
- [ ] Test rollback procedure
- [ ] Train team on HeliosDB
- [ ] Plan cutover window

**Migration Day**:
- [ ] Announce maintenance window
- [ ] Stop application writes
- [ ] Final data sync
- [ ] Validate data consistency
- [ ] Update application configuration
- [ ] Start application
- [ ] Smoke tests
- [ ] Monitor for issues
- [ ] Rollback if critical issues

**Post-Migration**:
- [ ] Monitor performance (24 hours)
- [ ] Monitor errors (24 hours)
- [ ] Validate reports
- [ ] User acceptance testing
- [ ] Monitor for 1 week
- [ ] Keep Oracle as backup (2 weeks)
- [ ] Decommission Oracle (after 2 weeks)

---

## Document Information

**Version**: 1.0
**Maintainers**: HeliosDB Oracle Migration Team
**Feedback**: support@heliosdb.com

**Related Documentation**:
- [Cassandra Migration Guide](https://www.heliosdb.com/docs/full/reference/guides/cassandra_migration/)
- [MongoDB Migration Guide](https://www.heliosdb.com/docs/full/reference/guides/mongodb_migration/)