DDLSUBST
Valid For
Extract and Replicat
Description
Use the DDLSUBST
parameter to substitute strings in a DDL operation. For example, you could substitute one table name for another or substitute a string within comments. The search is not case-sensitive. To represent a quotation mark in a string, use a double quote mark.
Guidelines for Using DDLSUBST
-
Do not use
DDLSUBST
to convert column names and data types to something different on the target. Changing the structure of a target object in this manner will cause errors when data is replicated to it. Likewise, do not useDDLSUBST
to change owner and table names in a target DDL statement. Always use aMAP
statement to map a replicated DDL operation to a different target object. -
DDLSUBST
always executes after theDDL
parameter, regardless of their relative order in the parameter file. Because the filtering executes first, use filtering criteria that is compatible with the criteria that you are using for string substitution. For example, consider the following parameter statements:DDL INCLUDE OBJNAME fin.* DDLSUBST 'cust' WITH 'customers' INCLUDE OBJNAME sales.*
In this example, no substitution occurs because the objects in the
INCLUDE
andDDLSUBST
statements are different. Thefin
-owned objects are included in the Oracle GoldenGate DDL configuration, but thesales
-owned objects are not. -
You can use multiple
DDLSUBST
parameters. They execute in the order listed in the parameter file. -
For Oracle DDL that includes comments, do not use the
DDLOPTIONS
parameter with theREMOVECOMMENTS BEFORE
option if you will be doing string substitution on those comments.REMOVECOMMENTS BEFORE
removes comments before string substitution occurs. To remove comments, but allow string substitution, use theREMOVECOMMENTS AFTER
option. -
There is no maximum string size for substitutions, other than the limit that is imposed by the database. If the string size exceeds the database limit, the Extract or Replicat process that is executing the operation abends.
Default
No substitution
Syntax
DDLSUBST 'search_string' WITH 'replace_string' [INCLUDE inclusion_clause | EXCLUDE exclusion_clause]
-
'search_string'
-
The string in the source DDL statement that you want to replace. Enclose the string within single quote marks. To represent a quotation mark in a string, use a double quotation mark.
-
WITH
-
Required keyword.
-
'replace_string'
-
The string that you want to use as the replacement in the target DDL. Enclose the string within single quote marks. To represent a quotation mark in a string, use a double quotation mark.
-
INCLUDE
inclusion_clause
| EXCLUDE
exclusion_clause
-
Specifies one or more
INCLUDE
andEXCLUDE
statements to filter the DDL operations for which the string substitution rules are applied. See "DDL Filtering Options" for syntax and usage.
Examples
- Example 1
-
The following replaces the string
cust
with the stringcustomers
for tables in thefin
schema.DDLSUBST 'cust' WITH 'customers' INCLUDE ALL OBJTYPE 'table' OBJNAME fin.*
- Example 2
-
The following substitutes a new directory only if the
DDL
command includes the wordlogfile
. If the search string is found multiple times, the replacement string is inserted multiple times.DDLSUBST '/file1/location1' WITH '/file2/location2' INCLUDE INSTR 'logfile'
- Example 3
-
The following uses multiple
DDLSUBST
statements, which execute in the order shown.DDLSUBST 'a' WITH 'b' INCLUDE ALL DDLSUBST 'b' WITH 'c' INCLUDE ALL
The net effect of the preceding substitutes all
a
andb
strings withc
.