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
- 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:
- TABLE Export tables
- PACKAGE Export packages
- DATA Export datas from table as INSERT statement
- COPY Export datas from table as COPY statement
- VIEW Export views
- GRANT Export grants
- SEQUENCE Export sequences
- TRIGGER Export triggers
- FUNCTION Export functions
- PROCEDURE Export procedures
- 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.