1. Parameters and Functions Reference Guide
  2. User Exit Functions
  3. GET_COLUMN_VALUE_FROM_NAME

GET_COLUMN_VALUE_FROM_NAME

Valid For

Extract and Replicat

Description

Use the GET_COLUMN_VALUE_FROM_NAME function to retrieve the column value from the data record by using the specified column name. Column values are the basis for most logic within the user exit. You can base complex logic on the values of individual columns within the data record.

If the character session of the user exit is set with SET_SESSION_CHARSET to a value other than the default character set of the operating system, as defined in ULIB_CS_DEFAULT in the ucharset.h file, the character data that is exchanged between the user exit and the process is interpreted in the session character set.

A column value is set to the session character set only if the following is true:

  • The column value is a SQL character type (CHAR/VARCHAR2/CLOB, NCHAR/NVARCHAR2/NCLOB), a SQL date/timestamp/interval/number type)

  • The column_value_mode indicator is set to EXIT_FN_CNVTED_SESS_CHAR_FORMAT.

If the database is case-sensitive, object names must be specified in the same letter case as they are defined in the hosting database; otherwise, the case does not matter.

Syntax

#include "usrdecs.h"
short result_code;
column_def column;
ERCALLBACK (GET_COLUMN_VALUE_FROM_NAME, &column, &result_code);

Buffer

typedef struct
{
char *column_value;
unsigned short max_value_length;
unsigned short actual_value_length;
short null_value;
short remove_column;
short value_truncated;
short column_index;
char *column_name;
/* Version 3 CALLBACK_STRUCT_VERSION */
short column_value_mode;
short source_or_target;
/* Version 2 CALLBACK_STRUCT_VERSION */
char requesting_before_after_ind;
char more_lob_data;
/* Version 3 CALLBACK_STRUCT_VERSION  */
ULibCharSet column_charset;
} column_def;

Input

column_value

A pointer to a buffer to accept the returned column value.

max_value_length

The maximum length of the returned column value. Typically, the maximum length is the length of the column value buffer. If ASCII format is specified (see column_value_mode) the column value is null-terminated, and the maximum length should equal the maximum length of the column value.

column_name

The name of the column for the column value to be returned.

column_value_mode

Indicates the character set of the column value.

EXIT_FN_CHAR_FORMAT

ASCII format: The value is a null-terminated ASCII (or EBCDIC) string (with a known exception for the sub-data type UTF16_BE, which is converted to UTF8.)

Note:

A column value might be truncated when presented to a user exit, because the value is interpreted as an ASCII string and is supposed to be null-terminated. The first value of 0 becomes the string terminator.

  • Dates are in the format CCYY-MM-DD HH:MI:SS.FFFFFF, in which the fractional time is database-dependent.

  • Numeric values are in their string format. For example, 123.45 is represented as "123.45".

  • Non-printable characters or binary values are converted to hexadecimal notation.

  • Floating point types are output as null-terminated strings, to the first 14 significant digits.

EXIT_FN_RAW_FORMAT

Internal Oracle GoldenGate canonical format: This format includes a two-byte null indicator and a two-byte variable data length when applicable. No character-set conversion is performed by Oracle GoldenGate for this format for any character data type.

EXIT_FN_CNVTED_SESS_CHAR_FORMAT

User exit character set: This only applies if the column data type is:

  • a character-based type, single or multi-byte

  • a numeric type with a string representation

This format is not null-terminated.

source_or_target

One of the following indicating whether to use the source or target data record to retrieve the column value.

EXIT_FN_SOURCE_VAL
EXIT_FN_TARGET_VAL
requesting_before_after_ind

Set when processing an after image record and you want the before columns of either an update or a primary key update.

To get the "before" value of the column while processing an "after image" of a primary key update or a regular (non-key) update record, set the requesting_before_after_ind flag to BEFORE_IMAGE_VAL.

  • To access the before image of the key columns of a primary key update, nothing else is necessary.

  • To access non-key columns of a primary key update or any column of a regular update, the before image must be available.

The default setting is AFTER_IMAGE_VAL (get the after image of the column) when an explicit input for requesting_before_after_ind is not specified.

To make a before image available, you can use the GETUPDATEBEFORES parameter or you can use the INCLUDEUPDATEBEFORES option within the CUSEREXIT parameter statement.

Note that:

  • GETUPDATEBEFORES causes an Extract process to write before-image records to the trail and also to make an EXIT_CALL_PROCESS_RECORD call to the user exit with the before images.

  • INCLUDEUPDATEBEFORES does not cause an EXIT_CALL_PROCESS_RECORD call to the user exit nor, in the case of Extract, does it cause the process to write the before image to the trail.

Output

column_value

A pointer to the returned column value. If column_value_mode is specified as EXIT_FN_CHAR_FORMAT, the column value is returned as a null-terminated ASCII string; otherwise, the column value is returned in the Oracle GoldenGate internal canonical format. In ASCII format, dates are returned in the following format: 

CCYY-MM-DD HH:MI:SS.FFFFFF

The inclusion of fractional time is database-dependent.

actual length

The string length of the returned column name. The actual length does not include a null terminator when column_value_mode is specified as EXIT_FN_CHAR_FORMAT.

null_value

A flag (0 or 1) indicating whether or not the column value is null. If the null_value flag is 1, then the column value buffer is filled with null bytes.

value_truncated

A flag (0 or 1) indicating whether or not the value was truncated. Truncation occurs if the length of the column value exceeds the maximum buffer length. If column_value_mode was specified as EXIT_FN_CHAR_FORMAT, the null terminator is included in the length of the column.

char more_lob_data

A flag that indicates if more LOB data is present beyond the initial 4K that can be stored in the base record. When a LOB is larger than the 4K limit, it is stored in LOB fragments.

You must allocate the appropriate amount of memory to contain the returned values. Oracle GoldenGate will access LOB columns up to 8K of data at all times, filling up the buffer to the amount that the user exit has allocated. If the LOB is larger than that which was allocated, subsequent callbacks are required to obtain the total column data, until all data has been sent to the user exit.

To determine the end of the data, evaluate more_lob_data. The user exit sets this flag to either CAR_NO_VAL or CHAR_YES_VAL before accessing a new column. If this flag is still initialized after first callback and is not set to either CHAR_YES_VAL or CAR_NO_VAL, then one of the following is true:

  • Enough memory was allocated to handle the LOB.

  • It is not a LOB.

  • It was not over the 4K limit of the base trail record size.

It is recommended that you obtain the source table metadata to determine if a column might be a LOB.

Return Values

EXIT_FN_RET_BAD_COLUMN_DATA
EXIT_FN_RET_COLUMN_NOT_FOUND
EXIT_FN_RET_INVALID_COLUMN
EXIT_FN_RET_INVALID_CONTEXT
EXIT_FN_RET_INVALID_PARAM
EXIT_FN_RET_OK

Example

memset (&col_meta, 0, sizeof(col_meta));
if (record.mapped)
col_meta.source_or_target = EXIT_FN_TARGET_VAL;
else
col_meta.source_or_target = EXIT_FN_SOURCE_VAL;
col_meta.source_or_target = EXIT_FN_SOURCE_VAL;
col_meta.column_name = (char *)malloc(100);
col_meta.max_name_length = 100;
col_meta.column_index = 1;

call_callback (GET_COL_METADATA_FROM_NAME, &col_meta, &result_code);