Convert PLpgSQL to PLSQL - v22-appfactory/appfactory-wiki GitHub Wiki

Port database functions

It will be necessary to take all the functions and stored procedures that are in use in the Postgres DB and rewrite them in Oracles procedural language dialect, PL/SQL.

There are currently over 70 database functions that will need to be ported to PL/SQL. Some are small and simple consisting of a single query or insert statement. Others are much larger and have more complex logic and multiple calls to other functions.

There are some differences between Oracle and Postgres that will affect how the functions can be ported. Currently, database functions are being called using a SELECT statement from the ORM. In Oracle, functions cannot have side effects when called from a SELECT statement so it may be necessary to use a combination of functions and procedures to get the same effect.

This will require a good amount of refactoring of the code in the database.

Oracle also makes use of Packages which are modules that can contain related functions, procedures, variables, and user created data types. Using Packages would make the PL/SQL code more modular but will require more refactoring.

More research will be needed in this area to accurately estimate how much time is needed to complete this process, but this will be one of the largest challenges in switching to Oracle.

TBD: Provide some examples of converted database functions to show differences.