14 Customizing Oracle GoldenGate Processing

This chapter describes how to customize Oracle GoldenGate processing.

Topics:

• Executing Commands, Stored Procedures, and Queries with SQLEXEC
  
• Using Oracle GoldenGate Macros to Simplify and Automate Work
  
• Using User Exits to Extend Oracle GoldenGate Capabilities
  
• Using the Oracle GoldenGate Event Marker System to Raise Database Events
  

14.1 Executing Commands, Stored Procedures, and Queries with SQLEXEC

The SQLEXEC parameter of Oracle GoldenGate enables Extract and Replicat to communicate with the database to do the following:

• Execute a database command, stored procedure, or SQL query to perform a database function, return results (SELECT statements) or perform DML (INSERT, UPDATE, DELETE) operations.

• Retrieve output parameters from a procedure for input to a FILTER or COLMAP clause.

Note:

SQLEXEC provides minimal globalization support. To use SQLEXEC in the capture parameter file of the source capture, make sure that the client character set in the source .prm file is either the same or a superset of the source database character set.

• Performing Processing with SQLEXEC
  
• Using SQLEXEC
  
• Executing SQLEXEC within a TABLE or MAP Statement
  
• Executing SQLEXEC as a Standalone Statement
  
• Using Input and Output Parameters
  
• Handling SQLEXEC Errors
  
• Additional SQLEXEC Guidelines
  

14.1.1 Performing Processing with SQLEXEC

SQLEXEC extends the functionality of both Oracle GoldenGate and the database by allowing Oracle GoldenGate to use the native SQL of the database to execute custom processing instructions.

• Stored procedures and queries can be used to select or insert data into the database, to aggregate data, to denormalize or normalize data, or to perform any other function that requires database operations as input. Oracle GoldenGate supports stored procedures that accept input and those that produce output.

• Database commands can be issued to perform database functions required to facilitate Oracle GoldenGate processing, such as disabling triggers on target tables and then enabling them again.

14.1.2 Using SQLEXEC

The SQLEXEC parameter can be used as follows:

• as a clause of a TABLE or MAP statement

• as a standalone parameter at the root level of the Extract or Replicat parameter file.

14.1.3 Executing SQLEXEC within a TABLE or MAP Statement

When used within a TABLE or MAP statement, SQLEXEC can pass and accept parameters. It can be used for procedures and queries, but not for database commands.

Syntax

This syntax executes a procedure within a TABLE or MAP statement.

SQLEXEC (SPNAME sp_name,
[ID logical_name,]
{PARAMS param_spec | NOPARAMS})

Argument	Description
SPNAME	Required keyword that begins a clause to execute a stored procedure.
sp_name	Specifies the name of the stored procedure to execute.
ID logical_name	Defines a logical name for the procedure. Use this option to execute the procedure multiple times within a TABLE or MAP statement. Not required when executing a procedure only once.
PARAMS param_spec | NOPARAMS	Specifies whether or not the procedure accepts parameters. One of these options must be used (see Using Input and Output Parameters).

Syntax

This syntax executes a query within a TABLE or MAP statement.

SQLEXEC (ID logical_name, QUERY ' query ',
{PARAMS param_spec | NOPARAMS})

Argument	Description
ID logical_name	Defines a logical name for the query. A logical name is required in order to extract values from the query results. ID logical_name references the column values returned by the query.
QUERY ' sql_query '	Specifies the SQL query syntax to execute against the database. It can either return results with a SELECT statement or change the database with an INSERT, UPDATE, or DELETE statement. The query must be within single quotes and must be contained all on one line. Specify case-sensitive object names the way they are stored in the database, such as within quotes for Oracle case-sensitive names. SQLEXEC 'SELECT "col1" from "schema"."table"'
PARAMS param_spec | NOPARAMS	Defines whether or not the query accepts parameters. One of these options must be used (see Using Input and Output Parameters).

If you want to execute a query on a table residing on a different database than the current database, then the different database name has to be specified with the table. The delimiter between the database name and the tablename should be a colon (:). The following are some example use cases:

select col1 from db1:tab1
select col2 from db2:schema2.tab2
select col3 from tab3
select col3 from schema4.tab4

14.1.4 Executing SQLEXEC as a Standalone Statement

When used as a standalone parameter statement in the Extract or Replicat parameter file, SQLEXEC can execute a stored procedure, query, or database command. As such, it need not be tied to any specific table and can be used to perform general SQL operations. For example, if the Oracle GoldenGate database user account is configured to time-out when idle, you could use SQLEXEC to execute a query at a defined interval, so that Oracle GoldenGate does not appear idle. As another example, you could use SQLEXEC to issue an essential database command, such as to disable target triggers. A standalone SQLEXEC statement cannot accept input parameters or return output parameters.

