Oracle Golden Gate Functions

Consider below source and target tables

Client Requirement

The client requirements are below:

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

On proddb:
==========
GGSCI> add extract PFOXGNE, integrated tranlog, begin now
GGSCI> register extract PFOXGNE database
GGSCI> add exttrail ./dirdat/tn, extract PFOXGNE
GGSCI (ggprod) 3> edit param PFOXGNE
EXTRACT PFOXGNE
USERID ogg, PASSWORD ogg
EXTTRAIL ./dirdat/tn
TABLE FOX.GG_FU

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,
[email protected](@STRCAT(FIRST_NAME,' ',LAST_NAME)),
SALARY=SALARY,
[email protected](0.1*SALARY),
[email protected](HOURLY_PRICE > 75, 'SENIOR', HOURLY_RATE > 50, 'JUNIOR', 'FRESHER'),
[email protected](@COLTEST(DEPT_NAME, NULL, MISSING),'IT',DEPT_NAME) ,
[email protected](PHONE),
[email protected](),
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;

Was this article helpful?

Related Articles

Leave a Comment