One of our ISV customers wanted to get off their expensive Oracle licenses and develop a cloud-based SaaS solution. They looked to us to help their development team migrate their database applications to Amazon RDS for PostgreSQL . The DB Best migration team utilized AWS Schema Conversion Tool (SCT) to automate the conversion of customer’s database and application code. However, the original system utilized a lot of specific Oracle features, for example, synonyms. SCT does not convert all types of Oracle synonyms to PostgreSQL correctly. So, the DB Best team came up with a variety of state-of-art workarounds to accomplish this database migration task.
Read the following blog post to discover how we approached the most complex code conversion steps.
Background on Oracle synonyms
A synonym in Oracle Database is basically an alias which links to the database objects. You can then use this alias in queries. The trick is that when you change the name of an object, all you need to do is update the synonym definition. The database application will continue working as before without any code updates.
Oracle synonyms are very useful in both distributed and nondistributed database environments because they hide the identity of the underlying object, including its location in a distributed system. You can create synonyms for the following types of object:
- Table or object table
- View or object view
- Stored procedure or function
- Sequence
- Package
- Materialized view
- Java class schema object
- User-defined object type
- Synonym
Automated conversion of Oracle synonyms to PostgreSQL
PostgreSQL does not support synonyms out of the box. For older versions of PostgreSQL, you may consider using the following patch to use synonyms. In our project, we migrated customer’s databases to AWS RDS for PostgreSQL. You can’t use any patches for PostgreSQL on AWS RDS, so, we had to find different solutions.
Our team utilized AWS Schema Conversion Tool (SCT) to convert the source Oracle database to PostgreSQL. SCT creates wrapping objects for the synonyms for tables, views, procedures, and functions. The picture below shows that SCT creates a view for Oracle synonym for a table.
This approach allows for minimizing the code changes in the database application remediation phase. This is an important step of DB Best’s 12-step migration methodology and we will talk about it at the end of the blog post. Also, this way you can achieve similar functionality in PostgreSQL as you have in Oracle with synonyms.
The table below shows how SCT converts Oracle synonyms.
| Synonym for an Oracle database object | Wrapping object on PostgreSQL |
| Table or view | View |
| Function | Function |
| Procedure | Function |
However, SCT generates an action item for synonyms for sequences, packages, user-defined object types, Java class schema objects, and other synonyms. The picture below shows the action item you get in AWS SCT when you try to convert an Oracle synonym for a sequence.
In our project, the source database code included quite a lot of synonyms for sequences and packages. So, we needed to create a workaround to emulate the behavior of Oracle synonyms for sequences and packages in PostgreSQL.
Convert Oracle synonyms for sequences to PostgreSQL
A sequence in Oracle Database is a database object that generates unique integers on request from any of the database users. You may want to use sequences when you add records to your tables to generate a unique ID for each record.
PostgreSQL also supports sequences, and SCT easily converts Oracle sequences to PostgreSQL. However, the issues occur when you try to convert Oracle synonym for a sequence. And in this customer’s project, the database application always calls sequences via the synonym.
This means that after converting the source database code with AWS Schema Conversion Tool, we discovered a huge number of action items related to Oracle synonyms for sequences. So, we created a script which automatically generates the wrapping function for a given synonym on PostgreSQL. Basically, this function calls the previously converted sequence on PostgreSQL. That’s why we need to update the database application code to match the new format of calling the wrapping function on PostgeSQL. You can find more details on this in the last chapter of our blog post.
Synonym for sequence conversion example
Now, let’s consider a conversion example of an Oracle synonym for the following very simple sequence.
1 2 3 4 5 6 7 8 9 | -- An example of the Oracle sequence CREATE SEQUENCE sample_sequence START WITH 0 INCREMENT BY 1 NOCACHE NOCYCLE; -- An example of the synonym for this sequence CREATE PUBLIC SYNONYM sample_sequence_synonym FOR sample_sequence; |
So, we will use the following PostgreSQL function to emulate Oracle synonym to the above-mentioned sequence.
1 2 3 4 | -- PostgreSQL wrapping function to emulate Oracle synonym CREATE OR REPLACE FUNCTION public_synonyms.sample_sequence_synonym() RETURNS BIGINT LANGUAGE SQL AS $$ SELECT NEXTVAL('schema1.sample_sequence'); $$; |
We discovered a pattern in synonym names in the source Oracle database. These names were just the same as the names of underlying sequences. And then we created this short script that helps automate the generation of wrapping functions.
1 2 3 4 5 6 7 8 9 10 11 12 | -- We execute this code on the Oracle database server -- to automatically generate PostgreSQL wrapping functions SELECT FORMAT( 'CREATE OR REPLACE FUNCTION public_synonyms.%s() RETURNS BIGINT LANGUAGE SQL AS $$ SELECT NEXTVAL(''%s.%s''); $$;' , sequence_name , sequence_schema , sequence_name) FROM information_schema.sequences WHERE sequence_schema IN ('saturn', 'general', 'wtailor'); |
So, we run this script to generate the wrapping functions for all synonyms for sequences that we have in the source database. Then we upload this automatically code to the PostgreSQL database.
Convert Oracle synonyms for packages to PostgreSQL
A package in Oracle Database is a collection of related procedures, functions, and other program objects. Basically, a package includes the list of included database objects and their PL/SQL code. Actually, a synonym for an Oracle package points to this list of database objects.
AWS SCT cannot convert an Oracle package to PostgreSQL. So, we created a workaround that allows for minimizing of manual code conversion. In general, we run a script that connects to Oracle and extracts all the synonyms for packages. The script stores the extracted data in the text file. Then we run another script which automatically converts the extracted statements from the file to PostgreSQL. Then the script uploads converted the code to the target database. You need to run this second script after converting the source code with the AWS Schema Conversion Tool. That’s because the script creates the wrapping functions in PostgreSQL for Oracle functions from the source package. So, the script considers that the related functions already exist in the target environment.
Synonym for package conversion example
Let’s consider an example of an Oracle synonym for the following package.
1 2 3 4 5 6 7 8 9 10 11 | -- An example of the Oracle package CREATE OR REPLACE PACKAGE ORACLE_PACKAGE AS TYPE ora_type IS RECORD ( monthname VARCHAR2(512), amount NUMBER); TYPE t_ora_type IS TABLE OF ora_type; v_pi CONSTANT NUMBER(8) :=3.14; minimum_balance CONSTANT REAL := 0.00; maximum_balance CONSTANT REAL := 10.00; CURSOR c_cur IS SELECT 'SUNDAY' AS "day" FROM dual; END ORACLE_PACKAGE; |
First, we need to run AWS SCT to convert the source function. After that, we will have to convert the synonym for the package using our in-house scripts. These scripts will create a new PostgreSQL function for all database objects from this package. For example, for the constant definition, our script creates a function that looks like:
1 2 3 4 5 6 7 8 9 10 11 | -- PostgreSQL wrapping function to emulate Oracle constant CREATE OR REPLACE FUNCTION schema_name."s_oracle_package$schema_name$oracle_package$maximum_balance$c"() RETURNS DOUBLE PRECISION LANGUAGE plpgsql AS $function$ BEGIN RETURN 10.00; END; $function$ ; |
And here’s how the wrapping function for a cursor will look like:
1 2 3 4 5 6 7 8 9 10 | -- PostgreSQL wrapping function to emulate Oracle cursor CREATE OR REPLACE FUNCTION schema_name."s_oracle_package$oracle_package$c_cur"() RETURNS refcursor LANGUAGE plpgsql AS $function$ BEGIN RETURN schema_name.oracle_package$c_cur$o(); END; $function$ ; |
As you can see, this function simply calls the c_cur function, previously converted by AWS SCT.
The conversion script names the database objects as follows: synonym_name$package_name$function_name$return_type. By the way, SCT uses the same approach and names the functions using the following template: package_name$function_name.
Application remediation
Our database migration experience shows that the application update step requires no less than 25% of the overall efforts. However, the conversion approach we utilized in that project, allowed us to save a significant amount of time needed for the application modifications.
In addition to that, the customer’s original application utilized the Java Hibernate framework. This really helps, because you can set up SQL Dialects in Hibernate. However, in other unification projects that we previously accomplished, we used totally different approaches to match the type of the customer’s applications.
The dialect specifies the type of database currently used. So, the application generates an appropriate type of SQL statements depending on the dialect.
The original customer’s application was connected only to the Oracle Database. As we already mentioned, the app used the synonyms to call Oracle database objects. Here’s how the code that calls a sequence looks like:
1 | SELECT synonym_for_sequence_name.nextval FROM dual; |
So, we created a new custom Oracle and PostgreSQL dialects based on the existing classes of the org.hibernate.dialect. Then we connected the application to the PostgreSQL database running on AWS. After we created the wrapping functions for all Oracle synonyms, we added the new code to match the PostgreSQL dialect. For example, we use the following code to emulate call the call of the Oracle synonym for a sequence.
1 | SELECT synonym_for_sequence_name(); |
Please note that the name of the wrapping function is exactly the same as the name of the Oracle synonym.
A massive project
We utilized these approaches to help our customer move their Oracle workloads to Amazon RDS for PostgreSQL. However, this massive migration project brings much more challenging tasks. We plan to accomplish them within the next couple of years.
At the end of the day, we will set up a continuous automated Oracle code conversion and schema transformation to PostgreSQL.
Of course, we will share our experience and best practices with you. So, stay tuned for our blog updates.
