3.20 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 toEXIT_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 toBEFORE_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 forrequesting_before_after_ind
is not specified.To make a before image available, you can use the
GETUPDATEBEFORES
parameter or you can use theINCLUDEUPDATEBEFORES
option within theCUSEREXIT
parameter statement.Note that:
-
GETUPDATEBEFORES
causes an Extract process to write before-image records to the trail and also to make anEXIT_CALL_PROCESS_RECORD
call to the user exit with the before images. -
INCLUDEUPDATEBEFORES
does not cause anEXIT_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 asEXIT_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 asEXIT_FN_CHAR_FORMAT
. -
null_value
-
A flag (
0
or1
) indicating whether or not the column value is null. If thenull_value
flag is1
, then the column value buffer is filled with null bytes. -
value_truncated
-
A flag (
0
or1
) indicating whether or not the value was truncated. Truncation occurs if the length of the column value exceeds the maximum buffer length. Ifcolumn_value_mode
was specified asEXIT_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 eitherCAR_NO_VAL
orCHAR_YES_VAL
before accessing a new column. If this flag is still initialized after first callback and is not set to eitherCHAR_YES_VAL
orCAR_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);
Parent topic: User Exit Functions