1. Microservices Architecture Documentation
  2. Administer
  3. Simplify and Automate Work with Oracle GoldenGate Macros
  4. Call a Macro
  5. Call a Macro that Contains Parameters

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.

  1. The macro processor reads through the macro body looking for instances of parameter names specified in the PARAMS statement.

  2. For each occurrence of the parameter name, the corresponding parameter value specified during the call is substituted.

  3. 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-39 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-40 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)
);