You can use wildcards for table and sequence names. Do not use a wildcard for an owner name (such as schema). Where appropriate, Oracle GoldenGate parameters permit the use of two wildcard types to specify multiple objects in one statement:
● A question mark (?) replaces one character. For example in a schema that contains tables named TABn, where n is from 0 to 9, a wildcard specification of HQ.TAB? would return HQ.TAB0, HQ.TAB1, HQ.TAB2, and so on, up to HQ.TAB9, but no others. This wildcard is not supported for DEFGEN.
● An asterisk (*) represents any number of characters (including zero sequence). For example, the specification of HQ.T* could return such objects as HQ.TOTAL, HQ.T123, and HQ.T.
● In TABLE and MAP statements, you can combine the asterisk and question-mark wildcard characters in source object names.
Rules for using wildcards for source objects
For source objects, you can use the asterisk alone or with a partial name. For example, the following source specifications are valid:
● TABLE HQ.*;
● MAP HQ.T_*;
● MAP HQ.T_*, TARGET HQ.*;
The TABLE, MAP and SEQUENCE parameters takes the case-sensitivity and locale of the database into account for wildcard resolution. For databases that are created as case-sensitive or case-incase-sensitive, the wildcard matching matches the exact name and case. For example, if the database is case-sensitive, SCHEMA.TABLE is matched to SCHEMA.TABLE, Schema.Table is matched to Schema.Table, and so forth. If the database is case-insensitive, the matching is not case-sensitive.
For databases that can have both case-sensitive and case-insensitive object names in the same database instance, with the use of quote marks to enforce case-sensitivity, the
Teradata No
Always
case-insensitive, stores in mixed case
No effect No effect
TimesTen No
Always
case-insensitive, stores in upper case
Case-insensitive,
stores in upper case Case-insensitive, stores in upper case Table 2 Case sensitivity of object names per database (continued)
Database Requires quotes to
en-force case-sensitivity? Unquoted object name Quoted object name
Specifying object names in Oracle GoldenGate input
following statements produce the same results:
TABLE *;
TABLE "*";
Similarly, a question mark wildcard used alone matches any single character, whether or not it is within quotes. The following produce the same results:
TABLE ?;
TABLE "?";
If a question mark or asterisk wildcard is used with other characters, case-sensitivity is applied to the non-wildcard characters, but the wildcard matches both case-sensitive and case-insensitive names.
● The following TABLE statements capture any table name that begins with lower-case
"abc". The quoted name case is preserved and a case-sensitive match is applied. It captures table names that include "abcA" and "abca" because the wildcard matches both case-sensitive and case-insensitive characters.
TABLE "abc*";
TABLE "abc?";
● The following TABLE statements capture any table name that begins with upper-case ABC, because the partial name is case-insensitive (no quotes) and is stored in upper case by this database. However, because the wildcard matches both case-sensitive and case-insensitive characters, this example captures table names that include ABCA and
"ABCa".
TABLE abc*;
TABLE abc?;
Rules for using wildcards for target objects
When using wildcards in the TARGET clause of a MAP statement, the target objects must exist in the target database (unless DDL replication is supported and in use. This allows new schemas and their objects to be replicated as they are created.)
For target objects, only an asterisk can be used. If an asterisk wildcard is used with a partial name, Replicat replaces the wildcard with the entire name of the corresponding source object. Therefore, specifications such as the following are incorrect:
TABLE HQ.T_*, TARGET RPT.T_*;
MAP HQ.T_*, TARGET RPT.T_*;
The preceding produces the following results because the wildcard in the target
specification is replaced with T_TEST(the name of a source object), making the whole target name T_T_TEST<xxx>. The following illustrates this:
● HQ.T_TEST1 maps to RPT.T_T_TEST1
● HQ.T_TEST2 maps to RPT.T_T_TEST2 (And so forth)
The following examples show the correct use of asterisk wildcarding.
MAP HQ.T_*, TARGET RPT.*;
Specifying object names in Oracle GoldenGate input
This enables the following correct result:
● HQ.T_TEST1 maps to RPT.T_TEST1
● HQ.T_TEST2 maps to RPT.T_TEST2 (And so forth)
Fallback name mapping
Oracle GoldenGate has a fallback mapping mechanism in the event that a source name cannot be mapped to a target name. If an exact match cannot be found on the target for a case-sensitive source object, Replicat tries to map the source name to the same name in upper or lower case (depending on the database type) on the target. For more information, see the NAMEMATCHIGNORECASE parameter in the Oracle GoldenGate Windows and UNIX Reference Guide.
Wildcard mapping from pre-11.2.1 trail version
If Replicat is configured to read from a trail file that is a version prior to Oracle GoldenGate 11.2.1, the target mapping is made in the following manner to provide backward
compatibility.
● Quoted object names are case-sensitive.
● Unquoted object names are case-insensitive.
The following maps a case-sensitive table name "abc" to target "abc". This only happens with a trail that was written by pre-11.2.1 Extract for SQL Server databases with a case-sensitive configuration and for Sybase. Note that in this example, if the target database is Oracle, DB2 or SQL/MX, fallback name mapping is performed if the target database does not contain case-sensitive "abc" but does have table ABC. (See “Fallback name mapping”.) MAP "abc", TARGET *;
The following example maps a case-insensitive table name abc to target table name ABC. Previous releases of Oracle GoldenGate stored case-insensitive object names to the trail in upper case; thus the target table name is always upper cased. For case-insensitive name conversion, the comparison is in uppercase, A to Z characters only, in US-ASCII without taking locale into consideration.
MAP abc, TARGET *;
Asterisks or question marks as literals in object names
If the name of an object itself includes an asterisk or a question mark, the entire name must be escaped and placed within double quotes, as in the following example:
TABLE HT.“\?ABC”;
How wildcards are resolved
By default, when an object name is wildcarded, the resolution for that object occurs when the first row from the source object is processed. (By contrast, when the name of an object is stated explicitly, its resolution occurs at process startup.) To change the rules for resolving wildcards, use the WILDCARDRESOLVE parameter. The default is DYNAMIC.