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.

Note:

If you use CUSEREXITS, the LD_LIBRARY_PATH environment variable needs to be extended. By default, the $OGG_HOME/lib directory is part of the Oracle GoldenGate software Home directory. It is separated from the Deployment directory by design. If additional shared objects need to be added for User Exit functions, then it is recommended that you do not use the $OGG_HOME/lib directory and choose a different location. For CUSEREXITS, you must extend the LD_LIBRARY_PATH environment variable to a different location.

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.

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.

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

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.
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.
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.
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 9-41 Example of Base Syntax, UNIX

CUSEREXIT eruserexit.so MyUserExit

Example 9-42 Example Base Syntax, Windows

CUSEREXIT eruserexit.dll MyUserExit

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.

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 9-20 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.

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 9-21 column_value_mode_matrix Meanings

Data Type EXIT_FN_RAW_FORMAT EXIT_FN_CHAR_FORMAT EXIT_FN_CNVTED_SESS_FORMAT

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

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

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

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.

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.