Stored procedures, functions, triggers and views are used to implement business logic inside databases. That is why one of the most important parts of the overall database migration is to convert SQL. However, it is not an easy task since it requires deep knowledge in database administration and development for both DBMS. This whitepaper contains some tips to help database administrators convert SQL from Oracle to PostgreSQL.

First important difference in SQL syntax of Oracle and PostgreSQL is that Oracle uses packages to organize functions into semantic groups, whereas PostgreSQL uses schemas for similar purpose. Additionally, there are no package-level variables in PostgreSQL, but they can be emulated as data of a temporary service table. To convert SQL from Oracle to PostgreSQL in the smooth and safe way, it is important to research best practices to convert SQL code and to understand what kind of task the respective procedure, function, trigger or view solves, instead of simply converting the syntax.

It is also important to note that there is a difference in how variables are declared in Oracle stored procedures and PostgreSQL functions. In Oracle, variables can be declared anywhere, while in PostgreSQL, local variables must be declared at the beginning of the function. This syntax difference requires a change in habit, and careful attention must be paid to ensure that the new function operates correctly.

In addition to syntax differences, it is important to have a good understanding of both Oracle and PostgreSQL before beginning the migration process. If your application uses proprietary features of Oracle (system packages or built-in functions), you will need to rewrite those parts of the application that use Oracle. It is essential to address any differences in syntax and functionality between the two systems to ensure a smooth transition.

To make the migration process easier, it is possible to use special tools that can convert SQL from Oracle to PostgreSQL and partially automate the migration of stored procedures, functions, triggers, and views. One such tool is the Oracle to PostgreSQL Code Converter developed by Intelligent Converters. This tool can convert SQL code of stored procedures, functions, triggers, and views properly mapping Oracle built-in functions into PostgreSQL equivalents. Predefined Oracle types are mapped into PostgreSQL, and reserved words and identifiers are handled intelligently. Source code can be extracted directly from the database or from a PL/SQL script file.

Although the Oracle to PostgreSQL Code Converter automates the migration of basic syntax patterns and constructions, some manual post-processing of the output code may be required, especially for large and complicated fragments of the source code. Therefore, it is essential to test the migrated code thoroughly to ensure that it operates correctly.

In conclusion, it is a complex task to convert SQL code of stored procedures, functions, triggers and views from Oracle to PostgreSQL that requires careful planning and execution. It is important to convert SQL from Oracle to PostgreSQL with respect to the differences in syntax and functionality between the two DBMS. Additionally, it may be helpful to use special tools such as the Oracle to PostgreSQL Code Converter to partially automate the migration process.