Blog: Converting Oracle system functions to PostgreSQL with AWS SCT extension pack

Migrating Oracle databases to PostgreSQL on AWS, you often face challenges with converting system functions. Oracle has a rich set of built-in functions, and PostgreSQL doesn’t provide users with all of those.

To support and simplify Oracle to PostgreSQL on AWS migrations, AWS Schema Conversion Tool provides users with an extension pack. After applying it to your Amazon Aurora PostgreSQL or Amazon RDS for PostgreSQL database, you may use the functions, which emulate the behavior of Oracle’s system functions.

You can find more information on the extension packs for OLTP databases on our blog. In this blog post, we will focus on the practical examples of using the extension pack functions. Watch the following video to see how the extension pack helps emulate Oracle’s TRUNC and TO_CHAR functions for dates.

Video script

Background on the conversion of the TRUNC function

Let us consider the following example from the official Oracle documentation.

SELECT d "Original Date",  
   trunc(d) "Nearest Day, Time Removed",  
   trunc(d, 'ww') "Nearest Week",
   trunc(d, 'iw') "Start of Week",  
   trunc(d, 'mm') "Start of Month",  
   trunc(d, 'year') "Start of Year"  
FROM dates;

In Oracle, you can use the TRUNC function to truncate the date to the nearest week, to the start of the week, month, or year.

After migrating your database to Amazon Aurora PostgreSQL, you may use the date_trunc function. However, it does not support the wide range of parameters as the TRUNC function in Oracle. Also, to call this function in Amazon Aurora PostgreSQL, you need to use a format different from the one you use in Oracle. Therefore, you will need to change the application logic to match this new format.

You know that the modernization of the application code is a complex process. Because it requires a lot of effort, you may want to find a simple yet proven workaround. The function from the extension pack provides for using the same format of the function call. Needless to say that it delivers the same result too. And what is even more important — you will not need to update your app code.

Background on the conversion of the TO_CHAR function

In Oracle, the TO_CHAR function converts a datetime value to a VARCHAR2 string according to the specified format. The built-in PostgreSQL function supports only some of the formats available in Oracle.

Therefore, you will need either to take care of these limitations in your application code or use the relevant function from the extension pack. In this case, your application code will not change, as the function call will look the same as before.

Applying the extension pack to your PostgreSQL database

For Oracle DBAs, who got used to leveraging system functions, it may be hard to deal with the limited set of functions available out of the box in PostgreSQL.

So, you may want to use AWS SCT to apply the extension pack to your Amazon Aurora PostgreSQL or Amazon RDS for PostgreSQL database. You can do that without converting your source Oracle database. This will enrich the set of available functions in free and open-source PostgreSQL. To install the extension pack into your database, you need to create a new Oracle to PostgreSQL conversion project in AWS SCT. You don’t need to connect to the source database — establishing a connection to your Amazon Aurora PostgreSQL database will be enough. Then right-click on the database metadata tree and choose the Apply Extension Pack option from the pop-up menu. However, if you use AWS SCT for the conversion of your database code, the tool will apply the extension pack automatically.

Simplify your cloud adoption with DB Best

Managing customers’ data and applications for almost 20 years, DB Best obtained vast experience in cloud migrations. Leveraging the likes of AWS Schema Conversion Tool or AWS Database Migration Service, our team of certified experts can guarantee you a smooth sail to the Amazon cloud. Check out our AWS Schema Conversion Tool Jumpstart offer to get you up and running fast in the cloud.

Share this...
Share on Facebook
Tweet about this on Twitter
Share on LinkedIn