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 use DDLSUBST
to change owner and table names in a target DDL statement. Always use a MAP
statement to map a replicated DDL operation to a different target object.
DDLSUBST
always executes after the DDL
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
and DDLSUBST
statements are different. The fin
-owned objects are included in the Oracle GoldenGate DDL configuration, but the sales
-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 the REMOVECOMMENTS 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 the REMOVECOMMENTS 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
and EXCLUDE
statements to filter the DDL operations for which the string substitution rules are applied. See "DDL Filtering Options" for syntax and usage.
Examples
The following replaces the string cust
with the string customers
for tables in the fin
schema.
DDLSUBST 'cust' WITH 'customers' INCLUDE ALL OBJTYPE 'table' OBJNAME fin.*
The following substitutes a new directory only if the DDL
command includes the word logfile
. If the search string is found multiple times, the replacement string is inserted multiple times.
DDLSUBST '/file1/location1' WITH '/file2/location2' INCLUDE INSTR 'logfile'
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
and b
strings with c
.