You can define rules in a control file by which OBDUMPER can preprocess the data before the export.
File template
Template of a defined control file:
lang=java
(
Column name "Preprocess function",
Column name "Preprocess function",
Column name "Preprocess function",
);
The naming convention of the control file is table name. ctrl . The content of the control file requires that the order of column names is the same as that defined in the table. To export data in the--cut format, you do not need to define positions of byte offsets. For more information about the preprocess functions, see Preprocessing functions.
Note
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.
The following example shows how to escape the column name:
lang=java
(
`WHEN` "lower(`WHEN`)",
c2 "nanvl(c2,'0')"
);
Example
The following example shows a control file for exporting data in the --cut format:
lang=java
(
c1 "lower(c1)", -- Convert letters of values in Column c1 to lowercase.
c2 "ltrim(c2)", -- Trim leading spaces of values in Column c2.
c3 "rtrim(c3)", -- Trim 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)", -- Trim 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 encoding 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.
c17 "systimestamp", -- Do not process values in Column c17.
c18 "constant('1')", -- Do not process values in Column c18. Return a constant 1.
c19 "groovy('if(value==null){return "nill";}else{return value.toUpperCase();}')", -- Pass the values in Column c19 to a dynamic function for processing.
c20 "tmsfmt(c20,'yyyyMMddHHmmssSSS','20210310000000000','yyyyMMddHHmmssSSS')", -- Verify the dates of values in Column c20. If the verification fails, return the default value.
c21 "lpadb(c21,5,'x')", -- Append five single-byte 'x' to the left of values in Column c21.
c22 "rpadb(c22,5,'x')", -- Append five single-byte 'x' to the right of values in Column c22.
c23 "case when length(trim(c23))<18 then 'Y' else 'N' end", -- Verify whether values in Column c23 match the specified condition. If yes, return 'Y'.
c24 "case length(trim(c24)) when '1' then 'one' when '2' then 'two' else 'unknown' end", -- Verify whether values in Column c24 are equal to the specified value. If yes, return the corresponding value.
)