Using the GoldgenGate column conversion functions – STRCAT and STREXT

In one of my earlier posts, I had showed some examples of using the CASE and EVAL column conversion functions available in GoldenGate.

Let us today take a look at two other functions STRCAT and STREXT.

STRCAT basically enables us to concatenate two strings or two character columns.

The STREXT function on the other hand is used to extract a portion of a string.

For example in our source database we have a table MYPHONE with the following structure:

SQL> desc myphone
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 AREA_CODE                                          NUMBER
 PHONE_NO                                           NUMBER

In the target we have the same table MYPHONE with a different structure:

SQL> desc myphone
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PHONE_NO                                           VARCHAR2(30)

So in the target database, the PHONE_NO column includes both the area code as well as the phone number with a ‘-‘ character as well between both the values, while in the source database the table has two columns – one for the area code and one for the phone number.

We will use SRTCAT function in this case.

Remember that since the source and target tables differ in structure, we need to run the DEFGEN utility in the source GoldenGate environment to generate the definitions file and then copy this across to the target GoldenGate environment..

These are the contents of the extract parameter file:

extract ext
userid ggs_owner, password ggs_owner
rmthost venom , mgrport 7809
rmttrail ./dirdat/gg
table ggs_owner.myphone;

These are the contents of the replicat parameter file – note how the STRCAT function is being used here.

REPLICAT rep
sourcedefs /export/home/oracle/gg/dirsql/myphone.sql
USERID ggs_owner, PASSWORD ggs_owner
MAP ggs_owner.myphone, TARGET ggs_owner.myphone,
COLMAP (phone_no=@STRCAT("0",area_code,"-",phone_no));

We now start the extract and replicat processes and insert some records in the source table.

The source table now has the following rows:

SQL> select * from myphone;

 AREA_CODE   PHONE_NO
---------- ----------
         8   92471136
         4   17213124
         2     818188

Let us see how these rows have been transformed on the target database.

SQL> select * from myphone;

PHONE_NO
------------------------------
08-92471136
04-17213124
02-818188

We can extend this example and use both STRCAT and STREXT together to achieve the data transformation that we desire.

For example in our source MYSSID table, we have data like this:

SQL> select * from myssid;

      SSID
----------
 123456787
 123456788
 123456789

But on the target database, in the same table the data has been displayed like this:

SQL> select * from myssid;

SSID
--------------------
123-456-787
123-456-788
123-456-789

So how did we achieve this?

Have a look at the replicat parameter file and note how we have used first the STREXT function to extract 3 characters at a time – the first 3, then the next 3 and finally the last 3 and then used STRCAT to concatenate the result of the STREXT functions together with a ‘-‘ character acting as the string separator.

REPLICAT rep
sourcedefs /export/home/oracle/gg/dirsql/myssid.sql
USERID ggs_owner, PASSWORD ggs_owner
MAP ggs_owner.myssid, TARGET ggs_owner.myssid,
COLMAP (ssid=@STRCAT(@STREXT(ssid,1,3),"-",@STREXT(ssid,4,6),"-",@STREXT(ssid,7,9) ));