Tips and wisdom from 15 years of DBA experience

Monday, November 29, 2010

Transform data in Oracle Golden Gate

In order to transform data in Oracle Golden Gate, you will need to understand the MAP command well. Although there are cases where you will want to execute commands on the source in order to place them into additional payload that will be sent to the target, the vast majority of activity in transformation will be done on the target. 

In the following scenario we place a value into a given column based on the value of an existing column:

MAP schema1.tabA, 
    TARGET schema1.tabA, 
        COLMAP (USEDEFAULTS, colA = @CASE(colB,23,14,24,15,16));

This statement reads "If colB is 23, set colA to 14, else if colB is 24, set colA to 15, in all other cases, set colA to 16");

For more details on the @CASE() function, see the Oracle Golden Gate Reference Guide at www.oracle.com.

Friday, November 12, 2010

How To Put NULL In Target Column With Golden Gate

Golden Gate has a large API which makes most tasks very simple. One item that took me a bit longer to figure out than it should of was how to tell Golden Gate to place NULL into the target column. Here is how to do it:

MAP source.table, TARGET target.table, COLMAP(USEDEFAULTS, null_column = @COLSTAT(NULL));

I have no idea why it took me so long to figure this out.

Followers