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

# Migrating from Oracle to HeliosDB

This guide describes how to move an Oracle schema, its data and the application that
uses it to HeliosDB Full. The work is done with standard tools: a schema-conversion
tool, `psql` or your ETL tooling for data, and your application's own drivers.

**Effort**: depends on how much logic lives in PL/SQL; plan an assessment first.
**Downtime**: a cutover window for the final data sync, unless your application can
write to both systems during the transition.

---

## Table of Contents

1. [Overview](#overview)
2. [Why Migrate?](#why-migrate)
3. [Pre-Migration Checklist](#pre-migration-checklist)
4. [Migration Strategies](#migration-strategies)
5. [Step-by-Step Migration](#step-by-step-migration)
6. [Compatibility Notes](#compatibility-notes)
7. [Common Issues & Solutions](#common-issues--solutions)
8. [Post-Migration Optimization](#post-migration-optimization)
9. [Get Migration Help](#get-migration-help)

---

## Overview

HeliosDB Full speaks the PostgreSQL wire protocol and also has an Oracle TNS listener.
The most reliable path for an Oracle application is the PostgreSQL route: convert the
schema and code with a PostgreSQL-targeted conversion tool, load the data over the
PostgreSQL listener, and point the application at HeliosDB with a PostgreSQL driver.
The Oracle listener helps for parts of an application that must keep an Oracle thin
driver and send SQL that HeliosDB runs as is (see the
[Oracle Quick Start](/docs/full/quick-starts/protocols/oracle_quick_start/)).

## Why Migrate?

- One server for relational, document, key-value and wide-column access, with
  PostgreSQL, MySQL, Oracle, MongoDB, Cassandra and Redis listeners
- Standard PostgreSQL tooling and drivers for development and operations
- Commercial licence with source availability (SSPL-1.0); contact
  [sales@heliosdb.com](mailto:sales@heliosdb.com) for pricing to compare costs for
  your workload

## Pre-Migration Checklist

- [ ] Inventory schemas, tables, views, sequences, synonyms, PL/SQL packages,
      procedures, functions and triggers (`DBA_OBJECTS` grouped by type)
- [ ] Measure the amount of PL/SQL (`DBA_SOURCE` line counts per object)
- [ ] List Oracle features in use: database links, Advanced Queuing, Spatial, Text,
      `DBMS_` and `UTL_` packages, materialized views, partitioning
- [ ] Capture the SQL the application sends (AWR/ASH, `V$SQL`, driver logging)
- [ ] Run a conversion assessment (Ora2Pg `SHOW_REPORT` with `ESTIMATE_COST`, or AWS
      SCT / Google DMS assessment) to size the manual work
- [ ] Prepare a HeliosDB Full test server
      ([Quick Start](/docs/full/quickstart/))
- [ ] Define acceptance tests: row counts, checksums, and application test suites

## Migration Strategies

| Strategy | When | How |
|----------|------|-----|
| **PostgreSQL route** (recommended) | Most applications | Convert schema and PL/SQL to PostgreSQL syntax, load data over the PostgreSQL listener, switch the application to a PostgreSQL driver |
| **Oracle-listener route** | Components that must keep an Oracle thin driver and use plain SQL | Enable `--enable-oracle`, rewrite the statements listed in the quick start, keep the driver |
| **Mixed** | Large estates | Move services one at a time; each picks the route that fits it |

## Step-by-Step Migration

### Step 1: Convert the schema

Use a PostgreSQL-targeted converter. With [Ora2Pg](https://ora2pg.darold.net/):

```bash
ora2pg -t TABLE    -o tables.sql    -c ora2pg.conf
ora2pg -t SEQUENCE -o sequences.sql -c ora2pg.conf
ora2pg -t VIEW     -o views.sql     -c ora2pg.conf
```

HeliosDB's own toolkit, [Any2HeliosDB](https://github.com/HeliosDatabase/Any2HeliosDB)
(Apache-2.0), also moves Oracle schemas and data into HeliosDB with validation.

Apply the DDL with `psql`:

```bash
psql "host=heliosdb.internal user=postgres" -f sequences.sql -f tables.sql -f views.sql
```

Review the output: Oracle `NUMBER` maps to `NUMERIC`/`BIGINT`, `VARCHAR2` to
`VARCHAR`/`TEXT`, `DATE` to `TIMESTAMP` (Oracle dates carry a time), `CLOB` to `TEXT`,
`BLOB`/`RAW` to `BYTEA`. Create indexes with the server running
`--enable-secondary-indexes`, so they are built and maintained.

### Step 2: Convert PL/SQL

Convert procedures, functions and triggers to PL/pgSQL (Ora2Pg `-t FUNCTION`,
`-t PROCEDURE`, `-t TRIGGER`, `-t PACKAGE`), or move the logic into the application
tier. HeliosDB Full runs PL/pgSQL functions and `BEFORE ... FOR EACH ROW` triggers
created over the PostgreSQL listener:

```sql
CREATE FUNCTION raise_sal(p_id INT, p_amt NUMERIC) RETURNS NUMERIC AS $$
DECLARE v NUMERIC;
BEGIN
  UPDATE emp SET sal = sal + p_amt WHERE empno = p_id;
  SELECT sal INTO v FROM emp WHERE empno = p_id;
  RETURN v;
END;
$$ LANGUAGE plpgsql;

SELECT raise_sal(7839, 100);
```

Test every converted routine against expected results; prefer functions called with
`SELECT` over procedures called with `CALL`.

### Step 3: Move the data

Export from Oracle as CSV (Ora2Pg `-t COPY`, SQLcl `SET SQLFORMAT csv`, or your ETL
tool) and load it with `COPY ... FROM STDIN`, which HeliosDB supports:

```bash
psql "host=heliosdb.internal user=postgres" \
  -c "\copy emp FROM 'emp.csv' WITH (FORMAT csv, HEADER true)"
```

Load large tables in parallel, one table per session, and create indexes after the
load when you can.

### Step 4: Validate

```sql
SELECT COUNT(*) FROM emp;                       -- compare with Oracle
SELECT SUM(sal), MIN(hiredate), MAX(hiredate) FROM emp;
```

Compare row counts and per-column aggregates for every table, and run the
application's test suite against HeliosDB. Validate result sets of the queries your
application relies on (ordering, analytic functions, date handling) rather than
assuming equivalence.

### Step 5: Switch the application

- **Java:** replace the Oracle JDBC URL with `jdbc:postgresql://heliosdb.internal:5432/<db>`
  and the PostgreSQL driver; review Hibernate dialect settings (`PostgreSQLDialect`).
- **Python:** `oracledb` to `psycopg`; bind style `:name` becomes `%(name)s`.
- **.NET:** `Oracle.ManagedDataAccess` to `Npgsql`.

Rewrite Oracle-only SQL in the application (see Compatibility Notes).

### Step 6: Cut over

1. Freeze writes on Oracle (maintenance window).
2. Load the delta (tables changed since the bulk load) and re-run validation.
3. Point the application at HeliosDB; keep Oracle read-only for an agreed fallback
   period.

## Compatibility Notes

Rewrite these Oracle forms when moving SQL to HeliosDB:

| Oracle | HeliosDB (PostgreSQL syntax) |
|--------|------------------------------|
| `NVL(a, b)` | `COALESCE(a, b)` |
| `DECODE(x, a, r1, r2)` | `CASE WHEN x = a THEN r1 ELSE r2 END` |
| `SYSDATE` | `CURRENT_TIMESTAMP` |
| `FROM DUAL` | omit `FROM` |
| `ROWNUM <= n` | `LIMIT n` |
| `seq.NEXTVAL` | `nextval('seq')` |
| `a = b(+)` | `LEFT JOIN` |
| `CONNECT BY` | `WITH RECURSIVE` |
| `MINUS` | `EXCEPT` |
| `''` (empty string) is `NULL` | `''` and `NULL` differ; review comparisons |
| PL/SQL packages | schemas of functions, or application code |
| `DBMS_OUTPUT`, `UTL_FILE`, `DBMS_SCHEDULER` | application logging, files and scheduling |
| Database links | application-level integration |

## Common Issues & Solutions

| Issue | Solution |
|-------|----------|
| `CREATE INDEX` has no effect on query speed | Start the server with `--enable-secondary-indexes` and recreate the indexes |
| Converted procedure called with `CALL` shows no effect | Convert it to a function and call it with `SELECT`, then test |
| Date values differ | Oracle `DATE` includes a time of day; use `TIMESTAMP` columns and compare with explicit formats |
| `GRANT` returns `0A000` | Authorization is not enforced in the current release; isolate applications in separate servers |
| Empty-string comparisons behave differently | Normalise `''` to `NULL` during the load, or adjust the predicates |

## Post-Migration Optimization

- Enable `--read-cache-mb` and `--enable-compaction`; see the
  [Performance Tuning Guide](/docs/full/guides/user/performance_tuning_guide/).
- Review the slow-statement log (`--log-min-duration-statement`) during the first weeks.
- Set up backups ([Backup & Disaster Recovery](/docs/full/deployment/production_deployment_backup/)).

## Get Migration Help

- [Any2HeliosDB](https://github.com/HeliosDatabase/Any2HeliosDB) migration toolkit
- Community: [Discord](https://discord.gg/yTykuUrFXc)
- Migration assessment and services: [sales@heliosdb.com](mailto:sales@heliosdb.com)

## Next Steps

1. Run the assessment checklist above.
2. Start a test server with the [Quick Start](/docs/full/quickstart/).
3. Convert one schema end to end and validate it before planning the full migration.