Parameter syntax	Purpose
SQLEXEC 'call procedure_name()'	Execute a stored procedure
SQLEXEC 'sql_query'	Execute a query
SQLEXEC 'database_command'	Execute a database command

Argument	Description
'call procedure_name ()'	Specifies the name of a stored procedure to execute. The statement must be enclosed within single quotes. Example: SQLEXEC 'call prc_job_count ()'
'sql_query'	Specifies the name of a query to execute. The query must be contained all on one line and enclosed within single quotes. Specify case-sensitive object names the way they are stored in the database, such as within double quotes for Oracle object names that are case-sensitive. SQLEXEC 'SELECT "col1" from "schema"."table"'
'database_command'	Specifies a database command to execute. Must be a valid command for the database.

SQLEXEC provides options to control processing behavior, memory usage, and error handling. For more information, see Reference for Oracle GoldenGate.

14.1.5 Using Input and Output Parameters

Oracle GoldenGate provides options for passing input and output values to and from a procedure or query that is executed with SQLEXEC within a TABLE or MAP statement.

• Passing Values to Input Parameters
  
• Passing Values to Output Parameters
  
• SQLEXEC Examples Using Parameters
  

14.1.5.1 Passing Values to Input Parameters

To pass data values to input parameters within a stored procedure or query, use the PARAMS option of SQLEXEC.

Syntax

PARAMS ([OPTIONAL | REQUIRED] param = {source_column | function}
[, ...] )

Where:

• OPTIONAL indicates that a parameter value is not required for the SQL to execute. If a required source column is missing from the database operation, or if a column-conversion function cannot complete successfully because a source column is missing, the SQL executes anyway.

• REQUIRED indicates that a parameter value must be present. If the parameter value is not present, the SQL will not be executed.

• param is one of the following:

  • For a stored procedure, it is the name of any parameter in the procedure that can accept input, such as a column in a lookup table.

  • For an Oracle query, it is the name of any input parameter in the query excluding the leading colon. For example, :param1 would be specified as param1 in the PARAMS clause.

  • For a non-Oracle query, it is pn, where n is the number of the parameter within the statement, starting from 1. For example, in a query with two parameters, the param entries are p1 and p2.

• {source_column | function} is the column or Oracle GoldenGate conversion function that provides input to the procedure.

14.1.5.2 Passing Values to Output Parameters

To pass values from a stored procedure or query as input to a FILTER or COLMAP clause, use the following syntax:

Syntax

{procedure_name | logical_name}.parameter

Where:

• procedure_name is the actual name of the stored procedure. Use this argument only if executing a procedure one time during the life of the current Oracle GoldenGate process.

• logical_name is the logical name specified with the ID option of SQLEXEC. Use this argument if executing a query or a stored procedure that will be executed multiple times.

• parameter is either the name of the parameter or RETURN_VALUE, if extracting returned values.

14.1.5.3 SQLEXEC Examples Using Parameters

These examples use stored procedures and queries with input and output parameters.

Note:

Additional SQLEXEC options are available for use when a procedure or query includes parametes. See the full SQLEXEC documentation in Reference for Oracle GoldenGate.

Example 14-1 SQLEXEC with a Stored Procedure

This example uses SQLEXEC to run a stored procedure named LOOKUP that performs a query to return a description based on a code. It then maps the results to a target column named NEWACCT_VAL.

CREATE OR REPLACE PROCEDURE LOOKUP
(CODE_PARAM IN VARCHAR2, DESC_PARAM OUT VARCHAR2)
BEGIN
    SELECT DESC_COL
    INTO DESC_PARAM
    FROM LOOKUP_TABLE
    WHERE CODE_COL = CODE_PARAM
END;

Contents of MAP statement:

MAP sales.account, TARGET sales.newacct, &
  SQLEXEC (SPNAME lookup, PARAMS (code_param = account_code)), &
    COLMAP (newacct_id = account_id, newacct_val = lookup.desc_param);

SQLEXEC executes the LOOKUP stored procedure. Within the SQLEXEC clause, the PARAMS (code_param = account_code) statement identifies code_param as the procedure parameter to accept input from the account_code column in the account table.

Replicat executes the LOOKUP stored procedure prior to executing the column map, so that the COLMAP clause can extract and map the results to the newacct_val column.

Example 14-2 SQLEXEC with a Query

This example implements the same logic as used in the previous example, but it executes a SQL query instead of a stored procedure and uses the @GETVAL function in the column map.

A query must be on one line. To split an Oracle GoldenGate parameter statement into multiple lines, an ampersand (&) line terminator is required.

Query for an Oracle database:

MAP sales.account, TARGET sales.newacct, &
SQLEXEC (ID lookup, &
QUERY 'select desc_col desc_param from lookup_table where code_col = :code_param', &
PARAMS (code_param = account_code)), &
COLMAP (newacct_id = account_id, newacct_val = &
@getval (lookup.desc_param));

