Preprocessing functions|V4.2.7|OceanBase Loader and Dumper|docs|OceanBase Documentations

You can configure a preprocessing function for each column when you define a control file.

Usage notes

For a preprocessing function configured for a column, only the values of the current column can be used as formal parameters. For example, c14 "concat(c15, '_suffix')" is not supported, and c15 "concat(c15, '_suffix')" is supported.
LPADB() and RPADB() are suitable for processing characters such as letters and numbers. It may be truncated when you use it to process emojis.
REPLACE() is used differently in Oracle and MySQL modes. It has the syntax that is similar to an Oracle function but is implemented like a MySQL function.
NVL() refers to the corresponding built-in function in MySQL. It distinguishes an empty character from NULL in implementation.
For preprocessing functions related to date and time such as SYSTIMESTAMP, TMSFMT(), and TO_TIMESTAMP(), the value is accurate only to milliseconds. In addition, the server clock must be the same as that of the database.
LPAD(char,length[,pad_string]) and RPAD(char,length[,pad_string]) refer to the corresponding built-in functions of MySQL. The length parameter indicates the total length of the return value that is displayed on the client. In most cases, it displays the actual number of characters in the return value. However, for some multibyte character sets, the displayed length of a string may differ from the actual length. Therefore, we recommend that you do not use this function to process multibyte values.

Functions

The following table describes the preprocessing functions supported in the current version.

