Retrieving Before and After Values

For update and delete operations, it can be useful to retrieve the BEFORE values of the source columns (the values before the update occurred). For inserts, all column values are considered AFTER images.

These values are stored in the trail and can be used in filters and column mappings. For example, you can:

  • Retrieve the before image of a row as part of a column-mapping specification in an exceptions MAP statement, and map those values to an exceptions table for use in testing or troubleshooting conflict resolution routines.

  • Perform delta calculations. For example, if a table has a Balance column, you can calculate the net result of a particular transaction by subtracting the original balance from the new balance, as in the following example: 

    MAP "owner"."src", TARGET "owner"."targ",
COLMAP (PK1 = PK1, delta = balance – @BEFORE (balance));

    Note:

    The previous example indicates a case-sensitive database such as Oracle. The table names are in quote marks to reflect case-sensitivity.

To Reference the Before Value

  1. Use the @BEFORE column conversion function with the name of the column for which you want a before value, as follows: 

    @BEFORE (column_name)

  2. Use the GETUPDATEBEFORES parameter in the Extract parameter file to capture before images from the transaction record, or use it in the Replicat parameter file to use the before image in a column mapping or filter. If using the Conflict Resolution and Detection (CDR) feature, you can use the GETBEFORECOLS option of TABLE. To use these parameters, all columns must be present in the transaction log. If the database only logs the values of columns that changed, using the @BEFORE function may result in a "column missing" condition and the column map is executed as if the column were not in the record. See Ensuring Data Availability for Filters to ensure that column values are available.

    Oracle GoldenGate also provides the @AFTER function to retrieve after values when needed for filtering, for use in conversion functions, or other purposes. For more information about @BEFORE and @AFTER, see Parameters and Functions Reference for Oracle GoldenGate.