How to Control Oracle to PostgreSQL Migration

Due to the modern trend of switching to open-source software as an opportunity to cut the licensing fees, many companies migrate their databases from Oracle to PostgreSQL.Every database specialist knows it is very important to verify that all database objects have been processed properly after the migration is completed. This guide explores main steps of the verification. Migration of database business logic such as stored procedures, functions and triggers is not covered by the whitepaper.

Table Definitions

Oracle allows to explore table definition by running SQL query:

select COLUMN_NAME, DATA_DEFAULT, DATA_TYPE from USER_TAB_COLUMNS where table_name=’your table name’

PostgreSQL can do the same by running the statement:\d table_name

We can say that Oracle table is migrated properly when each column hasequal type, size, NULL check and default value in the target PostgreSQL table. This table sillust rates correct type mapping for Oracle and PostgreSQL.

Oracle	PostgreSQL
BFILE	VARCHAR(255)
BINARY_FLOAT	REAL
BINARY_DOUBLE	DOUBLE PRECISION
BLOB	BYTEA
CHAR(n), CHARACTER(n)	CHAR(n), CHARACTER(n)
CLOB	TEXT
DATE	TIMESTAMP
DECIMAL(p,s), DEC(p,s)	DECIMAL(p,s), DEC(p,s)
DOUBLE PRECISION	DOUBLE PRECISION
FLOAT(p)	DOUBLE PRECISION
LONG	TEXT
LONG RAW	BYTEA
NCHAR(n)	CHAR(n)
NCHAR VARYING(n)	VARCHAR(n)
NCLOB	TEXT
NUMBER(p,0), NUMBER(p), 1 <= p < 5	SMALLINT
NUMBER(p,0), NUMBER(p), 5 <= p < 9	INT
NUMBER(p,0), NUMBER(p), 9 <= p < 19	BIGINT
NUMBER(p,0), NUMBER(p), p >= 19	DECIMAL(p)
NUMBER(p,s), NUMERIC(p,s)	DECIMAL(p,s)
NUMBER, NUMBER(*)	DOUBLE PRECISION
NVARCHAR2(n)	VARCHAR(n)
RAW(n)	BYTEA
REAL	DOUBLE PRECISION
ROWID	CHAR(10)
SMALLINT	SMALLINT
TIMESTAMP(p)	TIMESTAMP(p)
TIMESTAMP(p) WITH TIME ZONE	TIMESTAMP(p) WITH TIME ZONE
VARCHAR2(n)	VARCHAR(n)
XMLTYPE	XML

Let us illustrate migration of table definition from Oracle to PostgreSQL by the following example. Assume, the Oracle table is declared as:

CREATE TABLE people(

id NUMBER(10) NOT NULL,

added DATE,

data BLOB,

commentsCLOB,

PRIMARY KEY(id)

);

PostgreSQL equivalent of this table definition is:

CREATE TABLE people(

id BIGINT NOT NULL,

added TIMESTAMP,

data BYTEA,

commentsTEXT,

PRIMARY KEY(id)

);

Data

The first thing that must be checked here is Oracle and PostgreSQL tables have equal number of records. Both database management systems allow to obtain number of rows in a table through the following query:

SELECT COUNT(*) FROM table_name

Next step of data verification is visual comparison of a random Oracle and PostgreSQL data fragments. Oracle allows to explore fragment of data as follows:

SELECT * FROM table_name OFFSET start_record ROWS FETCH NEXT number_of_records ROWS ONLY;

PostgreSQL supports similar syntax of SELECT-query:

SELECT * FROM table_name LIMIT number_of_records OFFSET start_record

Indexes

Validation of migrated indexes consists of checking a number of Oracle and PostgreSQL indexes per table and comparing indexed columns and attributes of each source and target index.

Oracle provides two queries to get information about primary key and other indexes. For primary key:

SELECT DISTINCT a.constraint_name, a.column_name FROM user_cons_columns a,

user_constraints b WHERE a.table_name='{table name}’ AND

a.constraint_name=b.constraint_name AND b.constraint_type=’P’

ORDER BY a.constraint_name

For other indexes:

SELECT DISTINCT user_indexes.index_name, user_indexes.uniqueness,

user_ind_columns.column_name FROM user_ind_columns,

user_indexes where user_ind_columns.table_name = ‘{table name}’ AND

