View source | Discuss this page | Page history | Printable version   

ERP 2.50:Oracle to PostgreSQL migration - automatic tool research

Contents

Introduction

This document describes the state of the art in the migration tools from Oracle to PostgreSQL databases.

First of all it is necessary to say that translating an Oracle database into a PostgreSQL database is not a trivial task, in many cases some elements of the Oracle database don't have direct conversion in PostgreSQL or even it s necessary to redo some parts in a completely different way.

In Peter Eisentraut at PGCon2008 words: "Compatibility and compatibility layers are an illusion".

There aren't official tools in the market that translate completely an entire Oracle database, but there are some that can convert the tables and data. The most complex part is convert ing the PL/SQL syntax into the PL/pgSQL syntax and in this aspect there isn't any application that can support all the syntax of Oracle PL/SQL.

Guideline compliant database

If the database implementation follows the Openbravo SQL/PL-SQL code rules, then the best option is to use DBSourceManager by following the migration guide.

Database full of Oracle specific elements

If your database data and the PL/SQL use a lot of Oracle specific elements, it exists the possibility that some of the elements are not supported by DBSourceManager. In this case, you need to search other alternatives.

This document is based in this guide: Porting Oracle Applications to PostgreSQL

Syntax

Oracle case folds to upper case, PostgreSQL to lower case. Big trouble if you mix quoted and unquoted identifiers.
SELECT foo [AS] bar — Most Oracle applications omit the AS, but PostgreSQL requires it. Fixed in PostgreSQL 8.4.

Data Types

varchar2 → varchar or text
clob, long → varchar or text
nchar, nvarchar2, nclob → (varchar or text)
number → numeric or bigint or int or smallint or double
precision or real (bug potential)
binary float/binary double → real/double precision
blob, raw, long raw → bytea (additional porting required)
date → date or timestamp

NULL

Oracle has a infamous behavior: NULL = , consequently = is not true. This causes inconsistencies and it can cause a data loss during the conversion.

The Oracle function transform_null_equals does not help here.

Sequences

Other Joins

 SELECT * FROM a, b WHERE a.x = b.y(+)

becomes

SELECT * FROM a LEFT JOIN b ON a.x = b.y
SELECT ...
   FROM A, B, C
   WHERE A.A_ID (+) = B.A_ID
     AND C.C_KEY(+) = B.C_KEY

becomes

SELECT ...
   FROM A
        RIGHT JOIN
        B ON (A.A_ID = B.A_ID)
        LEFT JOIN
        C ON (C.C_KEY = B.C_KEY)
SELECT ...
 FROM A, B, C, D, E
WHERE A.A_ID = B.A_ID
  AND B.B_ID = C.A_ID(+)
  AND B.B_KEY = C.B_KEY(+)
  AND C.C_ID = D.C_ID(+)
  AND B.A_ID = E.A_ID(+)
  AND B.B_KEY = E.B_KEY(+)
  AND ’CONSTANT’ = C.X_ID(+)

cannot be translated.

NLS * vs. LC *

Approximate analogies:

NLS CALENDAR
NLS COMP lc collate = ’C’
NLS CURRENCY lc monetary
NLS DATE FORMAT DateStyle
NLS DATE LANGUAGE lc messages, lc time (8.4?)
NLS LANG, NLS LANGUAGE LANG, client encoding
NLS NCHAR
NLS NUMERIC CHARACTERS lc numeric
NLS SORT lc collate
NLS TERRITORY LANG, lc *

ROWNUM and ROWID

Things That Won’t Work Directly

PL/SQL

Converting the PL/SQL code into PL/pgSQL syntax is the most complicated part, and it is recommended to take a look at this official porting document.

There is a lot of differences, the most typical:

Triggers

Oracle uses inline trigger actions:

CREATE TRIGGER foo AFTER action ON table
   AS BEGIN ... END;

becomes

CREATE OR REPLACE FUNCTION foo_tg() RETURNS TRIGGER
   LANGUAGE xxx
   AS $$ ... $$;
CREATE TRIGGER foo AFTER action ON table
   EXECUTE PROCEDURE foo_tg();

Note: FOR EACH STATEMENT is the default in Oracle and PostgreSQL.

Procedures/ Functions

CREATE FUNCTION ... RETURN type

becomes

CREATE FUNCTION ... RETURNS type
CURSOR foo IS SELECT ...;
BEGIN
    FOR x IN foo LOOP

can be simplified to

BEGIN
    FOR x IN SELECT ... LOOP

Note: The x is defined implicitly in Oracle. In PostgreSQL, you need to declare it.

BEGIN
   FOR x IN foo LOOP

Use RECORD:

DECLARE
   CURSOR foo IS SELECT ..;
   x RECORD;