Query for a non-Oracle database:

MAP sales.account, TARGET sales.newacct, &
SQLEXEC (ID lookup, &
QUERY 'select desc_col desc_param from lookup_table where code_col = ?', &
PARAMS (p1 = account_code)), &
COLMAP (newacct_id = account_id, newacct_val = &
@getval (lookup.desc_param));

14.1.6 Handling SQLEXEC Errors

There are two types of error conditions to consider when implementing SQLEXEC:

• The column map requires a column that is missing from the source database operation. This can occur for an update operation if the database only logs the values of columns that changed, rather than all of the column values. By default, when a required column is missing, or when an Oracle GoldenGate column-conversion function results in a "column missing" condition, the stored procedure does not execute. Subsequent attempts to extract an output parameter from the stored procedure results in a "column missing condition" in the COLMAP or FILTER clause.

• The database generates an error.

• Handling Missing Column Values
  
• Handling Database Errors
  

14.1.6.1 Handling Missing Column Values

Use the @COLTEST function to test the results of the parameter that was passed, and then map an alternative value for the column to compensate for missing values, if desired. Otherwise, to ensure that column values are available, you can use the FETCHCOLS or FETCHCOLSEXCEPT option of the TABLE parameter to fetch the values from the database if they are not present in the log. As an alternative to fetching columns, you can enable supplemental logging for those columns.

14.1.6.2 Handling Database Errors

Use the ERROR option in the SQLEXEC clause to direct Oracle GoldenGate to respond in one of the following ways:

Table 14-1 ERROR Options

Action	Description
IGNORE	Causes Oracle GoldenGate to ignore all errors associated with the stored procedure or query and continue processing. Any resulting parameter extraction results in a "column missing" condition. This is the default.
REPORT	Ensures that all errors associated with the stored procedure or query are reported to the discard file. The report is useful for tracing the cause of the error. It includes both an error description and the value of the parameters passed to and from the procedure or query. Oracle GoldenGate continues processing after reporting the error.
RAISE	Handles errors according to rules set by a REPERROR parameter specified in the Replicat parameter file. Oracle GoldenGate continues processing other stored procedures or queries associated with the current TABLE or MAP statement before processing the error.
FINAL	Performs in a similar way to RAISE except that when an error associated with a procedure or query is encountered, any remaining stored procedures and queries are bypassed. Error processing is called immediately after the error.
FATAL	Causes Oracle GoldenGate to abend immediately upon encountering an error associated with a procedure or query.

14.1.7 Additional SQLEXEC Guidelines

Observe the following SQLEXEC guidelines:

• Up to 20 stored procedures or queries can be executed per TABLE or MAP entry. They execute in the order listed in the parameter statement.

• A database login by the Oracle GoldenGate user must precede the SQLEXEC clause. Use the SOURCEDB and/or USERID or USERIDALIAS parameter in the Extract parameter file or the TARGETDB and/or USERID or USERIDALIAS parameter in the Replicat parameter file, as needed for the database type and configured authentication method.

• The SQL is executed by the Oracle GoldenGate user. This user must have the privilege to execute stored procedures and call RDBM-supplied procedures.

• Database operations within a stored procedure or query are committed in same context as the original transaction.

• Do not use SQLEXEC to update the value of a primary key column. If SQLEXEC is used to update the value of a key column, then the Replicat process will not be able to perform a subsequent update or delete operation, because the original key value will be unavailable. If a key value must be changed, you can map the original key value to another column and then specify that column with the KEYCOLS option of the TABLE or MAP parameter.

• For DB2, Oracle GoldenGate uses the ODBC SQLExecDirect function to execute a SQL statement dynamically. This means that the connected database server must be able to prepare the statement dynamically. ODBC prepares the SQL statement every time it is executed (at the requested interval). Typically, this does not present a problem to Oracle GoldenGate users. See the IBM DB2 documentation for more information.

• Do not use SQLEXEC for objects being processing by a data-pump Extract in pass-through mode.

• All object names in a SQLEXEC statement must be fully qualified with their two-part or three-part names, as appropriate for the database.

• All objects that are affected by a SQLEXEC stored procedure or query must exist with the correct structures prior to the execution of the SQL. Consequently, DDL on these objects that affects structure (such as CREATE or ALTER) must happen before the SQLEXEC executes.

• All objects affected by a standalone SQLEXEC statement must exist before the Oracle GoldenGate processes start. Because of this, DDL support must be disabled for those objects; otherwise, DDL operations could change the structure or delete the object before the SQLEXEC procedure or query executes on it.

14.2 Using Oracle GoldenGate Macros to Simplify and Automate Work

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

Do not use macros to manipulate data for tables that are being processed by a data-pump Extract in pass-through mode.

There are two steps to using macros:

Defining a Macro

