OBDUMPER can preprocess data to be exported based on predefined control files.
File template
Template for defining a control file:
lang=java
(
Column name "Preprocess function",
Column name "Preprocess function",
Column name "Preprocess function",
);
Notice
- A control file is named in the format of
- For more information about preprocessing functions, see Preprocessing functions.
Usage notes
If the column name defined in the table contains database keywords, you must add a grave accent (`) both before and after the column name to escape the column name. Example:
lang=java
(
`WHEN` "lower(`WHEN`)",
c2 "nanvl(c2,'0')"
);
Examples
Export data in the CUT format. Example:
lang=java
(
c1 "lower(c1)", -- Convert letters of values in Column c1 to lowercase.
c2 "ltrim(c2)", -- Truncate leading spaces of values in Column c2.
c3 "rtrim(c3)", -- Truncate trailing spaces of values in Column c3.
c4 "substr(c4,0,5)", -- Extract a substring of five characters from values in Column c4. The extraction starts from the first byte of each value.
c5 "trim(c5)", -- Truncate leading and trailing spaces of values in Column c5.
c6 "upper(c6)", -- Convert letters of values in Column c6 to uppercase.
c7 "nanvl(c7,'0')", -- Verify values in Column c7 and return 0 for non-numeric values.
c8 "replace(c8,'a','A')", -- Replace Letter 'a' of values in Column c8 with Letter 'A'.
c9 "nvl(c9,'nill')", -- Verify whether values in Column c9 are null and return nill for null values.
c10 "to_timestamp(c10,'yyyyMMddHHmmssSSS')", -- Convert values in Column c10 to the yyyy-MM-dd HH:mm:ss.SSS format. Return null if formatting fails.
c11 "length(c11)", -- Calculate the length of values in Column c11.
c12 "lpad(c12,5,'x')", -- Append a string of five 'x' to the left of values in Column c12.
c13 "rpad(c13,5,'x')", -- Append a string of five 'x' to the right of values in Column c13.
c14 "convert(c14,'utf-8','gbk')", -- Convert the character set of values in Column c14 from GBK to UTF-8.
c15 "concat(c15, '_suffix')", -- Concatenate values in Column c15 with a specific constant.
c16 "none", -- Do not process values in Column c16. Return the values in the corresponding column directly.
c17 "systimestamp", -- Do not process values in Column c17. Return the timestamp of the current cluster directly.
c18 "constant('1')", -- Do not process values in Column c18. Return a constant 1.
c19 "tmsfmt(c19,'yyyyMMddHHmmssSSS','20210310000000000','yyyyMMddHHmmssSSS')", -- Verify the dates of values in Column c19. If the verification fails, return the default value.
c20 "lpadb(c20,5,'x')", -- Append five single-byte 'x' to the left of values in Column c20.
c21 "rpadb(c21,5,'x')", -- Append five single-byte 'x' to the right of values in Column c21.
c22 "case when length(trim(c22))<18 then 'Y' else 'N' end", - Check whether the values in Column c22 meet the specified condition. If yes, return the values in the corresponding column.
c23 "case length(trim(c23)) when '1' then 'one' when '2' then 'two' else 'unknown' end", -- Check whether the values in Column c23 are equal to the specified value. If yes, return the values in the corresponding column.
c24 "sysdate", -- Set values in Column c24 to the current date.
c25 "sequence(100,1)" -- Generate an incremental sequence for Column c25, with 100 as the start value and 1 as the increment step.
c26 "reverse(c26)", -- Reverse characters in the values of Column c26.
c27 "mask("c27", 'A','a','b')", -- Mask the values of Column c27 by replacing uppercase letters with A, lowercase letters with a, and numbers with b.
c28 "mask_first_n(c28, 'A','a','b', 5)", -- Mask the first five characters in the values of Column c28 by replacing uppercase letters with A, lowercase letters with a, and numbers with b.
c29 "mask_last_n(c29, 'A','a','b', 5)", -- Mask the last five characters in the values of Column c29 by replacing uppercase letters with A, lowercase letters with a, and numbers with b.
c30 "mask_show_first_n(c30, 'A','a','b', 5)", -- Mask all characters except the first five characters in the values of Column c30 by replacing uppercase letters with A, lowercase letters with a, and numbers with b.
c31 "mask_show_last_n(c31, 'A','a','b', 5)" -- Mask all characters except the last five characters in the values of Column c31 by replacing uppercase letters with A, lowercase letters with a, and numbers with b.
)
Note
For more information about the functions, see the function list in Preprocessing functions.