Simplify and Automate Work with Oracle GoldenGate Macros

You can use Oracle GoldenGate macros in parameter files to configure and reuse parameters, commands, and conversion functions. reducing the amount of text you must enter to do common tasks. A macro is a built-in automation tool that enables you to call a stored set of processing steps from within the Oracle GoldenGate parameter file. A macro can consist of a simple set of frequently used parameter statements to a complex series of parameter substitutions, calculations, or conversions. You can call other macros from a macro. You can store commonly used macros in a library, and then call the library rather than call the macros individually.

Oracle GoldenGate macros work with the following parameter files:

DEFGEN
Extract
Replicat

There are two steps to using macros:

Defining a Macro
Calling a Macro

Define a Macro

To define an Oracle GoldenGate macro, use the MACRO parameter in the parameter file. MACRO defines any input parameters that are needed and it defines the work that the macro performs.

Syntax

MACRO #macro_name
PARAMS (#p1, #p2 [, ...])
BEGIN
macro_body
END;

Table 11-19 Macro Definition Arguments

Argument	Description
`MACRO`	Required. Indicates the start of an Oracle GoldenGate macro definition.
`#macro_name`	The name of the macro. Macro and parameter names must begin with a macro character. The default macro character is the pound (#) character, as in `#macro1` and `#param1`. A macro or parameter name can be one word consisting of letters and numbers, or both. Special characters, such as the underscore character (`_`) or hyphen `(-`), can be used. Some examples of macro names are: `#mymacro`, `#macro1`, `#macro_1`, `#macro-1`, `#macro$`. Some examples of parameter names are `#sourcecol`, `#s`, `#col1`, and `#col_1`. To avoid parsing errors, the macro character cannot be used as the first character of a macro name. For example, `##macro` is invalid. If needed, you can change the macro character by using the `MACROCHAR` parameter. See Reference for Oracle GoldenGate for Windows and UNIX. Macro and parameter names are not case-sensitive. Macro or parameter names within quotation marks are ignored.
PARAMS (#`p1`, #`p2`)	Optional definition of input parameters. Specify a comma-separated list of parameter names and enclose it within parentheses. Each parameter must be referenced in the macro body where you want input values to be substituted. You can list each parameter on a separate line to improve readability (making certain to use the open and close parentheses to enclose the parameter list). See Call a Macro that Contains Parameters for more information.
BEGIN	Begins the macro body. Must be specified before the macro body.
`macro_body`	The macro body. The body is a syntax statement that defines the function that is to be performed by the macro. A macro body can include any of the following types of statements. Simple parameter statements, as in: COL1 = COL2 Complex parameter statements with parameter substitution as in: MAP #o.#t, TARGET #o.#t, KEYCOLS (#k), COLMAP (USEDEFAULTS); Invocations of other macros, as in: #colmap (COL1, #sourcecol)
`END;`	Ends the macro definition. The semicolon is required to complete the definition.

The following is an example of a macro definition that includes parameters. In this case, the macro simplifies the task of object and column mapping by supplying the base syntax of the MAP statement with input parameters that resolve to the names of the owners, the tables, and the KEYCOLS columns.

MACRO #macro1  
PARAMS ( #o, #t, #k )  
BEGIN  
MAP #o.#t, TARGET #o.#t, KEYCOLS (#k), COLMAP (USEDEFAULTS); 
END;

The following is an example of a macro that does not define parameters. It executes a frequently used set of parameters.

MACRO #option_defaults
BEGIN
GETINSERTS
GETUPDATES
GETDELETES
INSERTDELETES
END;

Call a Macro

To call a macro, use the following syntax where you want the macro to run within the parameter file.

Syntax

[target =] macro_name (val[, ...])

[target =] macro_name (val | {val, val, ...}[, ...])

Table 11-20 Syntax Elements for Calling a Macro

Argument	Description
`target =`	Optional. Specifies the target to which the results of the macro are assigned or mapped. For example, `target` can be used to specify a target column in a `COLMAP` statement. In the following call to the `#make_date` macro, the column `DATECOL1` is the target and will be mapped to the macro results. `DATECOL1 = #make_date (YR1, MO1, DAY1)` Without a target, the syntax to call `#make_date` is: `#make_date (YR1, MO1, DAY1)`
`macro_name`	The name of the macro that is being called, for example: `#make_date`.
`(` `val[, ...]`)	The parameter input values. This component is required whether or not the macro defines parameters. If the macro defines parameters, specify a comma-separated list of input values, in the order that corresponds to the parameter definitions in the `MACRO` parameter, and enclose the list within parentheses. If the macro does not define parameters, specify the open and close parentheses with nothing between them ().
`(` `val` `\| {val,` `val, ...} )[, ...]`	The parameter input values. This component is required whether or not the macro defines parameters. If the macro defines parameters, specify a comma-separated list of input values, in the order that corresponds to the parameter definitions in the `MACRO` parameter, and enclose the list within parentheses. To pass multiple values to one parameter, separate them with commas and enclose the list within curly brackets. If the macro does not define parameters, specify the open and close parentheses with nothing between them ().

See the following topics to learn more about syntax for calling a macro:

Call a Macro that Contains Parameters

To call a macro that contains parameters, the call statement must supply the input values that are to be substituted for those parameters when the macro runs.

Valid input for a macro parameter is any of the following, preceded by the macro character (default is #):

A single value in plain or quoted text, such as: #macro (#name, #address, #phone) or #macro (#"name", #"address", #"phone").
A comma-separated list of values enclosed within curly brackets, such as: #macro1 (SCOTT, DEPT, {DEPTNO1, DEPTNO2, DEPTNO3}). The ability to substitute a block of values for any given parameter add flexibility to the macro definition and its usability in the Oracle GoldenGate configuration.
Calls to other macros, such as: #macro (#mycalc (col2, 100), #total). In this example, the #mycalc macro is called with the input values of col2 and 100.

Oracle GoldenGate substitutes parameter values within the macro body according to the following rules.

The macro processor reads through the macro body looking for instances of parameter names specified in the PARAMS statement.
For each occurrence of the parameter name, the corresponding parameter value specified during the call is substituted.
If a parameter name does not appear in the PARAMS statement, the macro processor evaluates whether or not the item is, instead, a call to another macro. (See Calling Other Macros from a Macro.) If the call succeeds, the nested macro is executed. If it fails, the whole macro fails.

Example 11-40 Using Parameters to Populate a MAP Statement

The following macro definition specifies three parameter that must be resolved. The parameters substitute for the names of the table owner (parameter #o), the table (parameter #t), and the KEYCOLS columns (parameter #k) in a MAP statement.

MACRO #macro1  PARAMS ( #o, #t, #k )  BEGIN  MAP #o.#t, TARGET #o.#t, KEYCOLS (#k), COLMAP (USEDEFAULTS); END;

Assuming a table in the MAP statement requires only one KEYCOLS column, the following syntax can be used to call #macro1. In this syntax, the #k parameter can be resolved with only one value.

#macro1 (SCOTT, DEPT, DEPTNO1)

To call the macro for a table that requires two KEYCOLS columns, the curly brackets are used as follows to enclose both of the required values for the column names:

#macro1 (SCOTT, DEPT, {DEPTNO1, DEPTNO2})

The DEPTNO1 and DEPTNO2 values are passed as one argument to resolve the #t parameter. Tables with three or more KEYCOLS can also be handled in this manner, using additional values inside the curly brackets.

Example 11-41 Using a Macro to Perform Conversion

In this example, a macro defines the parameters #year, #month, and #day to convert a proprietary date format.

MACRO #make_date
PARAMS (#year, #month, #day)
BEGIN
@DATE ('YYYY-MM-DD', 'CC', @IF (#year < 50, 20, 19), 'YY', #year, 'MM', #month, 'DD', #day)
END;

The macro is called in the COLMAP clause:

MAP sales.acct_tab, TARGET sales.account,
COLMAP
(
targcol1 = sourcecol1,
datecol1 = #make_date(YR1, MO1, DAY1),
datecol2 = #make_date(YR2, MO2, DAY2)
);

The macro expands as follows:

MAP sales.acct_tab, TARGET sales.account,
COLMAP
(
targcol1 = sourcecol1,
datecol1 = @DATE ('YYYY-MM-DD', 'CC', @IF (YR1 < 50, 20, 19),'YY', YR1, 'MM', MO1, 'DD', DAY1),
datecol2 = @DATE ('YYYY-MM-DD', 'CC', @IF (YR2 < 50, 20, 19),'YY', YR2, 'MM', MO2, 'DD', DAY2)
);

Call a Macro without Input Parameters

To call a macro without input parameters, the call statement must supply the open and close parentheses, but without any input values: #macro ().

The following macro is defined without input parameters. The body contains frequently used parameters.

MACRO #option_defaults
BEGIN
GETINSERTS
GETUPDATES
GETDELETES
INSERTDELETES
END;

This macro is called as follows:

#option_defaults ()
IGNOREUPDATES
MAP owner.srctab, TARGET owner.targtab;

#option_defaults ()
MAP owner.srctab2, TARGET owner.targtab2;

The macro expands as follows:

GETINSERTS
GETUPDATES
GETDELETES
INSERTDELETES
IGNOREUPDATES
MAP owner.srctab, TARGET owner.targtab;

GETINSERTS
GETUPDATES
GETDELETES
INSERTDELETES
MAP owner.srctab2, TARGET owner.targtab2;

Calling Other Macros from a Macro

To call other macros from a macro, create a macro definition similar to the following. In this example, the #make_date macro is nested within the #assign_date macro, and it is called when #assign_date runs.

The nested macro must define all, or a subset of, the same parameters that are defined in the base macro. In other words, the input values when the base macro is called must resolve to the parameters in both macros.

The following defines #assign_date:

MACRO #assign_date
PARAMS (#target_col, #year, #month, #day)
BEGIN
#target_col = #make_date (#year, #month, #day)
END;

The following defines #make_date. This macro creates a date format that includes a four-digit year, after first determining whether the two-digit input date should be prefixed with a century value of 19 or 20. Notice that the PARAMS statement of #make_date contains a subset of the parameters in the #assign_date macro.

MACRO #make_date
PARAMS (#year, #month, #day)
BEGIN
@DATE ('YYYY-MM-DD', 'CC', @IF (#year < 50, 20, 19), 'YY', #year, 'MM', #month, 'DD', #day)
END;

The following syntax calls #assign_date:

#assign_date (COL1, YEAR, MONTH, DAY)

The macro expands to the following given the preceding input values and the embedded #make_date macro:

COL1 = @DATE ('YYYY-MM-DD', 'CC', @IF (YEAR < 50, 20, 19),'YY', YEAR, 'MM', MONTH, 'DD', DAY)

Create Macro Libraries

You can create a macro library that contains one or more macros. By using a macro library, you can define a macro once and then use it within many parameter files.

To Create a Macro Library

Open a new file in a text editor.
Use commented lines to describe the library, if needed.
Use the following syntax to define each macro:
```
MACRO #macro_name
PARAMS (#p1, #p2 [, ...])
BEGIN
macro_body
END;
```
Save the file in the dirprm sub-directory of the Oracle GoldenGate directory as:
```
filename.mac
```
Where:

filename is the name of the file. The .mac extension defines the file as a macro library.

The following sample library named datelib contains two macros, #make_date and #assign_date.

-- datelib macro library
--
MACRO #make_date
PARAMS (#year, #month, #day)
BEGIN
@DATE ('YYYY-MM-DD', 'CC', @IF (#year < 50, 20, 19), 'YY', #year, 'MM', #month, 'DD', #day)
END;

MACRO #assign_date
PARAMS (#target_col, #year, #month, #day)
BEGIN
#target_col = #make_date (#year, #month, #day)
END;

To use a macro library, use the INCLUDE parameter at the beginning of a parameter file, as shown in the following sample Replicat parameter file.

INCLUDE /ggs/dirprm/datelib.mac
REPLICAT rep
ASSUMETARGETDEFS
USERIDALIAS ogg
MAP fin.acct_tab, TARGET fin.account;

When including a long macro library in a parameter file, you can use the NOLIST parameter to suppress the listing of each macro in the Extract or Replicat report file. Listing can be turned on and off by placing the LIST and NOLIST parameters anywhere within the parameter file or within the macro library file. In the following example, NOLIST suppresses the listing of each macro in the hugelib macro library. Specifying LIST after the INCLUDE statement restores normal listing to the report file.

NOLIST
INCLUDE /ggs/dirprm/hugelib.mac
LIST
INCLUDE /ggs/dirprm/mdatelib.mac
REPLICAT REP

Tracing Macro Expansion

You can trace macro expansion with the CMDTRACE parameter. With CMDTRACE enabled, macro expansion steps are shown in the Extract or Replicat report file.

Syntax

CMDTRACE [ON | OFF | DETAIL]

Where:

ON enables tracing.
OFF disables tracing.
DETAIL produces a verbose display of macro expansion.

In the following example, tracing is enabled before #testmac is called, then disabled after the macro's execution.

REPLICAT REP
MACRO #testmac
BEGIN
COL1 = COL2,
COL3 = COL4,
END;
...
CMDTRACE ON
MAP test.table1, TARGET test.table2,
COLMAP (#testmac);
CMDTRACE OFF