Customizing GoldenGate processing using SQLEXEC and GETVAL

Let us see how we can use the SQLEXEC parameter of GoldenGate to execute both an SQL query as well as a stored procedure and then using the @GETVAL function, we can populate a column in the target database which is not present on the source table.

Using a simple example to illustrate this, let us suppose we have two tables – one a lookup table called COUNTRY_CODES which has the country_name and country_id columns and another table called CUSTOMERS which only has the country_id column.

We would like to customize the GoldenGate processing and also display the country_name along with the country_id in the CUSTOMERS table itself on the target database.

Let us look at two ways of doing this – one using a SQL query and the other case where we use a stored procedure and pass a parameter to the stored procedure.

Case 1 – using SQL Query

Here we will use a SQL statement to obtain the value for the column COUNTRY_NAME in the CUSTOMERS table on the target database.

This is our Extract parameter file:

EXTRACT gavinext
USERID idit_prd, PASSWORD idit_prd
RMTHOST indb02, MGRPORT 7809
RMTTRAIL ./dirdat/xx
TABLE idit_prd.customers;

This is the Replicat parameter file:

REPLICAT gavinrep
SETENV (NLS_LANG=”AMERICAN_AMERICA.WE8ISO8859P1″)
SETENV (ORACLE_SID=GGDB2)
ASSUMETARGETDEFS
USERID idit_prd,PASSWORD idit_prd
MAP idit_prd.customers, TARGET idit_prd.customers, &
SQLEXEC (ID lookup, &
QUERY “select country_name cname from country_code where country_id =:v_country_id”,&
PARAMS (v_country_id = country_id)),&
COLMAP (USEDEFAULTS, country_name = @GETVAL (lookup.cname) );

Case 2 – Using a database stored procedure

We have a procedure called GET_COUNTRY which accepts the COUNTRY_ID value as a parameter and returns the COUNTRY_NAME as an OUT parameter.

This is the source code nof the database procedure, GET_COUNTRY:

create or replace procedure get_country
(v_country_id IN number, v_country_name OUT varchar2 )
is
begin
select country_name into v_country_name from country_code where country_id= v_country_id;
end;
/

We we call this procedure from GoldenGate using the SQLEXEC parameter in the Replicat parameter file and we see how by passing the parameter to the variable v_country_id and using the @GETVAL function, the COUNTRY_NAME column is being populated in the target database.

REPLICAT gavinrep
SETENV (NLS_LANG=”AMERICAN_AMERICA.WE8ISO8859P1″)
SETENV (ORACLE_SID=GGDB2)
ASSUMETARGETDEFS
USERID idit_prd,PASSWORD idit_prd
MAP idit_prd.customers, TARGET idit_prd.customers, &
SQLEXEC (SPNAME GET_COUNTRY, &
PARAMS (v_country_id = country_id)),&
COLMAP (USEDEFAULTS, country_name = @getval (GET_COUNTRY.V_COUNTRY_NAME) );