The process of Oracle to PostgreSQL migration includes converting queries that are part of views, stored procedures or triggers. This stage may require human processing because syntax of queries in Oracle and PostgreSQL is not the same and there is no direct equivalent for some patterns or inbuilt functions. This article explores conversion of most important patterns used in Oracle SQL queries. It is assumed that target audience has basic knowledge in database programming. 

First step is to extract Oracle queries from the database in form of SELECT-statements. This is how is can be done for Oracle views:

select VIEW_NAME, TEXT from SYS.USER_VIEWS;

Oracle offers a number of options for composing queries that are not supported by PostgreSQL. These patterns must be omitted during conversion: 

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

Oracle has built-in table DUAL used to compose queries which do not call for any table, for example: 

SELECT 1 FROM DUAL;

In PostgreSQL the same table must be created for further use in queries as follows: 

create table dual (varchar(1) not null );

insert into dual(dummy) values(‘x’);

All built-in functions missing in the target DBMS must be replaced by the appropriate equivalents during Oracle to PostgreSQL migration as follows:

1. CURTIME() is replaced by LOCALTIME(0)
2. DAY($a) or DAYOFMONTH($a) is replaced by expression EXTRACT(day from date($a))::integer
3. DateAdd($Date, $Format, $Days, $Months, $Years) is replaced by expression

$Date  + cast(‘$Days day’ as interval) + cast(‘$Months months’ as interval) + cast(‘$Years years’ as interval)

4. DECODE() is replaced by CASE-expression as follows

SELECT colorid, 

CASE colorid

WHEN ‘1’ THEN ‘white’ 

WHEN ‘2’ THEN ‘black’ 

WHEN ‘3’ THEN ‘red’ 

ELSE NULL 

END AS ‘colorname’

FROM colors

5. INSTR($str1, $str2) is replaced by POSITION($str2 in $str1) 
6. LCASE($str) is replaced by LOWER($str)
7. LOCATE($str1,$str2) is replaced by POSITION($str1 in $str2)
8. SUBSTR($string, $from, $for) is replaced by SUBSTRING($string, $from, $for)
9. NVL($a, $replace_with) is replaced by COALESCE($a, $replace_with)
10. RAND is replaced by RANDOM
11. REGEXP_LIKE($string, $pattern) is replaced by expression $string LIKE $pattern
12. SYSDATE is replaced by CURRENT_DATE
13. UCASE($str) is replaced by UPPER($str) 
14. SYS_GUID() can be replaced by uuid_generate_v1(). In early versions of PostgreSQL (before v9.4) this function depended on the OSSP UUID library, the following expression may be used to avoid such dependence: 

SELECT md5(random()::text || clock_timestamp()::text)::uuid

15. WEEK($a) is replaced by EXRACT(week from date($a)) 
16. YEAR($a) is replaced by EXRACT(year from date($a))

This brief guide on Oracle to PostgreSQL migration of queries indicates that it is a tedious and complicated procedure that can take a lot of efforts when doing it manually. It is quite reasonable to use dedicated automation tools for this purpose. 

One of such tool is Oracle to PostgreSQL converter developed by Intelligent Converters. This is a software company working in database migration field for more than 15 years. Their tool converts more than 80% of all possible patterns used in SQL SELECT-queries. Oracle to PostgreSQL converter also handles migration of table definitions, indexes, constraints and data.