Chat now with support
Chat with Support

SharePlex 8.6.6 - Preinstallation Checklist

Use Wildcards to specify multiple objects

You can use the percent (%) and underscore (_) wildcard symbols to specify multiple objects of a schema in one entry of the configuration file. SharePlex replicates any objects that satisfy the wildcard, except those that you explicitly exclude.

Requirements and limitations of wildcard support

Supported wildcard syntax

SharePlex supports the following SQL wildcards

  • Percent (%) wildcard to specify a string. (See the Examples.)
  • Underscore (_) wildcard to specify a single-character.
  • For table names that contain a percent sign or an underscore character (for example emp_salary), SharePlex recognizes the backslash (\) as an escape character to denote the character as a literal, not a wildcard.

Specify wildcarded names in the configuration file

Use this template for help when specifying a wildcarded name in the configuration file.

Configuration with wildcarded object names
expand src_owner.wildcard_name [not (list)]


Description of syntax elements
Component Description

Indicates that the specification contains wildcard characters that must be expanded. When SharePlex detects the expand keyword, it queries the database for all objects that match the criteria in the wildcard specification. Without this required keyword, the wildcard characters are assumed to be part of an explicit object name, and no wildcard expansion is performed.

Note: Leave a space between expand and the start of the source object specification.

  • src_owner is the owner of the source objects. Owner names cannot be wildcarded. If wildcards are used in the owner name, SharePlex assumes that they are part of the owner (schema) name.
  • wildcard_name is the wildcarded name of the source objects.

The names of the target objects must be identical to those of the source objects, but the objects may belong to different owners.

not (list)

An exclusion list that defines objects to omit from the wildcard expansion. Use this option to exclude objects that you do not want to be replicated. Note: This not keyword does not have the same meaning as the SQL wildcard NOT operator.

  • The not keyword and parentheses are required elements.
  • list is a comma-separated list of tables owned by the same owner, either wildcarded or explicit. Example: not (spo%, gen%, product).

Leave a space before and after the not keyword. A space is allowed after each comma in the list.

Note: If an object that satisfies a wildcard is listed elsewhere in the configuration file, that entry overrides the processing or routing specified in the wildcarded entry. In this case, a not clause is not needed. See the Examples.

  • tgt_owner is the owner of the target objects.
  • wildcard_name is the wildcarded name of the target objects.

The target specification must be in the form of owner.%. Partially expanded target wildcarded names are not supported, such as

routing_map Any valid routing map. For more information, see Routing specifications in a configuration file.

Validate a wildcard specification

To confirm that a wildcard specification will produce the specific list of tables that you want to replicate, issue the verify config command in sp_ctrl before you activate the configuration. This command produces a list of the objects that SharePlex will capture and replicate, as well as any problems that occurred. For more information about this command, see the SharePlex Reference Guide.


Examples of valid wildcard specifications

Example 1: The following wildcard specification directs SharePlex to activate all tables owned by scott, where the table name is like prod% except if the table name is like %temp%. All tables that match this description are replicated to tables of the same names on the target in the hal schema. Note that SharePlex automatically upshifts the names, so that it actually activates all tables where the table name is like 'PROD%' but not like '%TEMP%'.

expand not (%temp%)    hal.%    sysa@o.sidB

Example 2: The following example shows how you can specify special handling for one of the tables in a wildcarded specification, in this case the photo table. All tables but photo are routed through the default post queue. The separate entry for the photo table overrides the wildcarded entry and processes the photo table through a named post queue. For more information, see Configure named post queues.

cust.%        cust.%        hostB@o.oraB    hostB:lobQ@o.oraB

The following are additional examples of valid wildcard specifications

expand scott.%test%    scott.%    sysa@o.sidB
expand scott.%t__t%    fred.%    sysa@o.sidB
expand scott.% not (spo%, gen%, prodct)    scott.%     sysa@o.sidB
expand not (%temp%)    hal.%    sysa@o.sidB

Examples of invalid wildcard specifications

The following example contains a wildcarded schema, which is not permitted.

expand rob%.%test%    scott.%    sysa@o.sidB

The following example contains a partially wildcarded target object name, which is not permitted.

expand scott.%test%    scott.%obj%    sysa@o.sidB
Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating