OBDUMPER can preprocess data to be exported based on predefined control files.
File template
Template of a defined control file:
lang=java
(
Column name "Preprocess function",
Column name "Preprocess function",
Column name "Preprocess function",
);
Notice
- The name of the control file is table name.ctrl.
- The order and case of columns defined in the control file must be consistent with that defined in the table.
- 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. Sample code:
lang=java
(
`WHEN` "lower(`WHEN`)",
c2 "nanvl(c2,'0')"
);
Example
Export data in the CUT format. Sample code:
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 value of the corresponding column.
c17 "systimestamp", -- Do not process values in Column c17. Return the timestamp of the current server without processing the parameter value.
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", -- Verify whether values in Column c22 match the specified condition. If yes, return 'Y'.
c23 "case length(trim(c23)) when '1' then 'one' when '2' then 'two' else 'unknown' end", -- Verify whether values in Column c23 are equal to the specified value. If yes, return the corresponding value.
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 .