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) ));