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
For example, in the source Oracle database, we use a function, which truncates the date according to the format that is specified as the second parameter.
We use SQLPlus to run this function in Oracle and see the result.
We then use the AWS Schema Conversion Tool to convert this function to on Amazon Aurora PostgreSQL.
You can see that AWS SCT generates an extension pack and applies it automatically to your PostgreSQL database when you apply the converted code.
Now we launch psql to run the converted function on Amazon Aurora PostgreSQL.
Apparently, the converted function returns the same result.
Let us review the code of this converted function.
And the code of the function from the extension pack, which emulates the behavior of Oracle's trunc function.
Now, let us consider the second example.
We use the TO_CHAR function in Oracle to convert a date to a string.
You can see that using the same function in on Amazon Aurora PostgreSQL leads to an error.
This happens because the TO_CHAR function in Oracle supports more parameters than the same function in PostgreSQL.
After we apply the extension pack, we can use the converted function.
Now we launch psql to run the converted function on Amazon Aurora PostgreSQL.
As expected, it delivers the right result.
Thanks for watching this video. Stay tuned for more updates on AWS SCT features.
Background on the conversion of the TRUNC function
Let us consider the following example from the official Oracle documentation.
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.
