Adding custom fields to a replicated record using SQLEXEC

In this recipe we will look into how we can use SQLEXEC to populate a column using an SQL statement in the GoldenGate replication.

Getting ready

For this recipe we will use the EMP table that is delivered in the SCOTT schema with Oracle binaries. In the target environment, the EMP table has an extra column called AVG_SAL which will be the average salary of an employee's department calculated using SQL ran by SQLEXEC.

Source EMP Table	Target EMP Table
EMPNO	EMPNO
ENAME	ENAME
JOB	JOB
MGR	MGR
HIREDATE	HIREDATE
SAL	SAL
COMM	COMM
DEPTNO	DEPTNO
	AVG_SAL

How to do it...

We will follow the following steps to demonstrate the use of the SQLEXEC command.

Run the following in the source environment:

1. Create an Extract process parameter file as follows:

   EXTRACT EGGTEST1
   USERID GGATE_ADMIN@DBORATEST, PASSWORD GGATE_ADMIN
   EXTTRAIL /u01/app/ggate/dirdat/st
   TABLE scott.EMP;

2. The Datapump process parameter file should look as follows:

   EXTRACT...