Large South American Bank migrates from Oracle to PostgreSQL
Customer Goal
Bull South America was contracted by the bank to modernize their IT structure, including migrating to open source technology as part of a general governmental initiative. Part of this was converting a very large database with tens of thousands of lines of stored procedures Oracle to PostgreSQL.
Oracle Stored Procedure Migration Key Requirement
Bull's Database Migration Team was contracted to convert a large Oracle to PostgreSQL. The complexity of the conversion centered largely on the need to translate approximately 50,000 lines of Oracle PL/SQL. This Oracle Stored Procedure code ranged in complexity from simple table interfaces (e.g., INSERT, UPDATE, DELETE and SELECT interfaces) to complex packages using package global arrays. A manual translation of the code was out of the question. The customer, truly following a path to non-proprietary open source software, preferred not to use software that provided a compatibility layer on top of PostgreSQL.
A Better Migration Tool was Required and Developed by Bull
Bull quickly determined that the existing tools, including ora2pg, were too simplistic to be of any real use. We did take advantage of the orafce functionality, but, orafce resolved only a fraction of our challenges. We needed something new that would do much more than a line by line translation. We needed a tool that could evaluate the environment and generate code required to support features such as:
- Global variables
- Global package records and
- Global indexed arrays.
We needed a tool that could translate complex SQL from one form to another. And we needed utility functions that would build supporting data structures that could provide information to the translator regarding function interface definitions and table definitions.
So Bull developed an Oracle Stored Procedure Translation Tool and supporting utility functions. The translations done by Bull's tool include:
- CONNECT BY
- OUTER JOIN
- BULK COLLECT
- Cursors
- Client function interface rewrites including casting as required
- Package Global variables
- Package Global records
- Package Global binary indexed arrays
- Exceptions
- SQLCODE Oracle to PostgreSQL SQLSTATE translation functionality
- Many others
Oracle Stored Procedure Tool made the Migration Possible
Without this tool, the migration of our customer’s functions, procedures, packages, and triggers would have been cost prohibitive and practically impossible to code and to test. Our Oracle Stored Procedure Translation Tool translates the PL/SQL to PL/pgSQL and creates the correct interface code for the calling application. This allowed us to reduce the time for the translation from 6 months (which was unacceptable to the customer) to 1 month. Because the translation was done automatically with a tool, the quality of the translated code was dramatically improved, and testing went much faster.