Calling a Macro

Topics:

• Defining a Macro
  
• Calling a Macro
  
• Calling Other Macros from a Macro
  
• Creating Macro Libraries
  
• Tracing Macro Expansion
  

14.2.1 Defining 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 14-2 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 Calling 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;

14.2.2 Calling a Macro

This section shows you how to call a macro. (To define a macro, see Defining 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 14-3 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 (). For more information about this syntax, see the following: Calling a Macro that Contains Parameters. Calling a Macro without Input Parameters.
( 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 (). For more information about this syntax, see the following: Calling a Macro that Contains Parameters. Calling a Macro without Input Parameters.

• Calling a Macro that Contains Parameters
  
• Calling a Macro without Input Parameters
  

14.2.2.1 Calling 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. See the syntax in Table 14-3.

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 14-3 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 14-4 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)
);

14.2.2.2 Calling 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;

14.2.3 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)

14.2.4 Creating 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

1. Open a new file in a text editor.

2. Use commented lines to describe the library, if needed.

3. Using the syntax described in Defining a Macro, enter the syntax for each macro.

4. 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

14.2.5 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

14.3 Using User Exits to Extend Oracle GoldenGate Capabilities

User exits are custom routines that you write in C programming code and call during Extract or Replicat processing. User exits extend and customize the functionality of the Extract and Replicat processes with minimal complexity and risk. With user exits, you can respond to database events when they occur, without altering production programs.

Topics:

• When to Implement User Exits
  
• Making Oracle GoldenGate Record Information Available to the Routine
  
• Creating User Exits
  
• Supporting Character-set Conversion in User Exits
  
• Using Macros to Check Name Metadata
  
• Describing the Character Format
  
• Upgrading User Exits
  
• Viewing Examples of How to Use the User Exit Functions
  

14.3.1 When to Implement User Exits

You can employ user exits as an alternative to, or in conjunction with, the column-conversion functions that are available within Oracle GoldenGate. User exits can be a better alternative to the built-in functions because a user exit processes data only once (when the data is extracted) rather than twice (once when the data is extracted and once to perform the transformation).

The following are some ways in which you can implement user exits:

• Perform arithmetic operations, date conversions, or table lookups while mapping from one table to another.

• Implement record archival functions offline.

• Respond to unusual database events in custom ways, for example by sending an e-mail message or a page based on an output value.

• Accumulate totals and gather statistics.

• Manipulate a record.

• Repair invalid data.

• Calculate the net difference in a record before and after an update.

• Accept or reject records for extraction or replication based on complex criteria.

• Normalize a database during conversion.

14.3.2 Making Oracle GoldenGate Record Information Available to the Routine

The basis for most user exit processing is the EXIT_CALL_PROCESS_RECORD function. For Extract, this function is called just before a record buffer is output to the trail. For Replicat, it is called just before a record is applied to the target. If source-target mapping is specified in the parameter file, the EXIT_CALL_PROCESS_RECORD event takes place after the mapping is performed.

When EXIT_CALL_PROCESS_RECORD is called, the record buffer and other record information are available to it through callback routines. The user exit can map, transform, clean, or perform any other operation with the data record. When it is finished, the user exit can return a status indicating whether the record should be processed or ignored by Extract or Replicat.

14.3.3 Creating User Exits

The following instructions help you to create user exits on Windows and UNIX systems. For more information about the parameters and functions that are described in these instructions, see Reference for Oracle GoldenGate for Windows and UNIX.

Note:

User exits are case-sensitive for database object names. Names are returned exactly as they are defined in the hosting database. Object names must be fully qualified.

To Create User Exits

1. In C code, create either a shared object (UNIX systems) or a DLL (Windows) and create or export a routine to be called from Extract or Replicat. This routine is the communication point between Oracle GoldenGate and your routines. Name the routine whatever you want. The routine must accept the following Oracle GoldenGate user exit parameters:

   • EXIT_CALL_TYPE: Indicates when, during processing, the routine is called.

   • EXIT_CALL_RESULT: Provides a response to the routine.

   • EXIT_PARAMS: Supplies information to the routine. This function enables you to use the EXITPARAM option of the TABLE or MAP statement to pass a parameter that is a literal string to the user exit. This is only valid during the exit call to process a specific record. This function also enables you to pass parameters specified with the PARAMS option of the CUSEREXIT parameter at the exit call startup.

2. In the source code, include the usrdecs.h file. The usrdecs.h file is the include file for the user exit API. It contains type definitions, return status values, callback function codes, and a number of other definitions. The usrdecs.h file is installed within the Oracle GoldenGate directory. Do not modify this file.

