Splendid Data background



Migrations of Oracle based applications to Postgres

  • Both Oracle Enterprise Edition (EE)/Standard Edition (SE) and Postgres are relational databases (RDBMS). For a functional equation between Oracle EE versus Postgres see the document “SD-Functional equation Oracle EE vs. PostgresPURE” see tab “Documents”.
  • The core components of a database are the Data Definition Language (DDL) and the Data Manipulation Language (DML).
  • Migration can/will affect your database architecture, your applications that are using the database and your technology stack.
  • There is tooling available that supports all kinds of migrations…but:
    • Some of them will cause a new vendor lock-in due to the use of a compatibility layer that is NOT Open Source (e.g. EnterpriseDB).
    • Some of them do not take into account the most optimum implementation of Postgres (hence they do not use the strengths of Postgres).
    • Some of them are limited to parts of the migration only and leave you in the dark about the rest. They do not indicate what has been migrated, changed or not migrated at all (e.g. Ora2Pg).
Splendid Data, migrations and tooling
  • Every migration is an unique project, each application has its own specifics and each customer has his/her own set of requirements.
  • Specific tooling is used to assess the Oracle database (DDL) and the Oracle PL/SQL code (DML) that is stored in the database.
  • The translation tooling is able to automate a migration of the DDL and DML to some extent.
  • From a database perspective we are able to:
    • migrate the Oracle DDL to Postgres DDL mostly automatically to native PL/pgSQL (exceptions: partitioned tables and certain views), and
    • migrate the Oracle DML (PL/SQL code) to Postgres DML (PL/pgSQL code) almost automatically (approx. 80%). For more detailed information see the document “SD-84 Reasons to choose SD for migration Oracle to Postgres” see tab “Documents”.
  • The overall aim: the Splendid Data tooling leaves no footprint (proprietary code and/or tooling) behind…no vendor lock in!
From an application perspective
  • In most cases a database will run with an application on top of it. This application “uses and manipulates” the content of the database. A migrated database without the application moving along with it is of NO USE.
  • Possible uses of front-ends:
    • If the application is based on Java, it is usually easy to apply the application “as is” on the migrated database (DDL/DML).
    • A front-end in .NET: same as Java front-ends, normally there are no big issues.
    • All other types of front-ends like Oracle Forms, Oracle ADF, Oracle APEX, UNIFACE, COBOL etc. need to be assessed very carefully before anything can be said about feasibility.
    • In cases where BI-solutions are in place, migration will entirely depend on the BI-technology used and this needs to be assessed as well.
Advantages of the Splendid Data approach
  • Transparent approach…no surprises!
  • Migration assessment will be done by experts with extensive experience in both Oracle and Postgres.
  • Migration of the DDL and data is often easy to do.
  • Migration of the DML (PL/SQL code) that is stored in the Oracle database can be done automatically (approx. 80%).
  • The translated output is Postgres formatted native PL/pgSQL code and is applicable to the desired target-release of Postgres (9.4 up to the latest release).
  • The migration tooling will leave no footprint…100% Open Source…no vendor lock-in!
  • Maximises the use of Postgres capabilities.
  • Gives the opportunity to get rid of all expensive Oracle tooling (like Oracle RAC, Golden Gate, Data Guard, Weblogic etc.).
  • Provides the path from vendor lock-in to freedom!



Migration Assessment
  • Investigation: 1 day General Assessment – Done by Splendid Data and Free of Charge.
  • Technical Analysis.
  • Minimal customer effort.
  • Detailed report of analyzed DDL/DML.
  • Define next steps.
From assessment to proposal…1st step “What will be done?”
  • Pre-analysis interview to get a clear picture of the environment, which needs to be migrated.
  • Addressing the customer’s requirements before, during and after the migration.
  • Analysis of the schema’s (DDL & DML) to be migrated.
  • Find possible issues:
    • Unsupported features.
    • Unsupported syntax.
    • Implementation differences.
  • Findings/results will be documented and include workarounds in the detailed report.
From assessment to proposal…2nd step “Migration proposal”
  • Splendid Data architects do an evaluation based on the findings/results of the assessment and additional information regarding specifics of the Oracle based applications,
  • The evaluation may result in the following conclusions:
    • Additional information is needed to decide whether a migration is doable: plan a meeting with the client.
    • A migration is too elaborate: plan a meeting with the client and explain why!
    • A migration is doable: a migration proposal is offered to the client.

Actual migration

Actual migration…step 1 The Database

DDL and data
  • Migration of the DDL is often easy to do.
  • Migration of the data depends on the quality of the data, but in general is also easily doable.


DML: PL/SQL code → PL/pgSQL code
  • Is approx. 80% automatic.
  • Native → use Postgres in the right way.
  • Leaves no footprint behind.

Actual migration…step 2 The Middleware

  • Migration of Oracle Weblogic to an Open Source middleware solution is usually an easy task.
  • If an Open Source middleware solution is already used, it does not need to be touched!
  • Specific use of an ESB, ORM-layer and/or Message Queues need to be addressed and assessed.


Actual migration…step 3 The Application/UI

OLTP Application/UI
  • Java → Java, in general easy to do: only involves adjustment to some database calls.
  • .Net → .Net, in general easy to do: only involves adjustment to some database calls.
  • Others (Oracle Forms, Oracle ADF, Oracle APEX, Uniface, Cobol etc.)…need to be assessed.


  • Depends on the technology used (assessment).
  • Depends on the technology used (assessment).
Integration with other IS’n/Application(s)
  • Depends on the technology used (assessment).
  • Depends on the technology used (assessment).


Functional equation Oracle EE vs. PostgresPURE v1.2 20161102
84 Reasons to choose SD for migration Oracle to Postgres v1.0 20160419