Let us have a look at some GoldenGate column conversion functions – CASE and EVAL
To illustrate this example, we have a source and target table called EMPSAL and the structure of the tables in both databases is slightly different.
In the source table we have the JOB column and in the target database we have a TAX_BRACKET column.
In the first example, we will use CASE function to populate the target database TAX_BRACKET column based on the value for the JOB column in the source database.
In the second example, we will use the EVAL function to populate the target database TAX_BRACKET column based on a particular range of values for the SAL column in the source table.
Remember that since the table structure in source and target are different, we need to run the DEFGEN utility to generate a definitions file which we will then copy to the target GoldenGate environment and instead of using the ASSUMEDEFS parameter in the replicate parameter file, we use the SOURCEDEFS parameter instead to indicate the location of the definitions file which we have generated and copied over from the source GoldenGate environment.
Source
SQL> desc empsal Name Null? Type ----------------------------------------- -------- ---------------------------- ENAME VARCHAR2(20) SAL NUMBER JOB VARCHAR2(12)
Target
SQL> desc empsal Name Null? Type ----------------------------------------- -------- ---------------------------- ENAME VARCHAR2(20) SAL NUMBER TAX_BRACKET VARCHAR2(10)
We now create the definitions file for the EMPSAL table and copy the generated definitions file to the target GoldenGate environment.
GGSCI (sunos1) 4> edit params defgen "/export/home/oracle/gg/dirprm/defgen.prm" 3 lines, 97 characters defsfile ./dirsql/empsal.sql userid ggs_owner password ggs_owner table ggs_owner.empsal; sunos1:/export/home/oracle/gg $ ./defgen paramfile /export/home/oracle/gg/dirprm/defgen.prm sunos1:/export/home/oracle/gg/dirsql $ scp -rp empsal.sql oracle@sunos2:/export/home/oracle/gg/dirsql
Let us have a look at the Extract parameter file – quite normal and straight forward here.
extract ext userid ggs_owner, password ggs_owner rmthost sunos2 , mgrport 7809 rmttrail ./dirdat/gg table ggs_owner.empsal;
On the target, these are the contents of the replicat parameter file. Note the COLPMAP clause. Since both the tables have common columns ENAME and SAL, we do not have to explicitly map them and we can just specify USEDEFAULTS.
The TAX_BRACKET column on the target table is being populated using the CASE function which evaluates the values for the JOB column in the source table.
If the value for JOB is ‘OPERATOR’, then the TAX_BRACKET column is assigned the value ‘LOW’. If it is ‘DBA’ then the TAX_BRACKET column is assigned the value ‘MID’ and if the value is ‘MANAGER’, then the TAX_BRACKET column is assigned the value ‘HIGH’. If the value for JOB is none of the values, then a default value of ‘UNKNOWN’ is assigned.
REPLICAT rep sourcedefs /export/home/oracle/gg/dirsql/empsal.sql USERID ggs_owner, PASSWORD ggs_owner MAP ggs_owner.empsal, TARGET ggs_owner.empsal, COLMAP ( usedefaults, tax_bracket=@case(JOB, "OPERATOR","LOW","DBA", "MID","MANAGER","HIGH", "UNKNOWN"));Let us now insert some values into the source table.SQL> insert into empsal values 2 ('JOHN',10000, 'DBA'); 1 row created. SQL> insert into empsal values 2 ('JACK',5000, 'OPERATOR'); 1 row created. SQL> insert into empsal values 2 ('TOM',20000, 'MANAGER'); 1 row created. SQL> insert into empsal values 2 ('MARY', 6000, 'ANALYST'); 1 row created. SQL> commit; Commit complete. SQL> select * from empsal; ENAME SAL JOB -------------------- ---------- ------------ JOHN 10000 DBA JACK 5000 OPERATOR TOM 20000 MANAGER MARY 6000 ANALYSTLet us now see how the data has been replicated and how the transformation has taken place on the target.
SQL> select * from empsal; ENAME SAL TAX_BRACKE -------------------- ---------- ---------- JOHN 10000 MID JACK 5000 LOW TOM 20000 HIGH MARY 6000 UNKNOWNLet us now truncate both the tables and see the next example using EVAL instead of CASE.
We stop both the extract and replicat processes and will now change the replicat parameter file on the target to include the EVAL function.
So this is how our replicat parameter file will look like.
REPLICAT rep sourcedefs /export/home/oracle/gg/dirsql/empsal.sql USERID ggs_owner, PASSWORD ggs_owner MAP ggs_owner.empsal, TARGET ggs_owner.empsal, COLMAP ( usedefaults, & tax_bracket=@eval(sal < 10000,"LOW", sal >= 20000 , "HIGH",sal >= 10000, "MID"));Start the extract and replicat processes again and let us now insert the same set of values in the source table as we did before and see how they are now replicated and transformed on the target.
SQL> select * from empsal; ENAME SAL TAX_BRACKE -------------------- ---------- ---------- JOHN 10000 MID JACK 5000 LOW TOM 20000 HIGH MARY 6000 LOWFor those rows where the SAL value was less than 10000, a value of 'LOW' was assigned to the TAX_BRACKET column and if the value was equal to or greater than 10000 (but less than 20000), a value of 'MID' is assigned and all SAL values above 20000 are assigned 'HIGH'.
Have a read of the all the diferent column conversion functions mentioned in the GoldenGate reference guide and there are quite a few. Let me know if you found any others which are useful and kindly share your experience with the community!
