Migrate PL/SQL from Oracle to PostgreSQL
One of the most complicated stages of database migration is conversion of database logic according to the syntax of the target DBMS. This article explains differences between Oracle and PostgreSQL procedure languages since migration between these two database management systems is extremely popular. Although syntaxes of procedure languages for the two DBMS are basically similar, there are few important differences to remember during migration.
- If there are variable and column with the same name used in a SQL query, Oracle treats it as a column name and PostgreSQL – as a variable name. The good practice of writing procedure language code is to avoid such ambiguities.
- Oracle uses packages to organize procedures into groups by similar functionality. PostgreSQL uses schema for the same purpose. Package-level variables of Oracle can be emulated using temporary service table in PostgreSQL.
- PostgreSQL considers the function body as a string, so it is necessary to enclose it in dollar quotes.
- Oracle provides operator (+)that is equivalent of LEFT JOIN.This feature is not supported by PostgreSQL and must be replaced by the standard ‘LEFT JOIN’.
- PostgreSQL requires ‘LANGUAGE’ specification at the end of function since PL/PgSQL is not the only possible language there.
- All local variables must be declared using ‘DECLARE’ keyword right before ‘BEGIN’
- Oracle queries with limitation by number of returned rows like where rownum<= 1000 are replaced by limit 1000 in PostgreSQL
- Unlike Oracle, PostgreSQL does not allow ‘COMMIT’ statement inside function.
- Oracle specific keywords must be removed from the code:
- DEFAULT
- FORCE / NO FORCE
- WITH CHECK OPTION
- WITH OBJECT IDENTIFIER
- WITH READ ONLY
- UNDER
- Built-in functions of Oracle must be converted according to these table:
| Oracle | PostgreSQL |
| CURTIME() | LOCALTIME(0) |
| DAY($a) or DAYOFMONTH($a) | EXTRACT(day from date($a))::integer |
| DateAdd($Date, $Format, $Days, $Months, $Years) | $Date + cast(‘$Days day’ as interval) + cast(‘$Months months’ as interval) + cast(‘$Years years’ as interval) |
| HOUR($a) | EXTRACT(hour from $a) |
| INSTR($str1, $str2) | POSITION($str2 in $str1) |
| LCASE | LOWER |
| LOCATE($str1,$str2) | POSITION($str1 in $str2) |
| MINUTE($a) | EXTRACT(minute from $a) |
| MONTH($a) | EXTRACT(month from $a) |
| NVL($a, $replace_with) | COALESCE($a, replace_with) |
| RAND | RANDOM |
| REGEXP_LIKE($string, $pattern) | $string LIKE $pattern |
| SECOND($a) | EXTRACT(second from $a) |
| UCASE | UPPER |
| WEEK($a) | EXTRACT(weekfrom $a) |
| YEAR($a) | EXTRACT(year from date($a)) |
The guide above illustrates that migration of PL/SQL code into PostgreSQL format is a complicated procedure requiring a lot of efforts. There are special software tools that can partially automate migration. One of those tools is Oracle to PostgreSQL code converter developed by Intelligent Converters.It converts stored procedures, functions and triggers with respect to distinguished built-in functions in the source and target database management systems.The converter supports command line arguments to script and schedule the migration procedure.