3. Include Oracle GoldenGate callback routines in the user exit when applicable. Callback routines retrieve record and application context information, and they modify the contents of data records. To implement a callback routine, use the ERCALLBACK function in the shared object. The user callback routine behaves differently based on the function code that is passed to the callback routine.

   ERCALLBACK (function_code, buffer, result_code);
   

   Where:

   • function_code is the function to be executed by the callback routine.

   • buffer is a void pointer to a buffer containing a predefined structure associated with the specified function code.

   • result_code is the status of the function that is executed by the callback routine. The result code that is returned by the callback routine indicates whether or not the callback function was successful.

   • On Windows systems, Extract and Replicat export the ERCALLBACK function that is to be called from the user exit routine. The user exit must explicitly load the callback function at run-time using the appropriate Windows API calls.

4. Include the CUSEREXIT parameter in your Extract or Replicat parameter file. This parameter accepts the name of the shared object or DLL and the name of the exported routine that is to be called from Extract or Replicat. You can specify the full path of the shared object or DLL or let the operating system's standard search strategy locate the shared object.

   CUSEREXIT {DLL | shared_object} routine
   [, INCLUDEUPDATEBEFORES]
   [, PARAMS 'startup_string']
   

   Where:

   • DLL is a Windows DLL and shared_object is a UNIX shared object that contains the user exit function.

   • INCLUDEUPDATEBEFORES gets before images for UPDATE operations.

   • PARAMS 'startup_string' supplies a startup string, such as a startup parameter.

Example 14-5 Example of Base Syntax, UNIX

CUSEREXIT eruserexit.so MyUserExit

Example 14-6 Example Base Syntax, Windows

CUSEREXIT eruserexit.dll MyUserExit

14.3.4 Supporting Character-set Conversion in User Exits

To maintain data integrity, a user exit needs to understand the character set of the character-type data that it exchanges with an Oracle GoldenGate process. Oracle GoldenGate user exit logic provides globalization support for:

• character-based database metadata, such as the names of catalogs, schemas, tables, and columns

• the values of character-type columns, such as CHAR, VARCHAR2, CLOB, NCHAR, NVARCHAR2, and NCLOB, as well as string-based numbers, date-time, and intervals.

Properly converting between character sets allows column data to be compared, manipulated, converted, and mapped properly from one type of database and character set to another. Most of this processing is performed when the EXIT_CALL_PROCESS_RECORD call type is called and the record buffer and other record information is made available through callback routines.

The user exit has its own session character set. This is defined by the GET_SESSION_CHARSET and SET_SESSION_CHARSET callback functions. The caller process provides conversion between character sets if the character set of the user exit is different from the hosting context of the process.

To enable this support in user exits, there is the GET_DATABASE_METADATA callback function code. This function enables the user exit to get database metadata, such as the locale and the character set of the character-type data that it exchanges with the process that calls it (Extract, data pump, Replicat). It also returns how the database treats the case-sensitivity of object names, how it treats quoted and unquoted names, and how it stores object names.

For more information about these components, see Reference for Oracle GoldenGate for Windows and UNIX.

14.3.5 Using Macros to Check Name Metadata

The object name that is passed by the user exit API is the exact name that is encoded in the user-exit session character set, and exactly the same name that is retrieved from the database. If the user exit compares the object name with a literal string, the user exit must retrieve the database locale and then normalize the string so that it is compared with the object name in the same encoding.

Oracle GoldenGate provides the following macros that can be called by the user exit to check the metadata of database object names. For example, a macro can be used to check whether a quoted table name is case-sensitive and whether it is stored as mixed-case in the database server. These macros are defined in the usrdecs.h file.

Table 14-4 Macros for metadata checking

Macro	What it verifies
supportsMixedCaseIdentifiers( nameMeta, DbObjType )	Whether the database treats a mixed-case unquoted name of a specified data type as case-sensitive and stores the name in mixed case.
supportsMixedCaseQuotedIdentifiers( nameMeta, DBObjType )	Whether the database treats the mixed-case quoted name of a specified data type as case-sensitive and stores the name in mixed case.
storesLowerCaseIdentifiers( nameMeta, DbObjType )	Whether the database treats the mixed-case unquoted name of a specified data type as case-insensitive and stores the name in lower case.
storesLowerCaseQuotedIdentifiers( nameMeta, DbObjType )	Whether the database treats the mixed-case quoted name of a specified data type as case-insensitive and stores the name in lower case.
storesMixedCaseIdentifiers( nameMeta, DbObjType )	Whether the database treats the mixed-case unquoted name of a specified data type as case-insensitive and stores the name in mixed case.
storesMixedCaseQuotedIdentifiers( nameMeta, DbObjType )	Whether the database treats the mixed-case quoted name of a specified data type as case-insensitive and stores the name in mixed case.
storesUpperCaseIdentifiers( nameMeta, DbObjType )	Whether the database treats the mixed-case unquoted name of a specified data type as case-insensitive and stores the name in upper case.
storesUpperCaseQuotedIdentifiers( nameMeta, DbObjType )	Whether the database treats the mixed-case quoted name of a specified data type as case-insensitive and stores the name in upper case.

