In this recipe we will look into how we can use SQLEXEC to populate a column using an SQL statement in the GoldenGate replication.
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 |
We will follow the following steps to demonstrate the use of the SQLEXEC command.
Run the following in the source environment:
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;
The Datapump process parameter file should look as follows:
EXTRACT...