Function signature	Return type	Description
LOWER(char)	String	Converts letters in a parameter value to lowercase letters. The parameter value can be a column name, constant, or function expression. If the parameter value is a constant, quote it with single quotation marks (' '). Example: `LOWER('A')`.
LTRIM(char[,set])	String	Matches and truncates a parameter value from left to right. The value of the `char` parameter can be a column name, a constant, or a function expression. The value of the `set` parameter is a constant. By default, if the `set` parameter is omitted, its value is replaced with a space. Example: `LTRIM(' abc ')`.
RTRIM(char[,set])	String	Matches and truncates a parameter value from right to left. The value of the `char` parameter can be a column name, constant, or function expression. The value of the `set` parameter is a constant. By default, if the `set` parameter is omitted, its value is replaced with a space. Example: `RTRIM(' abc ')`.
SUBSTR(char,position[,length])	String	Truncates a parameter value based on the specified start position and length. The value of the `char` parameter can be a column name, constant, or function expression. The values of the `position` and `length` parameters are numeric constants. Example: `SUBSTR('abc',0,3)`.
TRIM(char)	String	Truncates leading and trailing spaces of a parameter value. The value of the `char` parameter can be a column name, a constant, or a function expression. Example: `TRIM(' abc ')`.
REVERSE(char)	String	Reverses parameter values. Example: `REVERSE("C1")` reverses characters in each value of the C1 column.
UPPER(char)	String	Converts letters in a parameter value to uppercase letters. The value of the `char` parameter can be a column name, constant, or function expression. If the parameter value is a constant, quote it with single quotation marks (' '). Example: `UPPER('a')`.
NANVL(char, default)	String	Verifies whether a parameter value is a number. If not, the default value is returned. The value of the `char` parameter can be a column name, a constant, or a function expression. The value of the `default` parameter is a constant. Example: `NANVL('1','1')`.
REPLACE(char,search[,replacement])	String	Replaces a parameter value based on the specified search condition. The value of the `char` parameter can be a column name, constant, or function expression. The values of the `search` and `replacement` parameters are constants. Example: `REPLACE('abc','a','A')`.
NVL(char, default)	String	Verifies whether a parameter value is left empty. If yes, the default value is returned. The value of the `char` parameter can be a column name, a constant, or a function expression. The value of the `default` parameter is a constant. Example: `NVL('a','--')`.
TO_TIMESTAMP(char,fmt1[,fmt2])	String	Converts a parameter value into a date. If the conversion fails, `NULL` is returned. The value of the `char` parameter can be a column name, constant, or function expression. The value of the `fmt1` parameter is a date parsing template. The value of the optional `fmt2` parameter is a date formatting template. Default format of the return value of `fmt2`: yyyy-MM-dd HH:mm:ss.SSS.
LENGTH(char)	String	Calculates the length of a parameter value. The value of the `char` parameter can be a column name, a constant, or a function expression.
LPAD(char,length[,pad_string])	String	Appends a character string of the specified length to the left of a parameter value. If the specified length is smaller than the length of the parameter value, the parameter value is truncated. The value of the `char` parameter can be a column name, a constant, or a function expression. The values of the `length` and `pad_string` parameters are constants. In addition, the value of the `pad_string` parameter must be a single-byte character. Example: `LPAD('a',1,'x')`.
RPAD(char,length[,pad_string])	String	Appends a character string of the specified length to the right of a parameter value. If the specified length is smaller than the length of the parameter value, the parameter value is truncated. The value of the `char` parameter can be a column name, a constant, or a function expression. The values of the `length` and `pad_string` parameters are constants. In addition, the value of the `pad_string` parameter must be a single-byte character. Example: `RPAD('a',1,'x')`.
CONVERT(char,charset1[,charset2])	String	Converts the character set of a parameter value. The value of the `char` parameter can be a column name, constant, or function expression. The values of the `charset1` and `charset2` parameters are constants. Example: `CONVERT('a','utf-8','gbk')`.
CONCAT(char1,char2)	String	Concatenates parameter values. The values of the `char1` and `char2` parameters can be a column name, constant, or function expression. Example: `CONCAT('a','b')`.
NONE	String	Returns a parameter value without processing it.
SYSTIMESTAMP	String	Returns the timestamp of the current server without processing the parameter value. Default format: yyyy-MM-dd HH:mm:ss.SSS.
SYSDATE	String	Returns the date on the current server without processing the parameter value. Default format: yyyy-MM-dd HH:mm:ss.
CONSTANT(char)	String	Returns a defined constant value without processing it. The value of the `char` parameter is a constant. Example: `CONSTANT('1')`.
TMSFMT(char,fmt1,default,fmt2)	String	Validates the date format of a parameter. If the validation fails, the default date format is returned. Example: `TMSFMT(c1,'yyyyMMddHHmmssSSS','2021-03-10 00:00:00.000','yyyy-MM-dd HH:mm:ss.SSS')`.
LPADB(char,byte_size[,pad_char])	String	Appends a byte string of the specified length to the left of a parameter value. If the specified length is smaller than the length of the parameter value, the parameter value is truncated. The value of the `char` parameter can be a column name, a constant, or a function expression. The values of the `byte_size` and `pad_char` parameters are constants. Example: `LPADB('a',1,'x')`.
RPADB(char,byte_size[,pad_char])	String	Appends a byte string of the specified length to the right of a parameter value. If the specified length is smaller than the length of the parameter value, the parameter value is truncated. The value of the `char` parameter can be a column name, a constant, or a function expression. The values of the `byte_size` and `pad_char` parameters are constants. Example: `RPADB('a',1,'x')`.
MASK(string str[, string upper[, string lower[, string number]]])	String	Masks uppercase letters, lowercase letters, or numbers in a field value. Example: `MASK("C1",A,a,b)` masks the values of Column C1 by replacing uppercase letters with A, lowercase letters with a, and numbers with b.
MASK_FIRST_N(string str[, int n])	String	Masks the first n characters in a field value. Example: `MASK_FIRST_N("C1",'A','a','b',5)` masks the first five characters in the values of Column C1 by replacing uppercase letters with A, lowercase letters with a, and numbers with b.
MASK_LAST_N(string str[, int n])	String	Masks the last n characters in a field value. Example: `MASK_LAST_N("C1",'A','a','b',5)` masks the last five characters in the values of Column C1 by replacing uppercase letters with A, lowercase letters with a, and numbers with b.
MASK_SHOW_FIRST_N(string str[, int n])	String	Masks all characters except the first n characters in a field value. Example: `MASK_SHOW_FIRST_N("C1",'A','a','b',5)` masks all characters except the first five characters in the values of Column C1 by replacing uppercase letters with A, lowercase letters with a, and numbers with b.
MASK_SHOW_LAST_N(string str[, int n])	String	Masks all characters except the last n characters in a field value. Example: `MASK_SHOW_LAST_N("C1",'A','a','b',5)` masks all characters except the last five characters in the values of Column C1 by replacing uppercase letters with A, lowercase letters with a, and numbers with b.
SEQUENCE(int m,int n)	String	Generates an incremental sequence for a specified column. The parameter m specifies the start value and n specifies the increment step. Example: `SEQUENCE(100,1)` generates a sequence with a start value of 100 and an increment step of 1.
DB_SEQUENCE(string sequence)	String	Assigns sequence values to rows to be imported so that the rows can be imported into the database based on the sequence values. Example: `DB_SEQUENCE(seq01)` specifies tDuring performance tuning, take into account the resource utilization on the server where OBLOADER run, on the ODP service nodes, and on the nodes in the OceanBase cluster, especially the network of the ODP nodes.During performance tuning, take into account the resource utilization on the server where OBLOADER run, on the ODP service nodes, and on the nodes in the OceanBase cluster, especially the network of the ODP nodes.he sequence value `seq01`. Note This function is supported in OBLOADER V4.1.0 and later.

Notice

The groovy dynamic function is no longer supported in OBLOADER V3.x.