Mapping and Manipulating Data
Learn about tasks, functions, commands, and processes used for integrating data between source and target tables.
Parameters that Control Mapping and Data Integration
All data selection, mapping, and manipulation that Oracle GoldenGate performs is accomplished by using one or more options of the TABLE
and MAP
parameters.
-
Use
TABLE
in the Extract parameter file. -
Use
MAP
in the Replicat parameter file.
TABLE
and MAP
specify the database objects that
are affected by the other parameters in the parameter file. See Specifying Object Names in Oracle GoldenGate Input for instructions for specifying object names in these parameters.
Mapping between Dissimilar Databases
Mapping and conversion between tables that have different data structures requires either a source-definitions file, a target-definitions file, or in some cases both. Mapping between dissimilar databases is controlled by the self-describing trails, and mapping is done by column name, regardless of the data type for the source or target column.
If you don't want automatic mapping based on the self-describing trails or want
backward compatibility then you can use SOURCEDEFS
or
TARGETDEFS
.
Mapping and Conversion on NonStop Systems
If you are mapping or converting data from a Windows or UNIX system to a NonStop Enscribe target, the mapping or conversion must be performed on the Windows or UNIX source system. Replicat for NonStop cannot convert three-part or two-part SQL table names and data types to the three-part file names that are used for the Enscribe platform. Extract can format the trail data with Enscribe names and target data types.
Mapping and Conversion on Windows and UNIX Systems
When Oracle GoldenGate is operating only on Windows-based and UNIX-based systems, column mapping and conversion can be performed in the Extract process, or in the Replicat process. To prevent the added overhead of this processing on the Extract process, you can configure the mapping and conversion to be performed on the Replicat process or on an intermediary system.
In the case where there are multiple sources and one target, it might be more efficient to perform the mapping and conversion on the source.
Globalization Considerations when Mapping Data
When planning to map and convert data between databases and platforms, take into consideration what is supported or not supported by Oracle GoldenGate in terms of globalization.
Conversion between Character Sets
Oracle GoldenGate converts between source and target character sets if they are different, so that object names and column data are compared, mapped, and manipulated properly from one database to another. See Supported Character Sets, for a list of supported character sets.
To ensure accurate character representation from one database to another, the following must be true:
-
The character set of the target database must be a superset or equivalent of the character set of the source database. Equivalent means not equal, but having the same set of characters. For example, Shift-JIS and EUC-JP technically are not completely equal, but have the same characters in most cases.
-
If your client applications use different character sets, the database character set must also be a superset or equivalent of the character sets of the client applications.
-
In many databases, including Oracle, it is possible to force a character into a database that is not part of the Character Set. Oracle GoldenGate considers this as an invalid value, and may not map this character correctly when replicating data. For these types of situations you can use the
REPLACEBADCHAR
parameter as described in the Parameters and Functions Reference for Oracle GoldenGate.
In this configuration, every character is represented when converting from a client or source character set to the local database character set.
A Replicat process can support conversion from one source character set to one target character set.
Database Object Names
Oracle GoldenGate processes catalog, schema, table and column names in their native language as determined by the character set encoding of the source and target databases. This support preserves single-byte and multibyte names, symbols, accent characters, and case-sensitivity with locale taken into account where available, at all levels of the database hierarchy.
Column Data
Oracle GoldenGate supports the conversion of column data between character sets when the data is contained in the following column types:
-
Character-type columns:
CHAR/VARCHAR/CLOB
toCHAR/VARCHAR/CLOB
of another character set; andCHAR/VARCHAR/CLOB
to and fromNCHAR/NVARCHAR/NCLOB.
-
Columns that contain string-based numbers and date-time data. Conversions of these columns is performed between z/OS EBCDIC and non-z/OS ASCII data. Conversion is not performed between ASCII and ASCII versions of this data, nor between EBCDIC and EBCDIC versions, because the data are compatible in these cases.
Note:
Oracle GoldenGate supports timestamp data from
0001-01-03 00:00:00
to9999-12-31 23:59:59
. If a timestamp is converted from GMT to local time, these limits also apply to the resulting timestamp. A value of zero month, zero day field, or an all zero date value isn't supported. For example, values such as 0000-00-00 00:00:00, or any date value that includes a zero month or zero day field isn't supported.
Character-set conversion for column data is limited to a direct mapping of a
source column and a target column in the COLMAP
or
USEDEFAULTS
clauses of the Replicat MAP
parameter.
A direct mapping is a name-to-name mapping without the use of a stored procedure or
column-conversion function. Replicat performs the character-set conversion. No
conversion is performed by Extract.
Preservation of Locale
Oracle GoldenGate takes the locale of the database into account when comparing case-insensitive object names. See Supported Locales for a list of supported locales.
Support for Escape Sequences
Oracle GoldenGate supports the use of an escape sequence to represent a string column, literal text, or object name in the parameter file. You can use an escape sequence if the operating system does not support the required character, such as a control character, or for any other purpose that requires a character that cannot be used in a parameter file.
An escape sequence can be used anywhere in the parameter file, but is particularly useful in the following elements within a TABLE
or MAP
statement:
-
An object name
-
WHERE
clause -
COLMAP
clause to assign a Unicode character to a Unicode column, or to assign a native-encoded character to a column. -
Oracle GoldenGate column conversion functions within a
COLMAP
clause.
Oracle GoldenGate supports the following types of escape sequence:
-
\uFFFF
Unicode escape sequence. AnyUNICODE
code point can be used except surrogate pairs. -
\377
Octal escape sequence -
\xFF
Hexadecimal escape sequence
The following rules apply:
-
If used for mapping of an object name in
TABLE
orMAP
, no restriction apply. For example, the followingTABLE
specification is valid:TABLE schema."\u3000ABC";
-
If used with a column-mapping function, any code point can be used, but only for an
NCHAR/NVARCHAR
column. For anCHAR/VARCHAR
column, the code point is limited to the equivalent of 7-bit ASCII. -
The source and target data types must be identical (for example,
NCHAR
toNCHAR
). -
Begin each escape sequence with a reverse solidus (code point
U+005C
), followed by the character code point. (A solidus is more commonly known as the backslash symbol.) Use the escape sequence, instead of the actual character, within your input string in the parameter statement or column-conversion function.
Note:
To specify an actual backslash in the parameter file, specify a double backslash. For example, the following finds a backslash in COL1: @STRFIND (COL1, '\\' )
.
To Use the \uFFFF Unicode Escape Sequence
-
The
\uFFFF
Unicode escape sequence must begin with a lowercaseu
, followed by exactly four hexadecimal digits. -
Supported ranges are as follows:
-
0
to9
(U+0030
toU+0039
) -
A
toF
(U+0041
toU+0046
) -
a
tof
(U+0061
toU+0066
)
-
\u20ac
is the Unicode escape sequence for the Euro currency sign.
Note:
For reliable cross-platform support, use the Unicode escape sequence. Octal and hexadecimal escape sequences are not standardized on different operating systems.
To Use the \377 Octal Escape Sequence
-
Must contain exactly three octal digits.
-
Supported ranges:
-
Range for first digit is
0
to3
(U+0030
toU+0033
) -
Range for second and third digits is
0
to7
(U+0030
toU+0037
)\200
is the octal escape sequence for the Euro currency sign on Microsoft Windows
-
To Use the \xFF Hexadecimal Escape Eequence
-
Must begin with a lowercase
x
followed by exactly two hexadecimal digits. -
Supported ranges:
-
0
to9
(U+0030
toU+0039
) -
A
toF
(U+0041
toU+0046
) -
a
tof
(U+0061
toU+0066
)
-
\x80
is the hexadecimal escape sequence for the Euro currency sign on Microsoft Windows 1252 Latin1 code page.
Mapping Columns Using TABLE and MAP
Oracle GoldenGate provides for column mapping at the table level and at the global level. Default column mapping is also provided in the absence of explicit column mapping rules.
This section contains the following guidelines for mapping columns:
Supporting Case and Special Characters in Column Names
By default, Oracle GoldenGate follows SQL-92 rules for specifying column names and literals. In Oracle GoldenGate parameter files, conversion functions, user exits, and commands, case-sensitive column names must be enclosed within double quotes if double quotes are required by the database to enforce case-sensitivity. For other case-sensitive databases that do not require quotes, case-sensitive column names must be specified as they are stored in the database. Literals must be enclosed within single quotes. See Differentiating Case-Sensitive Column Names from Literals for more information.
Configuring Table-level Column Mapping with COLMAP
If you are using self-describing trails then any column on the source object is mapped to the same column name on the target object. You only need to manage column names that are different between source and target or if you need to transform a column.
However, if not using self-describing trails then the default mapping is done by column order and not the column name. So column 1 on the source will be mapped to column 1 on the target, column 2 to column 2 and so on.
Use the COLMAP
option of the MAP
and
TABLE
parameters to:
-
map individual source columns to target columns that have different names.
-
specify default column mapping when an explicit column mapping is not needed.
-
Provide instructions for selecting, mapping, translating, and moving data from a source column into a target column.
Using USEDEFAULTS to Enable Default Column Mapping
You can use the USEDEFAULTS
option of
COLMAP
to specify automatic default column mapping for any
corresponding source and target columns that have identical names.
USEDEFAULTS
can save you time by eliminating the need to map every
target column explicitly.
Default mapping causes Oracle GoldenGate to map those columns and, if required, translate the data types based on the data-definitions file. Do not specify default mapping for columns that are mapped already with an explicit mapping statement.
The following example of a column mapping illustrates the use of both default
and explicit column mapping for a source table ACCTBL
and a target
table ACCTTAB
. Most columns are the same in both tables, except for the
following differences:
-
The source table has a
CUST_NAME
column, whereas the target table has aNAME
column. -
A ten-digit
PHONE_NO
column in the source table corresponds to separateAREA_CODE
,PHONE_PREFIX
, andPHONE_NUMBER
columns in the target table. -
Separate
YY, MM
, andDD
columns in the source table correspond to a singleTRANSACTION_DATE
column in the target table.
To address those differences, USEDEFAULTS
is used to map the
similar columns automatically, while explicit mapping and conversion functions are used
for dissimilar columns.
The following sample shows the column mapping using the COLMAP
option of
the MAP
and TABLE
parameters. It describes the mapping
of the source table ACCTBL
to the target table
ACCTTAB
.
MAP SALES.ACCTBL, TARGET SALES.ACCTTAB,
COLMAP ( USEDEFAULTS,
NAME = CUST_NAME,
TRANSACTION_DATE = @DATE ('YYYY-MM-DD', 'YY',YEAR, 'MM', MONTH, 'DD', DAY),
AREA_CODE = @STREXT (PHONE_NO, 1, 3),
PHONE_PREFIX = @STREXT (PHONE_NO, 4, 6),
PHONE_NUMBER = @STREXT (PHONE_NO, 7, 10)
)
;
Table 11-3 Sample Column Mapping
Parameter statement | Description |
---|---|
|
Begins the |
|
Maps source columns as-is when the target column names are identical. |
|
Maps the source column |
|
Converts the transaction date from the source date
columns to the target column |
|
Converts the source column |
See Understanding Default Column Mapping for more information about the rules followed by Oracle GoldenGate for default column mapping.
Specifying the Columns to be Mapped in the COLMAP Clause
The COLMAP
syntax is the following:
COLMAP ([USEDEFAULTS, ] target_column
= source_expression
)
In this syntax, target_column
is the name of the target
column and source_expression
. Some examples of
source_expressions
are:
-
The name of a source column, such as
ORD_DATE
. -
Numeric constant, such as
123
. -
String constant enclosed within single quotes, such as
'ABCD'
. -
An expression using an Oracle GoldenGate column-conversion function. Within a
COLMAP
statement, you can use any of the Oracle GoldenGate column-conversion functions to transform data for the mapped columns, for example:@STREXT (COL1, 1, 3)
-
Here's an example of using
BEFORE column_name
:BEFORE ORD_DATE
-
Here's an example of using
AFTER column_name
:AFTER ORD_DATE
. This is the default option if a column name is listed.
If the column mapping involves case-sensitive columns from different database types, specify each column as it is stored in the database.
-
If the database requires double quotes to enforce case-sensitivity, specify the case-sensitive column name within double quotes.
-
If the database is case-sensitive without requiring double quotes, specify the column name as it is stored in the database.
The following shows a mapping between a target column in an Oracle database and a source column in a case-sensitive SQL Server database.
COLMAP ("ColA" = ColA)
See Specifying Object Names in Oracle GoldenGate Input for more information about specifying names to Oracle GoldenGate.
See Globalization Considerations when Mapping Data for globalization considerations when mapping source and target columns in databases that have different character sets and locales.
Avoid using COLMAP
to map a value to a key column (which causes the
operation to become a primary key update), The WHERE
clause that Oracle
GoldenGate uses to locate the target row will not use the correct before image of the key
column. Instead, it will use the after image. This will cause errors if you are using any
functions based on that key column, such as a SQLEXEC
statement.
Column Mapping Limitations
-
LOB
columns cannot be used inFILTER
,WHERE
columns, or as asource_expression
in aCOLMAP
statement.LOB
columns areBLOB
,CLOB
,NCLOB
,XMLType
, User-Defined Data Types, Nested Tables,VARRAYs
and other special data types. -
If the source column contains more than 4000 bytes, it cannot be used in transformation routines, as the value is stored in the trail as an
LOB
record. For example aVARCHAR2(4000 CHAR
) in Oracle and the Japanese character set is stored as 3 bytes for each character. This implies that the column could be 12000 bytes long and Oracle GoldenGate would store this value as anLOB
field. -
The full SQL statement that Oracle GoldenGate would execute would exceed 4MB in size. For example, if you have a table with thousands of
VARCHAR2(4000)
columns and you want to put 4000 bytes in each one, this could cause the total SQL statement that Oracle GoldenGate is going to execute to exceed the maximum size of 4MB.
Configuring Global Column Mapping with COLMATCH
Use the COLMATCH
parameter to create global rules for column mapping. With COLMATCH
, you can map between similarly structured tables that have different column names for the same sets of data. COLMATCH
provides a more convenient way to map columns of this type than does using table-level mapping with a COLMAP
clause in individual TABLE
or MAP
statements.
Case-sensitivity is supported as follows:
-
For MySQL, SQL Server if the database is case-sensitive,
COLMATCH
looks for an exact case and name match regardless of whether or not a name is specified in quotes. -
For Oracle Database and Db2 databases, where names can be either case-sensitive or case-insensitive in the same database and double quotes are required to show case-sensitivity,
COLMATCH
requires an exact case and name match when a name is in quotes in the database.
Syntax
COLMATCH
{NAMES target_column = source_column |
PREFIX prefix |
SUFFIX suffix |
RESET}
Argument | Description |
---|---|
|
Maps based on column names. Put double quotes around the column name if it is case-sensitive and the database requires quotes to enforce case-sensitivity. For these database types, an unquoted column name is treated as case-insensitive by Oracle GoldenGate. For databases that support case-sensitivity without requiring quotes, specify the column name as it is stored in the database. If the
|
|
Ignores the specified name prefix or suffix. Put double quotes around the prefix or suffix if the database requires quotes to enforce case-sensitivity, for example " For databases that support case-sensitivity without requiring quotes, specify the prefix or suffix as it is stored in the database. For example, The following example specifies a case-insensitive prefix to ignore. The target column name COLMATCH PREFIX p_ The following example specifies a case-sensitive SUFFIX "_k" |
|
Turns off previously defined |
The following example illustrates when to use COLMATCH.
The source and target tables are identical except for slightly different table and column names.The database is case-insensitive.
ACCT Table | ORD Table |
---|---|
|
|
ACCOUNT Table | ORDER Table |
---|---|
|
|
To map the source columns to the target columns in this example, as well as to handle subsequent maps for other tables, the syntax is:
COLMATCH NAMES CUSTOMER_CODE = CUST_CODE
COLMATCH NAMES CUSTOMER_NAME = CUST_NAME
COLMATCH NAMES CUSTOMER_ADDRESS = CUST_ADDR
COLMATCH PREFIX S_
MAP SALES.ACCT, TARGET SALES.ACCOUNT, COLMAP (USEDEFAULTS);
MAP SALE.ORD, TARGET SALES.ORDER, COLMAP (USEDEFAULTS);
COLMATCH RESET
MAP SALES.REG, TARGET SALE.REG;
MAP SALES.PRICE, TARGET SALES.PRICE;
Based on the rules in the example, the following occurs:
-
Data is mapped from the
CUST_CODE
columns in the sourceACCT
andORD
tables to theCUSTOMER_CODE
columns in the targetACCOUNT
andORDER
tables. -
The
S_
prefix will be ignored. -
Columns with the same names, such as the
PHONE
andORDER_AMT
columns, are automatically mapped by means ofUSEDEFAULTS
without requiring explicit rules. See Understanding Default Column Mapping for more information. -
The previous global column mapping is turned off for the tables
REG
andPRICE
. Source and target columns in those tables are automatically mapped because all of the names are identical.
Understanding Default Column Mapping
For self-describing trails, if an explicit column mapping does not exist, either by
using COLMATCH
or COLMAP
, Oracle GoldenGate maps
source and target columns by default according to the following rules.
This doesn't apply if you are using SOURCEDEFS
or
TARGETDEFS
.
-
If a source column is found whose name and case exactly match those of the target column, the two are mapped.
-
If no case match is found, fallback name mapping is used. Fallback mapping performs a case-insensitive target table mapping to find a name match. Inexact column name matching is applied using upper cased names. This behavior is controlled by the
GLOBALS
parameterNAMEMATCHIGNORECASE
. You can disable fallback name matching with theNAMEMATCHEXACT
parameter, or you can keep it enabled but with a warning message by using theNAMEMATCHNOWARNING
parameter. -
Target columns that do not correspond to any source column take default values determined by the database.
If the default mapping cannot be performed, the target column defaults to one of the values shown in the following table.
Column Type | Value |
---|---|
Numeric |
Zero (0) |
Character or |
Spaces |
Date or Datetime |
Current date and time |
Columns that can take a |
Null |
Data Type Conversions
Learn about how Oracle GoldenGate maps data types.
Numeric Columns
Numeric columns are converted to match the type and scale of the target column. If the scale of the target column is smaller than that of the source, the number is truncated on the right. If the scale of the target column is larger than that of the source, the number is padded with zeros on the right.
You can specify a substitution value for invalid numeric data encountered when
mapping number columns by using the REPLACEBADNUM
parameter for
more information.
Character-type Columns
Character-type columns can accept character-based data types such as VARCHAR
, numeric in string form, date and time in string form, and string literals. If the scale of the target column is smaller than that of the source, the column is truncated on the right. If the scale of the target column is larger than that of the source, the column is padded with spaces on the right.
Literals must be enclosed within single quotes.
You can control the response of the Oracle GoldenGate process when a valid code point
does not exist for either the source or target character set when mapping character
columns by using the REPLACEBADCHAR
parameter for more information.
Datetime Columns
Datetime (DATE
, TIME
, and TIMESTAMP
) columns can accept datetime and character columns, as well as string literals. Literals must be enclosed within single quotes. To map a character column to a datetime column, make certain it conforms to the Oracle GoldenGate external SQL format of YYYY-MM-DD HH:MI:SS.FFFFFF.
Oracle GoldenGate supports timestamp data from 0001-01-03 00:00:00
to 9999-12-31 23:59:59
. If a timestamp is converted from GMT to local time, these limits also apply to the resulting timestamp. Depending on the timezone, conversion may add or subtract hours, which can cause the timestamp to exceed the lower or upper supported limit.
Required precision varies according to the data type and target platform. If the scale of the target column is smaller than that of the source, data is truncated on the right. If the scale of the target column is larger than that of the source, the column is extended on the right with the values for the current date and time.
Selecting and Filtering Rows
Filtering can only be performed on columns that are available to Oracle GoldenGate.
In the TRANLOG
Extract Oracle GoldenGate has access to all columns
that are present in the redo logs and in the database. If the columns are not in the
redo logs, they must be explicitly fetched (using FETCHCOLS
) to be
able to filter them. In the Extract pump and in the Replicat, the columns must be
available in the trail file. Because of this, any column that you want to use in a
FILTER
or WHERE
clause must be explicitly
logged using ADD TRANDATA COLS
, and you have to retain the default
of LOGALLSUPCOLS
.
To filter out or select rows for extraction or replication, use the FILTER
and WHERE
clauses of the TABLE
and MAP
parameters.
The FILTER
clause offers you more functionality than the WHERE
clause because you can employ any of the Oracle GoldenGate column conversion functions, whereas the WHERE
clause accepts basic WHERE
operators.
Selecting Rows with a FILTER Clause
Use a FILTER
clause to select rows based on a numeric value by using basic operators or one or more Oracle GoldenGate column-conversion functions.
Note:
To filter a column based on a string, use one of the Oracle GoldenGate string functions or use a WHERE
clause.
The syntax for FILTER
in a TABLE
statement is as follows:
TABLE source_table,
, FILTER (
[, ON INSERT | ON UPDATE| ON DELETE]
[, IGNORE INSERT | IGNORE UPDATE | IGNORE DELETE]
, filter_clause
);
The syntax for FILTER
in a MAP
statement is as
follows and includes an error-handling option.
MAP source_table
, TARGET target_table
,
, FILTER (
[, ON INSERT | ON UPDATE| ON DELETE]
[, IGNORE INSERT | IGNORE UPDATE | IGNORE DELETE]
[, RAISEERROR error_number
]
, filter_clause
);
Valid FILTER
clause elements are the following:
-
An Oracle GoldenGate column-conversion function. These functions are built into Oracle GoldenGate so that you can perform tests, manipulate data, retrieve values, and so forth. See Testing and Transforming Data for more information about Oracle GoldenGate conversion functions.
-
Numbers
-
Columns that contain numbers
-
Functions that return numbers
-
Arithmetic operators:
-
+
(plus) -
-
(minus) -
*
(multiply) -
/
(divide) -
\
(remainder)
-
-
Comparison operators:
-
>
(greater than) -
>=
(greater than or equal) -
<
(less than) -
<=
(less than or equal) -
=
(equal) -
<>
(not equal) -
Results derived from comparisons can be zero (indicating
FALSE
) or non-zero (indicatingTRUE
).
-
-
Parentheses (for grouping results in the expression)
-
Conjunction operators:
AND
,OR
Use the following FILTER
options to specify which SQL operations a filter clause affects. Any of these options can be combined.
ON INSERT | ON UPDATE | ON DELETE
IGNORE INSERT | IGNORE UPDATE | IGNORE DELETE
Use the RAISEERROR
option of FILTER
in the MAP
parameter to generate a user-defined error when the filter fails. This option is useful when you need to trigger an event in response to the failure.
Use the @RANGE
function within a FILTER
clause to
distribute the processing workload among multiple MAP
or
TABLE
statements.
REPERROR (9999, EXCEPTION)
MAP OWNER.SRCTAB, TARGET OWNER.TARGTAB,
SQLEXEC (ID CHECK, ON UPDATE, QUERY ' SELECT COUNT FROM TARGTAB WHERE PKCOL = :P1 ', PARAMS (P1 = PKCOL)),
FILTER (BALANCE > 15000),
FILTER (ON UPDATE, @BEFORE (COUNT) = CHECK.COUNT)
;
MAP OWNER.SRCTAB, TARGET OWNER.TARGEXC,
EXCEPTIONSONLY,
COLMAP ( USEDEFAULTS,
ERRTYPE = 'UPDATE FILTER FAILED'
)
;
Table 11-4 Using Multiple FILTER Statements
Parameter file | Description |
---|---|
|
Raises an exception for the specified error. |
|
Starts the |
|
Performs a query to retrieve the present value of the |
|
Uses a |
|
Uses another |
|
The semicolon concludes the |
|
Designates an exceptions |
Example 11-5 Calling the @COMPUTE Function
The following example calls the @COMPUTE
function to extract records in which the price multiplied by the amount exceeds 10,000.
MAP SALES.TCUSTORD, TARGET SALES.TORD,
FILTER (@COMPUTE (PRODUCT_PRICE * PRODUCT_AMOUNT) > 10000);
Example 11-6 Calling the @STREQ Function
The following uses the @STREQ
function to extract records where the value of a character column is 'JOE'.
TABLE ACCT.TCUSTORD, FILTER (@STREQ ("Name", 'joe') > 0);
Example 11-7 Selecting Records
The following selects records in which the AMOUNT
column is greater than 50 and executes the filter on UPDATE
and DELETE
operations.
TABLE ACT.TCUSTORD, FILTER (ON UPDATE, ON DELETE, AMOUNT > 50);
Example 11-8 Using the @RANGE Function
(Replicat group 1 parameter file)
MAP sales.acct, TARGET sales.acct, FILTER (@RANGE (1, 2, ID));
(Replicat group 2 parameter file)
MAP sales.acct, TARGET sales.acct, FILTER (@RANGE (2, 2, ID));
You can combine several FILTER
clauses in one MAP
or TABLE
statement, as shown in Table 11-4, which shows part of a Replicat parameter file. Oracle GoldenGate executes the
filters in the order listed, until one fails or until all are passed. If one filter
fails, they all fail.
Selecting Rows with a WHERE Clause
Use any of the elements in Table 11-5 in a WHERE
clause to select or exclude rows (or both) based on a
conditional statement. Each WHERE
clause must be enclosed within
parentheses. Literals must be enclosed within single quotes.
Table 11-5 Permissible WHERE Operators
Element | Examples |
---|---|
Column names |
|
Numeric values |
|
Literal strings |
|
Built-in column tests |
|
Comparison operators |
=, <>, >, <, >=, < |
Conjunctive operators |
|
Grouping parentheses |
Use open and close parentheses ( ) for logical grouping of multiple elements. |
Oracle GoldenGate does not support FILTER
for columns that have a multi-byte character set or a character set that is incompatible with the character set of the local operating system.
Arithmetic operators and floating-point data types are not supported by
WHERE
. To use more complex selection conditions, use a
FILTER
clause or a user exit routine.
The syntax for WHERE
is identical in the TABLE
and MAP
statements:
TABLE table, WHERE (clause);
MAP source_table, TARGET target_table, WHERE (clause);
Considerations for Selecting Rows with FILTER and WHERE
The following suggestions can help you create a successful selection clause.
Note:
The examples in this section assume a case-insensitive database.
Ensuring Data Availability for Filters
If the database only logs values for changed columns to the transaction log, there can be errors if any of the unchanged columns are referenced by selection criteria. Oracle GoldenGate ignores such row operations, outputs them to the discard file, and issues a warning.
To avoid missing-column errors, create your selection conditions as follows:
-
Use only primary-key columns as selection criteria, if possible.
-
Make required column values available by enabling supplemental logging for those columns. Alternatively, you can use the
FETCHCOLS
orFETCHCOLSEXCEPT
option of theTABLE
parameter. These options are valid for all supported databases. They query the database to fetch the values if they are not present in the log. To retrieve the values before theFILTER
orWHERE
clause is executed, include theFETCHBEFOREFILTER
option in theTABLE
statement before theFILTER
orWHERE
clause. For example:TABLE DEMO.PEOPLE, FETCHBEFOREFILTER, FETCHCOLS (age), FILTER (age > 50);
-
Test for a column's presence first, then for the column's value. To test for a column's presence, use the following syntax.
column_name
{= | <>} {@PRESENT | @ABSENT}The following example returns all records when the
amount
column is over 10,000 and does not cause a record to be discarded whenamount
is absent.WHERE (amount = @PRESENT AND amount > 10000)
Comparing Column Values
To ensure that elements used in a comparison match, compare appropriate column types:
-
Character columns to literal strings.
-
Numeric columns to numeric values, which can include a sign and decimal point.
-
Date and time columns to literal strings, using the format in which the column is retrieved by the application.
Testing for NULL Values
To evaluate columns for NULL
values, use the following syntax.
column
{= | <>} @NULL
The following returns TRUE
if the column value is
NULL
, and thereby replicates the row. It
returns FALSE
for all other cases (including a
column missing from the record).
WHERE (amount = @NULL)
The following returns TRUE
only if the column is present in the
record and is not NULL
.
WHERE (amount = @PRESENT AND amount <> @NULL)
Note:
If a value in the trail contains more than 4000 bytes then the@NULL
function will return TRUE
.
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
-
Use the
@BEFORE
column conversion function with the name of the column for which you want a before value, as follows:@BEFORE (
column_name
) -
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 theGETBEFORECOLS
option ofTABLE
. 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. See@BEFORE
and@AFTER
in the Parameters and Functions Reference for Oracle GoldenGate.
Selecting Columns
To control which columns of a source table are extracted by Oracle GoldenGate, use the COLS
and COLSEXCEPT
options of the TABLE
parameter. Use COLS
to select columns for extraction, and use COLSEXCEPT
to select all columns except those designated by COLSEXCEPT
.
Restricting the columns that are extracted can be useful when a target table does not contain the same columns as the source table, or when the columns contain sensitive information, such as a personal identification number or other proprietary business information.
Selecting and Converting SQL Operations
By default, Oracle GoldenGate captures and applies INSERT
, UPDATE
, and DELETE
operations. You can use the following parameters in the Extract or Replicat parameter file to control which kind of operations are processed, such as only inserts or only inserts and updates.
GETINSERTS | IGNOREINSERTS
GETUPDATES | IGNOREUPDATES
GETDELETES | IGNOREDELETES
You can convert one type of SQL operation to another by using the following parameters in the Replicat parameter file:
-
Use
INSERTUPDATES
to convert source update operations to inserts into the target table. This is useful for maintaining a transaction history on that table. The transaction log record must contain all of the column values of the table, not just changed values. Some databases do not log full row values to their transaction log, but only values that changed. -
Use
INSERTDELETES
to convert all source delete operations to inserts into the target table. This is useful for retaining a history of all records that were ever in the source database. -
Use
UPDATEDELETES
to convert source deletes to updates on the target.
Using Transaction History
Oracle GoldenGate enables you to retain a history of changes made to a target record and to map information about the operation that caused each change. This history can be useful for creating a transaction-based reporting system that contains a separate record for every operation performed on a table, as opposed to containing only the most recent version of each record.
For example, the following series of operations made to a target table named CUSTOMER
would leave no trace of the ID of Dave
. The last operation deletes the record, so there is no way to find out Dave's account history or his ending balance.
Table 11-6 Operation History for Table CUSTOMER
Sequence | Operation | ID | BALANCE |
---|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Retaining this history as a series of records can be useful in many ways. For example, you can generate the net effect of transactions.
To Implement Transaction Reporting
- To prepare Extract to capture before values, use the
GETUPDATEBEFORES
parameter in the Extract parameter file. A before value (or before image) is the existing value of a column before an update is performed. Before images enable Oracle GoldenGate to create the transaction record. - To prepare Replicat to post all operations as inserts, use the
INSERTALLRECORDS
parameter in the Replicat parameter file. Each operation on a table becomes a new record in that table. - To map the transaction history, use the return values of the
GGHEADER
option of the@GETENV
column conversion function. Include the conversion function as the source expression in aCOLMAP
statement in theTABLE
orMAP
parameter.
Using the sample series of transactions shown in Table 11-6 the following parameter configurations can be created to generate a more transaction-oriented view of customers, rather than the latest state of the database.
Process | Parameter statements |
---|---|
Extract |
|
Replicat |
|
Note:
This is not representative of a complete parameter file for an Oracle GoldenGate process. Also note that these examples represent a case-insensitive database.
This configuration makes possible queries such as the following, which returns the net sum of each transaction along with the time of the transaction and the customer ID.
SELECT AFTER.ID, AFTER.TS, AFTER.BALANCE - BEFORE.BALANCE
FROM CUSTHIST AFTER, CUSTHIST BEFORE
WHERE AFTER.ID = BEFORE.ID AND AFTER.TS = BEFORE.TS AND
AFTER.BEFORE_AFTER = 'A' AND BEFORE.BEFORE_AFTER = 'B';
Testing and Transforming Data
Data testing and transformation can be performed by either Extract or Replicat and is implemented by using the Oracle GoldenGate built-in column-conversion functions within a COLMAP
clause of a TABLE
or MAP
statement. With these conversion functions, you can:
-
Transform dates.
-
Test for the presence of column values.
-
Perform arithmetic operations.
-
Manipulate numbers and character strings.
-
Handle null, invalid, and missing data.
-
Perform tests.
If you need to use logic beyond that which is supplied by the Oracle GoldenGate functions, you can call your own functions by implementing Oracle GoldenGate user exits.
Oracle GoldenGate conversion functions take the following general syntax:
Syntax
@function (argument)
Table 11-7 Conversion Function Syntax
Syntax element | Description |
---|---|
@ |
The Oracle GoldenGate function name. Function names have the prefix |
|
A function argument. |
Table 11-8 Function Arguments
Argument element | Example |
---|---|
A numeric constant |
|
A string literal enclosed within single quote marks |
|
The name of a source column |
Depends on whether the database is case-insensitive, is case-sensitive and requires quote marks to enforce the case, or is case-sensitive and does not require quotes. |
An arithmetic expression |
|
A comparison expression |
|
Other Oracle GoldenGate functions |
|
Handling Column Names and Literals in Functions
By default, literal strings must be enclosed in single quotes in a column-conversion function. Case-sensitive column names must be enclosed within double quotes if required by the database, or otherwise entered in the case in which they are stored in the database.
Using the Appropriate Function
Use the appropriate function for the type of column that is being manipulated or evaluated. For example, numeric functions can be used only to compare numeric values. To compare character values, use one of the Oracle GoldenGate character-comparison functions. LOB columns cannot be used in conversion functions.
This statement would fail because it uses @IF
, which is a numerical function, to compare string values.
@IF (SR_AREA = 'Help Desk', 'TRUE', 'FALSE')
The following statement would succeed because it compares a numeric value.
@IF (SR_AREA = 20, 'TRUE', 'FALSE')
See Manipulating Numbers and Character Strings for more information.
Note:
Errors in argument parsing sometimes are not detected until records are processed. Verify syntax before starting processes.
Transforming Dates
Use the @DATE
, @DATEDIF
, and @DATENOW
functions to retrieve dates and times, perform computations on them, and convert them.
This example computes the time that an order is filled
Example 11-9 Computing Time
ORDER_FILLED = @DATE ( 'YYYY-MM-DD HH:MI:SS', 'JTS', @DATE ('JTS', 'YYMMDDHHMISS', ORDER_TAKEN_TIME) + ORDER_MINUTES * 60 * 1000000)
Performing Arithmetic Operations
To return the result of an arithmetic expression, use the @COMPUTE
function. The value returned from the function is in the form of a string. Arithmetic expressions can be combinations of the following elements.
-
Numbers
-
The names of columns that contain numbers
-
Functions that return numbers
-
Arithmetic operators:
-
+
(plus) -
-
(minus) -
*
(multiply) -
/
(divide) -
\
(remainder)
-
-
Comparison operators:
-
>
(greater than) -
>=
(greater than or equal) -
<
(less than) -
<=
(less than or equal) -
=
(equal) -
<>
(not equal)
Results that are derived from comparisons can be zero (indicating
FALSE
) or non-zero (indicatingTRUE
). -
-
Parentheses (for grouping results in the expression)
-
The conjunction operators
AND
,OR
. Oracle GoldenGate only evaluates the necessary part of a conjunction expression. Once a statement isFALSE
, the rest of the expression is ignored. This can be valuable when evaluating fields that may be missing or null. For example, if the value ofCOL1
is 25 and the value ofCOL2
is 10, then the following are possible:@COMPUTE ( (COL1 > 0) AND (COL2 < 3) )
returns0
.@COMPUTE ( (COL1 < 0) AND (COL2 < 3) )
returns 0. COL2 < 3 is never evaluated.@COMPUTE ((COL1 + COL2)/5)
returns 7.
Manipulating Numbers and Character Strings
To convert numbers and character strings, Oracle GoldenGate supplies the following functions:
Table 11-9 Conversion Functions for Numbers and Characters
Purpose | Conversion Function |
---|---|
Convert a binary or character string to a number. |
|
Convert a number to a string. |
|
Compare strings. |
|
Concatenate strings. |
|
Extract from a string. |
|
Return the length of a string. |
|
Substitute one string for another. |
|
Convert a string to upper case. |
|
Trim leading or trailing spaces, or both. |
|
Handling Null, Invalid, and Missing Data
When column data is missing, invalid, or null, an Oracle GoldenGate conversion function returns a corresponding value.
If BALANCE
is 1000
, but AMOUNT
is NULL
, the following expression returns NULL
:
NEW_BALANCE = @COMPUTE (BALANCE + AMOUNT)
These exception conditions render the entire calculation invalid. To ensure a successful conversion, use the @COLSTAT,
@COLTEST
and @IF
functions to test for, and override, the exception condition.
Using @COLSTAT
Use the @COLSTAT
function to return an indicator to Extract or Replicat that a column is missing, null, or invalid. The indicator can be used as part of a larger manipulation formula that uses additional conversion functions.
The following example returns a NULL
into target column ITEM
.
ITEM = @COLSTAT (NULL)
The following @IF
calculation uses @COLSTAT
to return NULL
to the target column if PRICE
and QUANTITY
are less than zero.
ORDER_TOTAL = PRICE * QUANTITY, @IF ((PRICE < 0) AND (QUANTITY < 0), @COLSTAT (NULL))
Using @COLTEST
Use the @COLTEST
function to check for the following conditions:
-
PRESENT
tests whether a column is present and not null. -
NULL
tests whether a column is present and null. -
MISSING
tests whether a column is not present. -
INVALID
tests whether a column is present but contains invalid data.
The following example checks whether the AMOUNT
column is present and NULL
and whether it is present but invalid.
@COLTEST (AMOUNT, NULL, INVALID)
Using @IF
Use the @IF
function to return one of two values based on a condition. Use it with the @COLSTAT
and @COLTEST
functions to begin a conditional argument that tests for one or more exception conditions and then directs processing based on the results of the test.
NEW_BALANCE = @IF (@COLTEST (BALANCE, NULL, INVALID) OR @COLTEST (AMOUNT, NULL, INVALID), @COLSTAT (NULL), BALANCE + AMOUNT)
This conversion returns one of the following:
-
NULL
whenBALANCE
orAMOUNT
isNULL
orINVALID
-
MISSING
when either column is missing -
The sum of the columns.
Performing Tests
The @CASE, @VALONEOF,
and @EVAL
functions provide additional methods for performing tests on data before manipulating or mapping it.
Using @CASE
Use @CASE
to select a value depending on a series of value tests.
@CASE (PRODUCT_CODE, 'CAR', 'A car', 'TRUCK', 'A truck')
This example returns the following:
-
A car
ifPRODUCT_CODE
isCAR
-
A truck
ifPRODUCT_CODE
isTRUCK
-
A
FIELD_MISSING
indication ifPRODUCT_CODE
fits neither of the other conditions
Using @VALONEOF
Use @VALONEOF
to compare a column or string to a list of values.
@IF (@VALONEOF (STATE, 'CA', 'NY'), 'COAST', 'MIDDLE')
In this example, if STATE
is CA
or NY
, the expression returns COAST
, which is the response returned by @IF
when the value is non-zero (meaning TRUE
).
Using @EVAL
Use @EVAL
to select a value based on a series of independent conditional tests.
@EVAL (AMOUNT > 10000, 'high amount', AMOUNT > 5000, 'somewhat high')
This example returns the following:
-
high amount
ifAMOUNT
is greater than10000
-
somewhat high
ifAMOUNT
is greater than5000
, and less than or equal to10000
, (unless the prior condition was satisfied) -
A
FIELD_MISSING
indication if neither condition is satisfied.
Using Tokens
You can capture and store data within the user token area of a trail record header. Token data can be retrieved and used in many ways to customize the way that Oracle GoldenGate delivers information.
For example, you can use token data in:
-
Column maps
-
Stored procedures called by a
SQLEXEC
statement -
User exits
-
Macros
Defining Tokens
To use tokens, you define the token name and associate it with data. The data can be any valid character data or values retrieved from Oracle GoldenGate column-conversion functions.
The token area in the record header permits up to 16,000 bytes of data. Token names, the length of the data, and the data itself must fit into that space.
To define a token, use the TOKENS
option of the TABLE
parameter in the Extract parameter file.
Syntax
TABLEtable_spec
, TOKENS (token_name
=token_data
[, ...]);
Where:
-
table_spec
is the name of the source table. A container or catalog name, if applicable, and an owner name must precede the table name. -
token_name
is a name of your choice for the token. It can be any number of alphanumeric characters and is not case-sensitive. -
token_data
is a character string of up to 2000 bytes. The data can be either a string that is enclosed within single quotes or the result of an Oracle GoldenGate column-conversion function. The character set of token data is not converted. The token must be in the character set of the source database for Extract and in the character set of the target database for Replicat. In the trail file, user tokens are stored in UTF-8.
TABLE ora.oratest, TOKENS ( TK-OSUSER = @GETENV ('GGENVIRONMENT' , 'OSUSERNAME'), TK-GROUP = @GETENV ('GGENVIRONMENT' , 'GROUPNAME') TK-HOST = @GETENV('GGENVIRONMENT' , 'HOSTNAME'));
As shown in this example, the Oracle GoldenGate @GETENV
function is an effective way to populate token data. This function provides several options for capturing environment information that can be mapped to tokens and then used on the target system for column mapping.
Using Token Data in Target Tables
To map token data to a target table, use the @TOKEN
column-conversion function in the source expression of a COLMAP
clause in a Replicat MAP
statement. The @TOKEN
function provides the name of the token to map. The COLMAP
syntax with @TOKEN
is:
Syntax
COLMAP (
target_column
= @TOKEN ('token_name
'))
The following MAP
statement maps target columns host
, gg_group
, and so forth to tokens tk-host
, tk-group
, and so forth. Note that the arguments must be enclosed within single quotes.
User tokens | Values |
---|---|
tk-host |
:sysA |
tk-group |
:extora |
tk-osuser |
:jad |
tk-domain |
:admin |
tk-ba_ind |
:B |
tk-commit_ts |
:2011-01-24 17:08:59.000000 |
tk-pos |
:3604496 |
tk-rba |
:4058 |
tk-table |
:oratest |
tk-optype |
:insert |
Example 11-10 MAP Statement
MAP ora.oratest, TARGET ora.rpt, COLMAP (USEDEFAULTS, host = @token ('tk-host'), gg_group = @token ('tk-group'), osuser= @token ('tk-osuser'), domain = @token ('tk-domain'), ba_ind= @token ('tk-ba_ind'), commit_ts = @token ('tk-commit_ts'), pos = @token ('tk-pos'), rba = @token ('tk-rba'), tablename = @token ('tk-table'), optype = @token ('tk-optype'));
The tokens in this example will look similar to the following within the record header in the trail: