MACRO

Valid for

Extract, Replicat

Description

Use MACRO to create a Oracle GoldenGate macro. For instructions on creating and using Oracle GoldenGate macros, see Using Oracle GoldenGate Macros.

The following syntax must be in the order shown and terminated with a semicolon (;).

Syntax

MACRO #macro_name
PARAMS (param1, param2 ,...)
BEGIN
macro_body
END;

macro_name

The name for the macro. Macro and parameter names are not case-sensitive.

Macro and parameter names must begin with a macro character. The default is the pound (#) character, as in #macro1 and #param1. Anything in the parameter file that begins with the macro character is assumed to be either a macro or a macro parameter.

You can change the macro character with the MACROCHAR parameter. Valid macro and parameter characters are alphanumeric and can include the underscore character (_). Parameter or macro names within quotation marks are treated as text and ignored.

PARAMS (param1, param2, ...)

Optional. Describes parameters to the macro. Parameter names are not case-sensitive.

Every parameter used in a macro must be declared in the PARAMS statement, and when the macro is invoked, the invocation must include a value for each parameter. By default, Oracle GoldenGate permits up to 30 parameters in a PARAMS clause, but you can change the default with the FUNCTIONSTACKSIZE parameter.

BEGIN

Begins the macro body. Must be specified before the macro body.

macro_body

Represents the macro body, which consists of one or more statements to be used as parameter file input. The macro body can include simple parameter statements such the first example below, and it can include complex statements like the second example. It also can include invocations of other macros, as in the third example.

Example 1: COL1 = COL2

Example 2: COL1 = #val2

Example 3: #colmap(COL1, #sourcecol)

END

Ends the macro definition.

;

(Semicolon) Marks the end of the macro parameter.

Examples

Example 1

The following example defines a macro that takes parameters.

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;

Example 2

The following example defines a macro that does not require parameters.

MACRO #option_defaults
BEGIN
GETINSERTS
GETUPDATES
GETDELETES
INSERTDELETES
END;

Example 3

The following example defines a macro that calls other macros.

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