Skip to content
HeliosDB HeliosDB Full

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
  2. Why Migrate?
  3. Pre-Migration Checklist
  4. Migration Strategies
  5. Step-by-Step Migration
  6. Compatibility Notes
  7. Common Issues & Solutions
  8. Post-Migration Optimization
  9. 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).

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 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)
  • Define acceptance tests: row counts, checksums, and application test suites

Migration Strategies

StrategyWhenHow
PostgreSQL route (recommended)Most applicationsConvert schema and PL/SQL to PostgreSQL syntax, load data over the PostgreSQL listener, switch the application to a PostgreSQL driver
Oracle-listener routeComponents that must keep an Oracle thin driver and use plain SQLEnable --enable-oracle, rewrite the statements listed in the quick start, keep the driver
MixedLarge estatesMove 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:

Terminal window
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 (Apache-2.0), also moves Oracle schemas and data into HeliosDB with validation.

Apply the DDL with psql:

Terminal window
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:

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:

Terminal window
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

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:

OracleHeliosDB (PostgreSQL syntax)
NVL(a, b)COALESCE(a, b)
DECODE(x, a, r1, r2)CASE WHEN x = a THEN r1 ELSE r2 END
SYSDATECURRENT_TIMESTAMP
FROM DUALomit FROM
ROWNUM <= nLIMIT n
seq.NEXTVALnextval('seq')
a = b(+)LEFT JOIN
CONNECT BYWITH RECURSIVE
MINUSEXCEPT
'' (empty string) is NULL'' and NULL differ; review comparisons
PL/SQL packagesschemas of functions, or application code
DBMS_OUTPUT, UTL_FILE, DBMS_SCHEDULERapplication logging, files and scheduling
Database linksapplication-level integration

Common Issues & Solutions

IssueSolution
CREATE INDEX has no effect on query speedStart the server with --enable-secondary-indexes and recreate the indexes
Converted procedure called with CALL shows no effectConvert it to a function and call it with SELECT, then test
Date values differOracle DATE includes a time of day; use TIMESTAMP columns and compare with explicit formats
GRANT returns 0A000Authorization is not enforced in the current release; isolate applications in separate servers
Empty-string comparisons behave differentlyNormalise '' to NULL during the load, or adjust the predicates

Post-Migration Optimization

Get Migration Help

Next Steps

  1. Run the assessment checklist above.
  2. Start a test server with the Quick Start.
  3. Convert one schema end to end and validate it before planning the full migration.