BEGIN
   FOR x IN foo LOOP

Tools

There isn't any tool that converts all the specific Oracle elements, but an alternative to DBSourceManager (with less support in PL/SQL code) can be ora2pg and orafce.

ora2pg

Ora2Pg is a Perl module to exports an Oracle database schema into a PostgreSQL compatible schema. It connects your Oracle database, extracts its structure, and generates an SQL script that you can load into your PostgreSQL database.

It converts a lot of elements of the database:

  1. TABLE Export tables
  2. PACKAGE Export packages
  3. DATA Export datas from table as INSERT statement
  4. COPY Export datas from table as COPY statement
  5. VIEW Export views
  6. GRANT Export grants
  7. SEQUENCE Export sequences
  8. TRIGGER Export triggers
  9. FUNCTION Export functions
  10. PROCEDURE Export procedures
  11. TABLESPACE Export tablespace (PostgreSQL >= 8 only)

In any case its strengths are the export of tables and its data, it also transform PL/SQL but doesn't support a lot of things.

The transformation of the PL code is achieved by regular expressions and can be added more in the PLSQL.pm file.

Example of regular expression that replaces the dbms_output for raise exception:

$str =~ s/DBMS_OUTPUT\.(put_line|put|new_line)*([^\)]+\);)/&raise_output($2)/igse;

Installation

The installation of this tool can be a pain and a challenge, mainly to install the DBD::Oracle perl module that needs to change many things in order to compile.

This is the guide to install it in Gentoo:

$ sudo emerge -av perl DBI perl-Compress-Zlib Crypt-OpenSSL-Random
$ sudo perl -MCPAN -e shell
$ cpan> install String::random
emerge -av oracle-instantclient-sqlplus

Note: follow the instructions at the end of the emerge message to download the necessary files

$ sudo perl -MCPAN -e shell
$ cpan> install DBD::Oracle

Note: for run the test you need the scott/triger user in oracle and with dba role

This probably fails and this are the workarounds for two typical errors:

cd $ORACLE_HOME/rdbms/demo
cp demo_xe.mk demo_xe.mk.back
vi demo_xe.mk

Set the ICLIBHOME with the path of the lib of the clients, probably the are taking taking the server libs, it should appear something like this:

ICLIBHOME=/usr/lib64/oracle/10.2.0.3/client/lib/

x86_64-pc-linux-gnu-gcc: unrecognized option '-wchar-stdc++'

x86_64-pc-linux-gnu-gcc: unrecognized option '-cxxlib-gcc'

cd $ORACLE_HOME/rdbms/demo
cp demo_xe.mk demo_xe.mk.back
vi demo_xe.mk

Remove the unrecognized options at the end of the file.

Configuration

Edit the ora2pg.conf URL: change at the end the SID, also check the host and the port, the protocol let it as is.

OracleUser : Oracle system user

OralcePass: pass of the system user

Schema: database, user to export (in uppercase!!!)

Objects to export: list comma separated of objects to export, for openbravo: TABLE,VIEW,SEQUENCE,FUNCTION,PROCEDURE

Execution

chmod +x ora2pg.pl
./ora2pg.pl ora2pg.conf

Results

The speed of this tool is good and is able to export all the Openbravo ERP database in about 4 mins but it don't translate some necessary elements so the data can't be inserted directly into PostgreSQL.

It's a nice tool, open source and easily configurable but need a lot more support for specific oracle elements specially in PL/SQL.

orafce

Is a tool that insert in PostgreSQL some typical functions of Oracle.

Homepage: http://pgfoundry.org/projects/orafce/ Platform: any (source code) License: BSD Open source: yes

For a complete list of the functionality that support orafce, please read this guide

Installation

For installation you can follow this guide

Results

The purpose of this tool is not to convert a Oracle application in a PostgreSQL application, instead try to add support for Oracle functions in PostgreSQL.

Can be dangerous because some functions doesn't work exactly as in Oracle.

Links

This is a collection of useful links in this topic:

Conclusions

There is no tool that automatically converts an Oracle database into PostgreSQL, specially the PL/SQL code that is very complex to convert.

The best tool to convert an Openbravo ERP database from Oracle to PostgreSQL is DBSourceManager.

As an alternative one could use ora2pg and do a lot of work for convert all the things that not convert ora2pg.

If you are too used to the Oracle syntax and you don't want to change it into PostgreSQL syntax, you can try orafce.

Retrieved from "http://wiki.openbravo.com/wiki/ERP_2.50:Oracle_to_PostgreSQL_migration_-_automatic_tool_research"

This page has been accessed 20,870 times. This page was last modified on 14 June 2011, at 11:04. Content is available under Creative Commons Attribution-ShareAlike 2.5 Spain License.