Oracle Golden Gate Functions

Oracle Golden Gate Functions are a set of predefined functions that can be used to manipulate data during replication. These functions can be used to perform a variety of tasks, such as Converting data types, Formatting data, Performing mathematical calculations, Testing conditions and much more.

Consider below source and target tables

Client Requirement

1. If HOURLY_PRICE is above 75 then EMP_LEVEL = Senior, if HOURLY_PRICE is above 50 then EMP_LEVEL= Junior else EMP_LEVEL = fresher

2. Give a 10% bonus to all employees and set target BONUS = 10% of SALARY

3. If the source DEPT_NAME is missing, set it default IT

4. TRNX on target must contain the exact time at which transaction has been executed

5. Combine the FIRST_NAME and LAST_NAME on target as FULL_NAME

6. Convert target FULL_NAME into upper case

Assumptions: Map all other columns with matching names by default

Setup Replication With Golden Gate Functions

Create GG_FUN table on proddb

On Proddb:
==========
Conn fox/fox
CREATE TABLE gg_fun
(
 EMPID NUMBER PRIMARY KEY,
 FIRST_NAME Varchar2(10),
 LAST_NAME Varchar2(10),
 SALARY Number,
 HOURLY_PRICE Number,
 DEPT_NAME Varchar2(4),
 PHONE Number,
 COUNTRY Varchar2(20)
); 

Create GG_FUN_TEST on devdb

On Devdb:
=========
Conn tom/tom
CREATE TABLE gg_fun_test
(
 EMP_ID Number PRIMARY KEY,
 FULL_NAME Varchar2(20),
 SALARY Number,
 BONUS Number,
 EMP_LEVEL Varchar2(20),
 DEPT_NAME Varchar2(4),
 PHONE Varchar2(10),
 TRNX Date,
 COUNTRY Varchar2(20)
); 

Connect to database via Golden Gate and add table level supplemental logging

On proddb:
==========
cd $GG_HOME
./ggsci
GGSCI> dblogin userid ogg, password ogg
Successfully logged into database.
GGSCI> add trandata FOX.GG_FUN
Logging of supplemental redo data enabled for table FOX.GG_FUN.
TRANDATA for scheduling columns has been added on table 'FOX.GG_FUN'.

Create Source Definitions File

First, we need to create a parameter file for DEFGEN utility. You can create it via GG prompt or manually via vi editor

On proddb:
==========
GGSCI> edit params defgen3
DEFSFILE ./dirdef/FoxGG_FUN.def
USERID ogg PASSWORD ogg
TABLE FOX.GG_FUN;

Exit the GG prompt and initiate defgen utility to generate definitions file

On proddb:
==========
cd $GG_HOME
./defgen paramfile ./dirprm/defgen3.prm

Copy the definitions file on target server under $GG_HOME/dirdef location

Create extract on source

Copy the definitions file on target server under $GG_HOME/dirdef location

Create data pump process

GGSCI> Add extract PFOXGND, EXTTRAILSOURCE ./dirdat/tn
GGSCI> Add rmttrail /u01/app/oracle/product/gg/dirdat/rn, extract PFOXGND
GGSCI> edit param PFOXGND
EXTRACT PFOXGND
USERID ogg, PASSWORD ogg
RMTHOST ggdev, MGRPORT 7809
RMTTRAIL /u01/app/oracle/product/gg/dirdat/rn
TABLE FOX.GG_FUN;

Let us create replicat process as per the client requirement on target

On Devdb:
==========
GGSCI> dblogin userid ogg, password ogg
GGSCI> add replicat DTOMGNR, integrated exttrail /u01/app/oracle/product/gg/dirdat/rn
GGSCI> edit param DTOMGNR
REPLICAT DTOMGNR
USERID ogg, PASSWORD ogg
SOURCEDEFS ./dirdef/FoxGG_FUN.def
MAP fox.gg_fun TARGET tom.gg_fun_test, &
COLMAP (EMP_ID=EMPID,
FULL_NAME=@STRUP(@STRCAT(FIRST_NAME,' ',LAST_NAME)),
SALARY=SALARY,
BONUS=@COMPUTE(0.1*SALARY),
EMP_LEVEL=@EVAL(HOURLY_PRICE > 75, 'SENIOR', HOURLY_RATE > 50, 'JUNIOR', 'FRESHER'),
Dept_name=@IF(@COLTEST(DEPT_NAME, NULL, MISSING),'IT',DEPT_NAME) ,
Phone=@NUMSTR(PHONE),
Trnx=@DATENOW(),
Country=country
);

Start the Extract, Pump and Replicat process

On proddb:
==========
GGSCI> start PFOXGNE
GGSCI> start PFOXGND

On devdb:
=========
GGSCI> start DTOMGNR

Let us test our replication

On proddb:
==========
INSERT INTO gg_fun VALUES (1001,'John', 'Doe', 2000, 70, 'HR', 987654321, 'USA');
INSERT INTO gg_fun VALUES (1002,'Daniel', 'David', 5000, 110, NULL, 4444444444, 'UK');
INSERT INTO gg_fun VALUES (1003,'Robin', 'Lee', 1000, 10, 'FIN', 123123212, 'CAN');
COMMIT;