14.3.6 Describing the Character Format

The input parameter column_value_mode describes the character format of the data that is being processed and is used in several of the function codes. The following table describes the meaning of the EXIT_FN_RAW_FORMAT, EXIT_FN_CHAR_FORMAT, and EXIT_FN_CNVTED_SESS_FORMAT format codes, per data type.

Table 14-5 column_value_mode_matrix Meanings

Data Type	EXIT_FN_RAW_FORMAT	EXIT_FN_CHAR_FORMAT	EXIT_FN_CNVTED_SESS_FORMAT
CHAR "abc"	2-byte null indicator + 2-byte length info + column value 0000 0004 61 62 63 20	"abc" encoded in ASCII or EBCDIC. NULL terminated. Tailing spaces are trimmed.	"abc" encoded in user exit session character set. NOT NULL terminated. Tailing spaces are trimmed by default unless the GLOBALS parameter NOTRIMSPACES is specified.
NCHAR 0061 0062 0063 0020	2-byte null indicator + 2-byte length info + column value. 0000 0008 00 61 0062 0063 0020	"abc" (encoded in UTF8) or truncated at the first byte, depending on whether NCHAR is treated as UTF-8. NULL terminated. Trailing spaces are trimmed.	"abc" encoded in user exit session character set. NOT NULL terminated. Tailing spaces are trimmed by default unless the GLOBALS parameter NOTRIMSPACES is specified.
VARCHAR2 "abc"	2-byte null indicator + 2-byte length info + column value	"abc" encoded in ASCII or EBCDIC. NULL terminated. No trimming.	"abc" encoded in user exit session character set. NOT NULL terminated. No trimming.
NVARCHAR2 0061 0062 0063 0020	2-byte null indicator + 2-byte length info + column value	"abc" (encoded in UTF8) or truncated at the first byte, depending on whether NVARCHAR2 is treated as UTF-8. NULL terminated. No trimming.	"abc"encoded in user exit session character set. NOT NULL terminated. No trimming.
CLOB	2-byte null indicator + 2-byte length info + column value	Similar to VARCHAR2, but only output up to 4K bytes. NULL Terminated. No trimming.	Similar to VARCHAR2, but only output data requested in user exit session character set. NOT NULL terminated. No trimming.
NCLOB	2-byte null indicator + 2-byte length info + column value	Similar to NVARCHAR2, but only output up to 4K bytes. NULL terminated. No trimming.	Similar to NVARCHAR2, but only output data requested in user exit session character set. NOT NULL terminated. No trimming.
NUMBER 123.89	2-byte null indicator + 2-byte length info + column value	"123.89" encoded in ASCII or EBCDIC. NULL terminated.	"123.89" encoded in user exit session character set. NOT NULL terminated.
DATE 31-May-11	2-byte null indicator + 2-byte length info + column value	"2011-05-31" encoded in ASCII or EBCDIC. NULL terminated.	"2011-05-31" encoded in user exit session character set. NOT NULL terminated.
TIMESTAMP 31-May-11 12.00.00 AM	2-byte null indicator + 2-byte length info + column value	"2011-05-31 12.00.00 AM" encoded in ASCII or EBCDIC. NULL terminated.	"2011-05-31 12.00.00 AM" encoded in user exit session character set. NOT NULL terminated.
Interval Year to Month or Interval Day to Second	2-byte null indicator + 2-byte length info + column value	NA	NA
RAW	2-byte null indicator + 2-byte length info + column value	2-byte null indicator + 2-byte length info + column value	2-byte null indicator + 2-byte length info + column value

14.3.7 Upgrading User Exits

The usrdecs.h file is versioned to allow backward compatibility with existing user exits when enhancements or upgrades, such as new functions or structural changes, are added to a new Oracle GoldenGate release. The version of the usrdecs.h file is printed in the report file at the startup of Replicat or Extract.

To use new user exit functionality, you must recompile your routines to include the new usrdecs file. Routines that do not use new features do not need to be recompiled.

14.3.8 Viewing Examples of How to Use the User Exit Functions

Oracle GoldenGate installs the following sample user exit files into the UserExitExamples directory of the Oracle GoldenGate installation directory:

• exitdemo.c shows how to initialize the user exit, issue callbacks at given exit points, and modify data. It also demonstrates how to retrieve the fully qualified table name or a specific metadata part, such as the name of the catalog or container, or the schema, or just the unqualified table name. In addition, this demo shows how to process DDL data. The demo is not specific to any database type.

• exitdemo_utf16.c shows how to use UTF16-encoded data (both metadata and column data) in the callback structures for information exchanged between the user exit and the caller process.

• exitdemo_more_recs.c shows an example of how to use the same input record multiple times to generate several target records.

