This whitepaper explores differences between Oracle’s PL/SQL and PostgreSQL PL/pgSQL languages as well as methods of Oracle to Postgres migration.
PL/SQL is similar to PostgreSQL procedure language in many aspects. It is a language requiring that code is block-structured and all variables are declared. Both languages support assignment operations, if-then-else conditionals and loops. At the same time, there are some differences that require special attention during Oracle to Postgres migration:
- When a name used in a SQL command has the potential to represent either a column name in a table or a reference to a variable within a function, PL/SQL will interpret it as a column name, whereas PL/pgSQL will interpret it as a variable name. To prevent such ambiguities, it is advisable to avoid them from arising in the initial setup. However, if required, you can resolve these ambiguities by appropriately specifying the context of the ambiguous name.
- Unlike Oracle, PostgreSQL does not support packages. It can be replaced by schemas to organize your functions into groups. Global package variables can be emulate via temp tables.
- FOR loops that iterate over queries (excluding cursors) exhibit distinct behavior: the target variable(s) must be explicitly declared, unlike PL/SQL, where they are automatically declared. One advantage of this approach is that the variable values remain accessible even after the loop has completed.
- Oracle triggers have the source code as a part or CREATE TRIGGER statement. PostgreSQL requires trigger’s code is arranged as separate procedure that is referenced from CREATE TRIGGER statement.
- Locked table in PostgreSQL function or procedure will not be released until the calling transaction is finished.
- COMMIT is not possible in a PostgreSQL function since it is running within some outer transaction and so COMMIT would terminate the execution. However, it is not necessary anyway, because the lock obtained by the LOCK TABLE will be released when we raise an error.
- The exception names supported by Oracle and PostgreSQL are different. The set of built-in exception in PostgreSQL is much larger but there is no way to declare custom exceptions. The basic statement RAISE exception_name works similarly in both DBMS.
Methods of Oracle to Postgres migration
The first thing coming in mind is manual migration, however this approach requires much time and efforts. Also, there is risk of inaccurate migration or other kind of errors due to human factor. Many database professionals use special software that allow partially automate Oracle to Postgres migration of the database logic. At the same complete automation is really hard task and there are no software solutions on the market that can do this properly.
One of such tools is Oracle to Postgres Code Converter developed by Intelligent Converters software company. It provides the following capabilities:
- Converts common Oracle built-in functions, reserved words and identifiers
- Maps Oracle data types into Postgres equivalents based on the accepted values range
- Extracts source code to migrate from either Oracle database instance or PL/SQL script
- Can store migration settings into a profile
- Supports command line to the on arguments