风哥教程

培训 . 交流 . 分享
Make progress together!

Oracle Goldengate列转换样例

[复制链接]
内容发布:paulyi| 发布时间:2014-2-26 20:25:46
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 (<input>)
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 (<column>, {LEFT | LEFTSPACE, | RIGHT | RIGHTZERO} [<length>] )
Argument Description
<string> The name of a string (character) column or a literal string. Enclose literals
within quotes.
<max length> The maximum string length, in characters.
Argument Description
<string1> The first string to be compared.
<string2> The second string to be compared.
<max length> The maximum number of characters in the string to compare.
Argument Description
<column> 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.
<length> 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



上一篇:oracle goldengate复制端延时应用参数DEFERAPPLYINTERVAL
下一篇:oracle goldengate 目标端表空间满导致进程abended处理过程
回复

使用道具 举报

1框架
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

热门文章教程

  • 风哥Oracle数据库巡检工具V1.0(附2.6网页
  • 实战PHP与MySQL权威指南PDF电子书下载
  • 大数据技术与应用入门培训教程(电子版下载
  • Oracle 12cR2 九大新功能全面曝光_详解云数
  • Oracle OCP认证考试IZ0-053题库共712题数据
  • MySQL5权威指南(第3版)PDF电子版下载
快速回复 返回顶部 返回列表