1. Microservices Architecture Documentation
  2. Administer
  3. Admin Client Command Line Interface for Oracle GoldenGate Microservices
  4. Specifying Object Names in Oracle GoldenGate Input
  5. Using Wildcards in Database Object Names

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 TABn, where n is from 0 to 9, a wildcard specification of HQ.TAB? returns HQ.TAB0, HQ.TAB1, HQ.TAB2, and so on, up to HQ.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 a TABLE or MAP parameter. It cannot be used to specify target objects in the TARGET clause of TABLE or MAP.

  • 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. This wildcard is valid for all database types throughout all Oracle GoldenGate commands and parameters where a wildcard is allowed.

  • In TABLE and MAP 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-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 hr."abc*";
TABLE hr."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 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: 

TABLE HQ.T_*, TARGET RPT.T_*;
MAP HQ.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_TESTn. The following illustrates the incorrect results:

  • HQ.T_TEST1 maps to RPT.T_T_TEST1

  • HQ.T_TEST2 maps to RPT.T_T_TEST2

  • (The same pattern applies to all other HQ.T_TESTn 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 to RPT.T_TEST1

  • HQ.T_TEST2 maps to RPT.T_TEST2

  • (The same pattern applies to all other HQ.T_TESTn 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.

Excluding Objects from a Wildcard Specification

You can combine the use of wildcard object selection with explicit object exclusion by using the EXCLUDEWILDCARDOBJECTSONLY, CATALOGEXCLUDE, SCHEMAEXCLUDE, MAPEXCLUDE, and TABLEEXCLUDE parameters.