Using Wildcards in Database Object Names
You can use wildcards for any part of a fully qualified object name, if supported for the specific database. These name parts can be the following: the container, database, or catalog name, the owner (schema or database name), and table or sequence name. For specifics on how object names and wildcards are supported, see the Oracle GoldenGate installation and configuration guide for that database.
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
TAB
n
, where n is from 0 to 9, a wildcard specification ofHQ.TAB?
returnsHQ.TAB0
,HQ.TAB1
,HQ.TAB2
, and so on, up toHQ.TAB9
, but no others. This wildcard is not supported for the DB2 LUW database nor for DEFGEN. This wildcard can only be used to specify source objects in aTABLE
orMAP
parameter. It cannot be used to specify target objects in theTARGET
clause ofTABLE
orMAP
. -
An asterisk (*) represents any number of characters (including zero sequence). For example, the specification of
HQ.T*
could return such objects asHQ.TOTAL
,HQ.T123
, andHQ.T
. This wildcard is valid for all database types throughout all Oracle GoldenGate commands and parameters where a wildcard is allowed. -
In
TABLE
andMAP
statements, you can combine the asterisk and question-mark wildcard characters in source object names only.
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.*;
-
TABLE PDB*.HQ.*;
-
MAP HQ.T_*;
-
MAP HQ.T_*, TARGET HQ.*;
The TABLE
, MAP
and SEQUENCE
parameters take the case-sensitivity and locale of the database into account for wildcard resolution. For databases that are created as case-sensitive or case-insensitive, the wildcard 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 wildcarding works differently. When used alone for a source name in a TABLE
statement, an asterisk wildcard matches any character, whether or not the asterisk is within quotes. The following statements produce the same results:
TABLE hr.*; TABLE hr."*";
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 hr.?; TABLE hr."?";
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-caseabc
. 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 hr."abc*"; TABLE hr."abc?";
-
The following
TABLE
statements capture any table name that begins with upper-caseABC
, 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 includeABCA
and"ABCa"
.TABLE hr.abc*; TABLE hr.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. (The exception is when DDL replication is being used, which 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:
TABLEHQ.T
_*, TARGET RPT.T_*; MAPHQ.T
_*, TARGET RPT.T_*;
The preceding mappings produce incorrect 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
n
. The following illustrates the incorrect results:
-
HQ.T_TEST1
maps toRPT.T_T_TEST1
-
HQ.T_TEST2
maps toRPT.T_T_TEST2
-
(The same pattern applies to all other
HQ.T_TEST
n
mappings.)
The following examples show the correct use of asterisk wildcards.
MAP HQ.T_*, TARGET RPT.*;
The preceding example produces the following correct results:
-
HQ.T_TEST1
maps toRPT.T_TEST1
-
HQ.T_TEST2
maps toRPT.T_TEST2
-
(The same pattern applies to all other
HQ.T_TEST
n
mappings.)
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. Fallback name mapping is controlled by the NAMEMATCH
parameters. For more information, see Parameters and Functions Reference
for Oracle GoldenGate.
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
.