Oracle Goldengate列转换样例

Goldengate提供的Column Conversion Functions中包括对应的num转str，str转num等函数，如下：

如果OGG提供的函数不能满足需求，我的想法是还能利用oracle本身的函数写对应的query语句来完成转换，比如
jy_date date
jy_date_str varchar2
MAP sales.account, TARGET sales.account, SQLEXEC (ID lookup, QUERY "select to_char(jy_date, 'yyyy-dd-mm') into target_col from account",
COLMAP (newacct_id = account_id, jy_date_str = lookup.target_col);

NUMSTR
Use the @NUMSTR function to convert a string (character) column or value into a number.
Use @NUMSTR to do either of the following:
● Map a string (character) to a number.
● Use a string column that contains only numbers in an arithmetic expression.
Syntax @NUMSTR ()
Example PAGE_NUM = @NUMSTR (ALPHA_PAGE_NO)

STRNUM
Use the @STRNUM function to convert a number into a string and specify the output format
and padding.
Syntax @STRNUM (, {LEFT | LEFTSPACE, | RIGHT | RIGHTZERO} [] )
Argument Description
The name of a string (character) column or a literal string. Enclose literals
within quotes.
The maximum string length, in characters.
Argument Description
The first string to be compared.
The second string to be compared.
The maximum number of characters in the string to compare.
Argument Description
The name of a source numeric column.
LEFT Left justify, without padding.
LEFTSPACE Left justify, fill the rest of the target column with spaces.
RIGHT Right justify, fill the rest of the target column with spaces. If the value of a column
is a negative value, the spaces are added before the minus sign. For example,
strnum(Col1, right) used for a column value of -1.27 becomes ###-1.27, assuming the
target column allows 7 digits. The minus sign is not counted as a digit, but the
decimal is.
RIGHTZERO Right justify, fill the rest of the target column with zeros. If the value of a column
is a negative value, the zeros are added after the minus sign and before the
numbers. For example, strnum(Col1, rightzero) used for a column value of -1.27
becomes -0001.27, assuming the target column allows 7 digits. The minus sign is
not counted as a digit, but the decimal is.
Specifies the output length, when any of the options are used that specify padding
(all but LEFT). For example:
◆ strnum(Col1, right, 6) used for a column value of -1.27 becomes ##-1.27. The minus
sign is not counted as a digit, but the decimal is.
◆ strnum(Col1, rightzero, 6) used for a column value of -1.27 becomes -001.27. The
minus sign is not counted as a digit, but the decimal is.

Example Assuming a source column named NUM has a value of 15 and the target column’s maximum
length is 5 characters, the following examples show the different types of results obtained
with formatting options.
Function statement Result (# denotes a space)
CHAR1 = @STRNUM (NUM, LEFT) 15
CHAR1 = @STRNUM (NUM, LEFTSPACE) 15###
CHAR1 = @STRNUM (NUM, RIGHTZERO) 00015
CHAR1 = @STRNUM (NUM, RIGHT) ###15
Function statement Result (# denotes a space)
CHAR1 = @STRNUM (NUM, LEFTSPACE, 4) 15##
CHAR1 = @STRNUM (NUM, RIGHTZERO, 4) 0015
CHAR1 = @STRNUM (NUM, RIGHT, 4) ##15