• exitdemo_lob.c shows an example of how to get read access to LOB data.

• exitdemo_pk_befores.c shows how to access the before and after image portions of a primary key update record, as well as the before images of regular updates (non-key updates). It also shows how to get target row values with SQLEXEC in the Replicat parameter file as a means for conflict detection. The resulting fetched values from the target are mapped as the target record when it enters the user exit.

Each directory contains the *.c files as well as makefiles and a readme.txt file.

14.4 Using the Oracle GoldenGate Event Marker System to Raise Database Events

Oracle GoldenGate provides an event marker system, also known as the event marker infrastructure (EMI), which enables the Oracle GoldenGate processes to take a defined action based on an event record in the transaction log or in the trail (depending on the data source of the process). The event record is a record that satisfies a specific filter criterion for which you want an action to occur. You can use this system to customize Oracle GoldenGate processing based on database events.

For example, you can use the event marker system to start, suspend, or stop a process, to perform a transformation, or to report statistics. The event marker system can be put to use for purposes such as:

• To establish a synchronization point at which SQLEXEC or user exit functions can be performed

• To execute a shell command that executes a data validation script or sends an email

• To activate tracing when a specific account number is detected

• To capture lag history

• To stop or suspend a process to run reports or batch processes at the end of the day

The event marker feature is supported for the replication of data changes, but not for initial loads.

The system requires the following input components:

1. The event record that triggers the action can be specified with FILTER, WHERE, or SQLEXEC in a TABLE or MAP statement. Alternatively, a special TABLE statement in a Replicat parameter file enables you to perform EVENTACTIONS actions without mapping a source table to a target table.

2. In the TABLE or MAP statement where you specify the event record, include the EVENTACTIONS parameter with the appropriate option to specify the action that is to be taken by the process.

You can combine EVENTACTIONS options, as shown in the following examples.

The following causes the process to issue a checkpoint, log an informational message, and ignore the entire transaction (without processing any of it), plus generate a report.

EVENTACTIONS (CP BEFORE, REPORT, LOG, IGNORE TRANSACTION)

The following writes the event record to the discard file and ignores the entire transaction.

EVENTACTIONS (DISCARD, IGNORE TRANS)

The following logs an informational message and gracefully stop the process.

EVENTACTIONS (LOG INFO, STOP)

The following rolls over the trail file and does not write the event record to the new file.

EVENTACTIONS (ROLLOVER, IGNORE)

For syntax details and additional usage instructions, see Reference for Oracle GoldenGate.

• Case Studies in the Usage of the Event Marker System
  

14.4.1 Case Studies in the Usage of the Event Marker System

These examples highlight some use cases for the event marker system.

Topics:

• Trigger End-of-day Processing
  
• Simplify Transition from Initial Load to Change Synchronization
  
• Stop Processing When Data Anomalies are Encountered
  
• Trace a Specific Order Number
  
• Execute a Batch Process
  
• Propagate Only a SQL Statement without the Resultant Operations
  
• Committing Other Transactions Before Starting a Long-running Transaction
  
• Execute a Shell Script to Validate Data
  

14.4.1.1 Trigger End-of-day Processing

This example specifies the capture of operations that are performed on a special table named event_table in the source database. This table exists solely for the purpose of receiving inserts at a predetermined time, for example at 5:00 P.M. every day. When Replicat receives the transaction record for this operation, it stops gracefully to allow operators to start end-of-day processing jobs. By using the insert on the event_table table every day, the operators know that Replicat has applied all committed transactions up to 5:00. IGNORE causes Replicat to ignore the event record itself, because it has no purpose in the target database. LOG INFO causes Replicat to log an informational message about the operation.

TABLE source.event_table, EVENTACTIONS (IGNORE, LOG INFO, STOP);

14.4.1.2 Simplify Transition from Initial Load to Change Synchronization

Event actions and event tables can be used to help with the transition from an initial load to ongoing change replication. For example, suppose an existing, populated source table must be added to the Oracle GoldenGate configuration. This table must be created on the target, and then the two must be synchronized by using an export/import. This example assumes that an event table named source.event_table exists in the source database and is specified in a Replicat TABLE statement.

TABLE source.event_table, EVENTACTIONS (IGNORE, LOG INFO, STOP);

To allow users to continue working with the new source table, it is added to the Extract parameter file, but not to the Replicat parameter file. Extract begins capturing data from this table to the trail, where it is stored.

At the point where the source and target are read-consistent after the export, an event record is inserted into the event table on the source, which propagates to the target. When Replicat receives the event record (marking the read-consistent point), the process stops as directed by EVENTACTIONS STOP. This allows the new table to be added to the Replicat MAP statement. Replicat can be positioned to start replication from the timestamp of the event record, eliminating the need to use the HANDLECOLLISIONS parameter. Operations in the trail from before the event record can be ignored because it is known that they were applied in the export.

