"Sorry, you're just not my type." - Change Data Types Between Databases

“Sorry, you're just not my type.”

The one sentence, no one ever wants to hear, after the first date with someone you thought you had a made a great connection with. The sudden empty feeling in your stomach and the emotional letdown from what could have been leaves you on wondering what happened.

Believe it or not the same can be true in the world of databases. With the increasing number of different types of databases that are showing up every year, you are bound to run into a situation where there are data type conflicts and incompatibilities.

When using SharePlex this usually is not a problem since most of the time the data types match from the source to the target, but there will be the few times where the the data types just won't map straight across during replication and some creativity needs to be thought out the accomplish this.

Case in point. I had this happen a short time ago when working with a client. We were working on a migration going from Oracle to a PostgreSQL database and the majority of their data was in XML.  In this case a direct map of the XML from the source to the target was not a supported data type for SharePlex. This was a critical issue for the migration and we needed to come up with a way to get the XML data from Oracle to PostgreSQL. Thanks to some great forward thinking from our Development team, we came up with a plan to migrate the XML data from the Oracle database to a TEXT column in the PostgreSQL database. We were able to successfully confirm that SharePlex is able to move XML from the source to a TEXT in the target. Even better, was when we presented this to the client they were ecstatic about it. It seems in the past they had already been wondering what they wanted to do with all their XML and storing/archiving it. By converting it to the TEXT data type we were able to meet their needs and move forward with the migration.

The short video below demonstrates the moving of XML from an Oracle Source to a PostgreSQL target and into a TEXT data type.