user_indexes.generated=’N’ AND user_ind_columns.index_name = user_indexes.index_name

AND user_ind_columns.table_name = user_indexes.table_name

ORDER BY user_indexes.index_name

PostgreSQL exposes indexes at the end of table description that is output of the statement\d {name_of_table},for example:

tests=# \d tbl1;

Column  |             Type            |                     Modifiers

———+—————————–+——————-+———————————

 id      | integer                     | not null          | nextval(‘tbl1_id_seq’::regclass)

 added   | timestamp without time zone |                   |

 data    | bytea                       |                   |

length  |bigint                      |                   |

Indexes:

    “tbl1_pkey” PRIMARY KEY, btree (id)

    “idx1” btree (added)

Foreign Keys

Approach to verification of migrated foreign keys is very close to indexes. Oracle exposes information about foreign keys through the query:

SELECT DISTINCT a.constraint_name, a.column_name FROM user_cons_columns a, 

user_constraints b WHERE a.table_name='{table name}’ AND

a.constraint_name=b.constraint_name AND b.constraint_type=’R’

ORDER BY a.constraint_name

PostgreSQL allows to extract data on foreign keys from service table “information_schema”:

SELECT

t_c.constraint_name, t_c.table_name, k_c_u.column_name,

c_c_u.table_name AS foreign_table_name,

c_c_u.column_name AS foreign_column_name

FROM

information_schema.table_constraints AS t_c

    JOIN information_schema.key_column_usage AS k_c_u

      ON t_c.constraint_name = k_c_u.constraint_name

    JOIN information_schema.constraint_column_usage AS c_c_u

      ON c_c_u.constraint_name = t_c.constraint_name

WHERE constraint_type = ‘FOREIGN KEY’ AND t_c.table_name=’table_name’;

Views

Checking migration of Oracle views into PostgreSQL format is the most sophisticated part of overall validation.It is made by comparing CREATE VIEW statement of every view with respect to differences between SQL dialects of source and destination DBMS.

Oracle exposes list of all views in the database using the query:

select VIEW_NAME, TEXT from SYS.USER_VIEWS;

PostgreSQL can do the same via the query:

select viewname, definition from pg_catalog.pg_views where schema name NOT IN (‘pg_catalog’, ‘information_schema’)

Check that none of Oracle specific keywords appear in PostgreSQL CREATE VIEW statement:

• DEFAULT
• FORCE / NO FORCE
• WITH CHECK OPTION
• WITH OBJECT IDENTIFIER
• WITH READ ONLY
• UNDER

Next thing to be verified is that Oracle specific notation of JOIN operators is converted into PostgreSQL standard properly. For example, LEFT JOIN statement in Oracle may be composed as:

SELECT t1.f2, t2.f2 FROM t1, t2 WHERE t1.f1=t2.f1 (+)

PostgreSQL accepts only ANSI SQL standard on LEFT JOIN that is:

SELECT t1.f2, t2.f2 FROM t1 LEFT OUTER JOIN t2 ON t1.f1=t2.f1

RIGHT JOIN in Oracle may be composed using operator (+) as follows:

SELECT orders.order_id, orders.order_date, suppliers.supplier_name

FROM suppliers, orders

WHERE suppliers.supplier_id(+) = orders.supplier_id;

In PotgreSQL the same query must be re-written as:

SELECT orders.order_id, orders.order_date, suppliers.supplier_name

FROM suppliersRIGHT OUTER JOIN orders

ON suppliers.supplier_id = orders.supplier_id;

Besides the differences listed above, queries in Oracle and PostgreSQL are distinguished due to different sets of embedded functions.When migrating views every Oracle function must be converted into PostgreSQL equivalent as it is specified in table below.

Oracle	PostgreSQL
dbms_random.value	RANDOM()
INSTR($str1, $str2)	POSITION($str2 in $str1)
LCASE ($a)	LOWER($a)
NVL($a, replace_with)	COALESCE($a, replace_with)
SYSDATE	CURRENT_DATE
UCASE($a)	UPPER($a)
SYS_EXTRACT_UTC($date)	CAST($date at time zone ‘utc’ AS timestamp)

More articles about Oracle to PostgreSQL migration can be found at: https://www.convert-in.com/docs/ora2pgs/contents.htm