Datetime columns
Datetime (DATE, TIME, and TIMESTAMP) columns can accept datetime and character columns, as well as string literals. To map a character column to a datetime column, make certain it conforms to theOracle GoldenGateexternal SQL format of YYYY-MM-DD:HH:MI:SS.FFFFFF.
By default, literals must be enclosed within double quotes. For information about using SQL-92 rules to delimit column identifiers and literals, see “Supporting case and special characters in column names” on page 144.
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 rows
To filter out or select rows for extraction or replication, use the FILTER and WHERE clauses of the following 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.
Syntax TABLE <table spec>, , FILTER (
[, ON INSERT | ON UPDATE| ON DELETE]
[, IGNORE INSERT | IGNORE UPDATE | IGNORE DELETE]
, <filter clause>);
Or...
Syntax MAP <table spec>, TARGET <table spec>, , FILTER (
[, ON INSERT | ON UPDATE| ON DELETE]
[, IGNORE INSERT | IGNORE UPDATE | IGNORE DELETE]
[, RAISEERROR <error_num>]
, <filter clause>);
Valid FILTER clause elements are the following:
Extract Replicat
TABLE MAP
Selecting rows
● 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. For more information about Oracle GoldenGate conversion functions, see
“Testing and transforming data” on page 158.
● 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 (indicating TRUE).
● 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.
By default, literal strings must be enclosed in double quotes in FILTER. To use double-quotes for column names that are case-sensitive or contain special characters, and to use single quotes for literal strings, use the USEANSISQLQUOTES parameter in the GLOBALS file. For more information, see the Oracle GoldenGate Windows and UNIX Reference Guide.
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.
Example 1 The following 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);
Selecting rows
Example 2 The following uses the @STREQ function to extract records where a string is equal to ’JOE’.
This example assumes that the USEANSISQLQUOTES parameter is used in the GLOBALS parameter file to apply SQL-92 rules for single and double quote marks.
TABLE ACCT.TCUSTORD, FILTER (@STREQ ("Name", ’joe’) > 0);
Example 3 The following selects records in which the amount column is greater than 50 and executes the filter on updates and deletes.
TABLE ACT.TCUSTORD, FILTER (ON UPDATE, ON DELETE, AMOUNT > 50);
Example 4 You can use the @RANGE function to divide the processing workload among multiple FILTER clauses, using separate TABLE or MAP statements. For example, the following splits the replication workload into two ranges (between two Replicat processes) based on the ID column of the source acct table. Note that object names are case-sensitive in this case.
(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));
Example 5 You can combine several FILTER clauses in one MAP or TABLE statement, as shown in Table 19, 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.
Table 19 Using multiple FILTER statements
Parameter file Description
REPERROR (9999, EXCEPTION) 1. Raises an exception for the specified error.
MAP OWNER.SRCTAB, TARGET OWNER.TARGTAB,
2. Starts the MAP statement.
SQLEXEC (ID CHECK, ON UPDATE, QUERY “ SELECT COUNT FROM TARGTAB “
“WHERE PKCOL = :P1 ”, PARAMS (P1 = PKCOL)),
3. Performs a query to retrieve the present value of the count column whenever an update is encountered.
FILTER (BALANCE > 15000), 4. Uses a FILTER clause to select rows where the balance is greater than 15000.
FILTER (ON UPDATE, BEFORE.COUNT = CHECK.COUNT)
5. Uses another FILTER clause to ensure that the value of the source count column before an update matches the value in the target column before applying the target update.
; 6. The semicolon concludes the MAP statement.