problems and solutions for migrating Oracle database into Postgresql

After a long break this is my first post, this is about migrating oracle database into Postgresql.

In this migration session I found all these important points, so I Jordan almost all the points to concern to succeed this process bringing oracle database into Postgresql is a simple process only but only thing is we should have patience to change the queries

According to the Postgresql in our project.

Change the Database Scripts from existing Oracle to Postgresql

—-> Change the Data Types

—-> Write Triggers and Sequences

—-> Write Unique Index

—-> Changing method names according to the Postgresql

Postgresql all the data in the Lowercase, So here we have to take care in our code. It is very strict at executing queries that means if we for get space after the ‘AND’

it will get fails and if we forgets “AS” where ever it needs then also it will get fail.

Important issues:

System Date:

“SELECT SYSDATE FROM DUAL” generally we use this query for System date but it becomes “select current_timestamp”

for SYSDATE the Postgresql provide current_timestamp

Dual:

Dual is not supported by the Postgresql

Sub Queries Selection:

Oracle: sub queries allowed but it can be nested up to 255 levels.

Postgresql: In the WHERE and HAVING clauses the use of sub queries (sub selects) is allowed, the sub queries using “FROM” clause is available from PostGres7.1

CHECK:

In Postgresql, it doesn’t have the restriction for the CHECK column constraints.

Updateable views:

It is not supported by the Postgresql but there is a way to bring the same functionality by using “INSTEAD”

Partial rollback:

There is no support for Partial rollback

Changing the Data Types:

The data Types gets differ from Oracle to Postgresql; here we need to change only four data Types in our scripts.

They are

varchar2, varchar —-> varchar

NUMBER(x, y) —-> NUMERIC(x, y)

NUMBER(x) —-> NUMERIC(x)

LONG —-> BIGINT

date —-> Date or timestamp

nchar, nvarchar2, nclob —-> varchar or text

Binary float/binary double —-> real, double

blob, raw, long raw —-> bytea

Write Triggers and Sequences:

Here the program can use same oracle sequence query for Postgresql, only we need to write the trigger part.

Ex: Example code snippet to change the oracle trigger into Postgresql Trigger

Oracle Trigger:

CREATE TRIGGER RT_PENDING_WORK_SEQ_TRIGGER

BEFORE INSERT ON RT_PENDING_WORK

FOR EACH ROW

BEGIN

SELECT RT_PENDING_WORK_SEQ.NEXTVAL INTO :NEW.SL_NO FROM DUAL;

END;

/

Postgresql Trigger:

CREATE OR REPLACE FUNCTION RT_PENDING_WORK_trigger_func() RETURNS TRIGGER AS $RT_PENDING_WORK_trigger$

BEGIN

NEW.SL_NO:=nextval(’rt_pending_work_seq’); —Here we have to abserver one –important point I wrote rt_pending_work_seq in lower case with single quatation marks, PostGREs is case –sensitive in postgres all data in lower case

return NEW;

END;

$RT_PENDING_WORK_trigger$ LANGUAGE plpgsql;

CREATE TRIGGER RT_PENDING_WORK_trigger BEFORE INSERT ON RT_PENDING_WORK FOR EACH ROW EXECUTE PROCEDURE RT_PENDING_WORK_trigger_func();

So here the programer’s duty is change the table name(RT_PENDING_WORK_trigger_func –> “TABLENAME_trigger_func” etc…) in the above trigger

There are many other issues something like name of methods get changed, so we have check our code and have alter that into Postgresql related.

Even to migrate Oracle Data Base into Postgresql there are many ready made tools are available in the internet and they are open source.

Link1: http://orafce.projects.postgresql.org

Link2: http://ora2pg.projects.postgresql.org

Link3: http://tora.sourceforge.net