Many organizations are often seeking to migrate from their commercial relational databases, such as Oracle, to open-source databases like PostgreSQL. When migrating from Oracle to PostgreSQL databases, the most time-consuming and complex part is converting database structures, code, and storage objects such as packages, procedures, functions, and triggers.
Converting or modernizing from Oracle to Amazon Relational Database Service (Amazon RDS) for PostgreSQL or Amazon Aurora PostgreSQL-Compatible Edition can become more complex and challenging when the code conversion equivalent native functions aren’t available in PostgreSQL.
For example, Oracle database provides LOB (large objects) data types such as BLOB and CLOB. To operate on these data types, Oracle provides the native DBMS_LOB package, which provides subprograms that can be used to access and interact with specific parts of LOBs or full LOBs.
Oracle databases have specific data types such as varrays (variable-size arrays), which store the number of elements as a single object, and associative arrays (formerly known as PL/SQL tables or index-by tables), which are sets of key-value pairs that are complex to convert because PostgreSQL doesn’t have a native package or function similar to Oracle DBMS_LOB to interact with LOBs and data types similar to Oracle collections such as varrays and associative arrays.
In this post, we explain how you can convert Oracle code objects that contain DBMS_LOB procedures and Oracle collections such as varrays and associative arrays from Oracle to the Amazon RDS for PostgreSQL or Amazon Aurora PostgreSQL equivalent by using AWS SCT, the AWS SCT extension pack, and manual code conversion.
AWS SCT converts your existing database schema from one database engine to another. It can convert relational OLTP schemas or data warehouse schemas. If the schema from your source database can’t be converted automatically, AWS SCT provides guidance on how you can create an equivalent schema in your target RDS database. AWS SCT has additional features for integration with AWS SCT extension packs. The AWS SCT extension pack is an add-on module that emulates functions present in a source database that are required when converting objects to the target database. When you install and apply the AWS SCT extension pack, it creates an aws_oracle_ext schema on the target database. AWS SCT automatically applies the extension pack when installed on the target database. It also shows the code where the extensions are applied to the target database code.
In this solution, we have taken a sample Oracle code that contains the DBMS_LOB package, which writes and appends to LOB object. It also contains data types such as varrays and associative arrays to store the values of variables.
We explain the steps to convert this sample Oracle code to Amazon RDS for PostgreSQL or Amazon Aurora PostgreSQL, which helps you simulate and convert such complex codes when you encounter them in your environment.
The following diagram provides an overview of the solution.
The workflow consists of the following high-level steps:
Use a source Oracle database that contains sample procedures with DBMS_LOB and Oracle collections.
Run AWS SCT to convert the Oracle code, which partially converts the code with errors.
Perform a manual code conversion using the AWS SCT extension pack with the aws_oracle_ext schema.
Apply and deploy the completed converted code to the target RDS for PostgreSQL or Amazon Aurora PostgreSQL database.
Complete the following prerequisite steps:
Create a source Oracle database if you don’t already have one.
Create an Amazon Aurora PostgreSQL cluster or an RDS for PostgreSQL instance if you don’t already have one. For instructions, refer to Create an Aurora PostgreSQL DB cluster or Create a PostgreSQL DB instance, respectively.
Make sure database client software are installed to connect to the source and target databases.
Create the demo_user schema in the source and target databases.
Create database objects in the source Oracle database
Complete the following steps to set up your source table:
Create an empty table:
Create the procedure update_product_details, which inserts the product ID, product name, delivery period, and detailed text of three products into the demo_user.product_tbl table:
This procedure loops through each product name as listed in the varrays, reads the delivery period details for that product as listed in the associate array, uses DBMS_LOB subroutines to formulate the detailed text of the product, and finally inserts it as records into the demo_user.product_tbl table.
In this procedure, the varray is to list the product names in the variable t_prd_names(‘Mobile’,’Tablet’,’Laptop’) and the associative array is to set the delivery time period for the specific product, such as the following:
The dbms_lob.write subroutine writes the product name to the buffer as the first word to the DET_CLOB buffer, starting from the v_offset value (which is 1) and writing up to the max bytes as specified in the v_amount variable:
The dbms_lob.writeappend subroutine appends the text for each product name as the value specified in the DET_TXT variable and formulates the detailed text of the product:
Inserts in the demo_user.product_tbl table consist of the product ID, name, delivery period, and detailed text of the product:
Run the procedure and query the table:
Configure AWS SCT
After you complete the preceding steps in the Oracle source database, the next step is to install the AWS SCT on your local or centralized server. After you install the AWS SCT, download the Oracle and PostgreSQL database drivers and update AWS SCT to connect to the source and target databases.
After a successful connection to both databases, create mapping rules in AWS SCT and apply the aws_oracle_ext extension pack to the target PostgreSQL database. For converting the schema, the AWS SCT tool displays the demo_user schema on the source database in the left pane. Choose (right-click) the schema demo_user on the source and choose Convert schema. AWS SCT will generate the equivalent code in PostgreSQL. You can review the converted procedure and apply the changes to the target database.
During conversion, AWS SCT creates a product_tbl table, an update_product_details$del_sch$c type, and an update_product_details() procedure in the demo_user schema in the target RDS for PostgreSQL or Amazon Aurora PostgreSQL database.
AWS SCT creates the following DDL for the PostgreSQL table and type:
Then it creates a converted package as shown in the following code. Observe the comments that AWS SCT provides.
AWS SCT has identified that it couldn’t convert portions of code when it interprets code involving DBMS_LOB subroutines and varray data types. In response, it annotates the code with error codes 5340 and 5030, flagging them with a Severity Critical message, prompting the need for manual code intervention. Therefore, it’s essential to address these issues and manually convert the code with the appropriate solutions.
AWS SCT conversion issues and solutions
In this section, we address the issues identified by AWS SCT and resolve them manually with the equivalent PostgreSQL code.
For our first issue, AWS SCT didn’t convert the Oracle varray data type as marked with error code 5340 as critical:
Because AWS SCT didn’t convert the varray type, we declare it as array in the manually converted code:
In PostgreSQL, large objects are considered as text and native string operations are performed. It doesn’t have built-in subroutines like dbms_lob.open, so AWS SCT returns the following error:
Because large objects are considered text in PostgreSQL, the specific procedure to open or close LOBs is not needed. However, the AWS SCT extension pack creates equivalent procedures for DBMS_LOB for certain subroutines, like aws_oracle_ext.dbms_lob$write and aws_oracle_ext.dbms_lob$writeappend in its additional schema, which are used in the manually converted code:
Associative array conversion
In this section, we explain how associative arrays are converted by AWS SCT using the extension pack.Associative arrays in Oracle are handled by AWS SCT using aws_oracle_ext.array procedures along with its subroutines.aws_oracle_ext.array$add_fields_to_array is equivalent to a declaration of column and data type in an associative array in Oracle. In Oracle code, it would be as follows:
In the AWS SCT converted code for PostgreSQL, it uses the add_fields_to_array procedure for table definition:
In the AWS SCT converted code for PostgreSQL, it assigns a temporary table:
aws_oracle_ext.array$create_storage_table : SCT would create type objects while converting the code as storage table.
aws_oracle_ext.array$set_value is equivalent to assigning values to associative arrays in Oracle. In Oracle code, it would be as follows:
In the AWS SCT converted code for PostgreSQL, it would be as follows:
aws_oracle_ext.array$get_value is equivalent to getting values for a given index in Oracle associative arrays. In Oracle code, it would be as follows:
v_del_sch(t_prd(i)), where i is the variable to represent product names in associative arrays.
In the manually converted code, we call the aws_oracle_ext.array$get_value procedure to retrieve the value, which is the delivery time period for a given product name such as Mobile:
Manually convert code in the target database
As explained earlier in the issues and solutions section, we apply all the solutions to the procedure code manually and deploy it into the target PostgreSQL environment:
Run the procedure in Amazon RDS for PostgreSQL or Amazon Aurora PostgreSQL:
Compare the result set in the source and target
To test the solution, we must compare the results set in the product_tbl table, both in the source and target, to confirm that they match.
In the source Oracle database, use the following code:
In the target database, use the following code:
We can confirm that the records result set matches.
To avoid ongoing costs, clean up the resources that you created for this post which you no longer need as part of this solution.
In Oracle, use the following code:
In Amazon RDS for PostgreSQL or Amazon Aurora PostgreSQL, use the following code:
In this post, we demonstrated how to convert Oracle database packages and procedures that have DBMS_LOB and varrays or associative arrays to Amazon RDS for PostgreSQL or Amazon Aurora PostgreSQL using the AWS SCT extension pack with minimal changes. Implement this solution in a non-production environment and test it thoroughly so that it matches the expected outcomes.
If you have any questions or comments about this post, use the comments section to post your thoughts.
About the Authors
Chithra Krishnamurthy is a Database Consultant with the Professional Services team, working at Amazon Web Services. She works with enterprise customers to help them achieve their business outcomes by providing technical guidance for Database Migrations to AWS and provide solutions for High Availability and DR strategies.
Sagar Patel is a Senior Database Specialty Architect with the Professional Services team at Amazon Web Services. He works as a database migration specialist to provide technical guidance and help Amazon customers migrate their on-premises databases to AWS.
Read MoreAWS Database Blog