The event record itself is ignored by Replicat, but an informational message is logged.

14.4.1.3 Stop Processing When Data Anomalies are Encountered

This example uses ABORT to stop Replicat immediately with a fatal error if an anomaly is detected in a bank record, where the customer withdraws more money than the account contains. In this case, the source table is mapped to a target table in a Replicat MAP statement for actual replication to the target. A TABLE statement is also used for the source table, so that the ABORT action stops Replicat before it applies the anomaly to the target database. ABORT takes precedence over processing the record.

MAP source.account, TARGET target.account;
TABLE source.account, FILTER (withdrawal > balance), EVENTACTIONS (ABORT);

14.4.1.4 Trace a Specific Order Number

The following example enables Replicat tracing only for an order transaction that contains an insert operation for a specific order number (order_no = 1). The trace information is written to the order_1.trc trace file. The MAP parameter specifies the mapping of the source table to the target table.

MAP sales.order, TARGET rpt.order;
TABLE source.order,
FILTER (@GETENV ('GGHEADER', 'OPTYPE') = 'INSERT' AND order_no = 1), &
EVENTACTIONS (TRACE order_1.trc TRANSACTION);

14.4.1.5 Execute a Batch Process

In this example, a batch process executes once a month to clear the source database of accumulated data. At the beginning of the transaction, typically a batch transaction, a record is written to a special job table to indicate that the batch job is starting. TRANSACTION is used with IGNORE to specify that the entire transaction must be ignored by Extract, because the target system does not need to reflect the deleted records. By ignoring the work on the Extract side, unnecessary trail and network overhead is eliminated.

TABLE source.job, FILTER (@streq (job_type = 'HOUSEKEEPING')=1), &
EVENTACTIONS (IGNORE TRANSACTION);

Note:

If a logical batch delete were to be composed of multiple smaller batches, each smaller batch would require an insert into the job table as the first record in the transaction.

14.4.1.6 Propagate Only a SQL Statement without the Resultant Operations

This example shows how different EVENTACTIONS clauses can be used in combination on the source and target to replicate just a SQL statement rather than the operations that result from that statement. In this case, it is an INSERT INTO...SELECT transaction. Such a transaction could generate millions of rows that would need to be propagated, but with this method, all that is propagated is the initial SQL statement to reduce trail and network overhead. The SELECTs are all performed on the target. This configuration requires perfectly synchronized source and target tables in order to maintain data integrity.

Extract:

TABLE source.statement, EVENTACTIONS (IGNORE TRANS INCLUDEEVENT);

Replicat:

TABLE source.statement, SQLEXEC (execute SQL statement), &
EVENTACTIONS (INFO, IGNORE);

To use this configuration, a statement table is populated with the first operation in the transaction, that being the INSERT INTO...SELECT, which becomes the event record.

Note:

For large SQL statements, the statement can be written to multiple columns in the table. For example, eight VARCHAR (4000) columns could be used to store SQL statements up to 32 KB in length.

Because of the IGNORE TRANS INCLUDEEVENT, Extract ignores all of the subsequent inserts that are associated with the SELECT portion of the statement, but writes the event record that contains the SQL text to the trail. Using a TABLE statement, Replicat passes the event record to a SQLEXEC statement that concatenates the SQL text columns, if necessary, and executes the INSERT INTO...SELECT statement using the target tables as the input for the SELECT sub-query.

14.4.1.7 Committing Other Transactions Before Starting a Long-running Transaction

This use of EVENTACTIONS ensures that all open transactions that are being processed by Replicat get committed to the target before the start of a long running transaction. It forces Replicat to write a checkpoint before beginning work on the large transaction. Forcing a checkpoint constrains any potential recovery to just the long running transaction. Because a Replicat checkpoint implies a commit to the database, it frees any outstanding locks and makes the pending changes visible to other sessions.

TABLE source.batch_table, EVENTACTIONS (CHECKPOINT BEFORE);

14.4.1.8 Execute a Shell Script to Validate Data

This example executes a shell script that runs another script that validates data after Replicat applies the last transaction in a test run. On the source, an event record is written to an event table named source.event. The record inserts the value COMPARE into the event_type column of the event table, and this record gets replicated at the end of the other test data. In the TABLE statement in the Replicat parameter file, the FILTER clause qualifies the record and then triggers the shell script compare_db.sh to run as specified by SHELL in the EVENTACTIONS clause. After that, Replicat stops immediately as specified by FORCESTOP.

Extract:

TABLE src.*;
TABLE test.event;

Replicat:

MAP src.*, TARGET targ.*;
MAP test.event, TARGET test.event, FILTER (@streq (event_type, 'COMPARE')=1), &
EVENTACTIONS (SHELL 'compare_db.sh', FORCESTOP);