ERP 2.50:Oracle to PostgreSQL migration - automatic tool research

Contents 1 Introduction 2 Guideline compliant database 3 Database full of Oracle specific elements 3.1 Syntax 3.2 Data Types 3.3 NULL 3.4 Sequences 3.5 Other Joins 3.6 NLS * vs. LC * 3.7 ROWNUM and ROWID 3.8 Things That Won’t Work Directly 3.9 PL/SQL 3.9.1 Triggers 3.9.2 Procedures/ Functions 4 Tools 4.1 ora2pg 4.1.1 Installation 4.1.2 Configuration 4.1.3 Execution 4.1.4 Results 4.2 orafce 4.2.1 Installation 4.2.2 Results 5 Links 6 Conclusions

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

• Identifiers:

Oracle case folds to upper case, PostgreSQL to lower case. Big trouble if you mix quoted and unquoted identifiers.

• Column aliases

SELECT foo [AS] bar — Most Oracle applications omit the AS, but PostgreSQL requires it. Fixed in PostgreSQL 8.4.

• MINUS Change to EXCEPT.

• SQL key words, usually not a big problem, but should be kept in mind.

• “FROM dual” Easy to work around (or use orafce).

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

• Oracle syntax: sequence_name.nextval
• PostgreSQL syntax: nextval(’sequence_name’)

Other Joins

• Example 1:

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

becomes

SELECT * FROM a LEFT JOIN b ON a.x = b.y

• Example 2:

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)

• Example 3:

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

• ROWNUM --> LIMIT
• ROWID --> ctid

Things That Won’t Work Directly

• CONNECT BY Try contrib/tablefunc.
• Materialized views Write your own wrapper.
• Snapshots Write your own wrapper.
• Database links Use contrib/dblink plus views.
• Autonomous transactions Try dblink.
• Synonyms Try views or wrapper or schema path.
• Partitioning Write your own system.

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

• Function body must be quoted (dollar quoting).
• For variable declarations, DECLARE is needed in PostgreSQL.

• Usually, you need less cursors in PostgreSQL.

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.

• Cursor Variables

BEGIN
   FOR x IN foo LOOP

Use RECORD:

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

• dbms_output -> raise exception

• the exceptions are similar but with different names

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.

• Url: http://pgfoundry.org/projects/ora2pg/
• Platform: any (console, perl script)
• License: artistic license
• Open Source: yes

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:

• Perl 5

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

• DBD::Oracle module for Perl

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:

• If the error are of -lclntsh not found:

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/

• If the error is:

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:

• http://wiki.postgresql.org/wiki/Oracle_Compatibility_Tasks Oracle Compatibility Tasks
• http://www.postgresql.org/docs/current/interactive/plpgsql-porting.html Porting from pl/sql to pl/pgsql
• http://wiki.postgresql.org/wiki/PostgreSQL_for_Oracle_DBAs PostgreSQL for Oracle DBAs
• http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL#Oracle Other links to convert Oracle Database to PostgreSQL Database
• https://fedorahosted.org/spacewalk/wiki/PostgresPortingGuidelines Postgres Porting Guidelines
• http://www.mail-archive.com/spacewalk-devel@redhat.com/msg00996.html PGPORT: Instructions for porting procedures/functions

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.

Category: Migration and Upgrades