ogg常用函数

CASE Selects a value depending on a series of value tests.
Syntax @CASE (<value>, <test value1>, <test result1>
[, <test value2>, <test result2>] [, ...]
[, <default result>]
This function does not support NCHAR or NVARCHAR data types


eg: @CASE (PRODUCT_CODE, "CAR", "A car", "TRUCK", "A truck")


EVAL Selects a value based on a series of independent tests.
Syntax @EVAL (<condition1>, <result1>
[<condition2>, <result2>] [, ....]
[, <default result>])


eg:AMOUNT_DESC = @EVAL (AMOUNT > 10000, "high amount", AMOUNT > 5000, "somewhat high" )


IF       Selects one of two values depending on whether a conditional statement returnsTRUE or FALSE.
eg: ACCT_CHIEF_COMPLAINT =@IF ( @NUMBIN (ACCT_CREATE_DATE ) < 48633, "xxxxxx",@BINARY(ACCT_CHIEF_COMPLAINT))


BINARY
默认中，二进制传输后会转换为ASCII码，如要目标端的值也为二进制，使用该函数
Syntax @BINARY(<column name>)
<column name> The name of the target column to which the data will be copied




BINTOHEX
二进制转换为十六进制
Syntax @BINTOHEX(<data>)
<data> The name of the source column, an expression, or a literal string that is enclosed within quotes.
Example @BINTOHEX("12345") converts to “3132333435".




COLSTAT
指示missing,null,invalid给目标列
Syntax @COLSTAT ({MISSING | NULL | INVALID})
Example The following example returns a NULL into target column ITEM.
ITEM = @COLSTAT (NULL)




COLTEST
源端列数据情况匹配，返回TRUE或FALSE
Syntax @COLTEST (<source column>, <test item> [, <test item>] [, ...])
test item=present,missing,null,invalid 中的一个


PRESENT Indicates a column is present in the source record and not NULL.
     Column values can be missing if the database does not log values for columns that do not change, but that is not the same as NULL.
NULL    Indicates a column is present in the source record and NULL.
MISSING Indicates a column is not present in the source record.
INVALID Indicates a column is present


Example The following example uses @IF to map a value to the HIGH_SALARY column only if the
BASE_SALARY column in the source record was both present (and not NULL) and greater than
250000. Otherwise, NULL is returned.
HIGH_SALARY =
@IF (@COLTEST (BASE_SALARY, PRESENT) AND
BASE_SALARY > 250000,
BASE_SALARY, @COLSTAT (NULL))




COMPUTE
用于计算，返回结果类型为string
Syntax @COMPUTE(<expression>)




DATE
转化为date型
Syntax @DATE ("<output descriptor>", "<input descriptor>", <source col> [, "<input descriptor>", <source col>] [, ...])
Example 
date_col = @DATE ("YYYY-MM-DD", "YY", date1_yy, "MM", date1_mm, "DD",date1_dd)
date_col = @DATE ("YYYY-MM-DD:HH:MI:00", "YYMMDD", date1, "HHMI", time1)
datetime_col = @DATE ("YYYY-MM-DD:HH:MI:SS", "YYYYMMDDHHMISS", numeric_date)
julian_ts_col = @DATE ("JTS", "YYYYMMDDHHMISS", numeric_date)
order_filled = @DATE ("YYYY-MM-DD:HH:MI:SS", "JTS", @DATE ("JTS","YYMMDDHHMISS", order_taken) + order_minutes * 60 * 1000000)


DATEDIFF
计算时间差
Syntax @DATEDIFF ("difference", "<date>", "<date>")
difference=DD|SS
Example
YTD = @DATEDIFF ("DD", "2011-01-01", @DATENOW ()) --计算天数差
todays_day = @COMPUTE (@DATEDIFF ("DD", "2011-01-01", @DATENOW ()) +1)


DATENOW
获取当前时间，格式为 YYYY-MM-DD HH:MI:SS
Syntax @DATENOW ()


DDL
获取ddl操作信息
Syntax @DDL ({TEXT | OPTYPE | OBJNAME | OBJTYPE | OBJOWNER})
OBJNAME Returns the name of the object that is affected by the DDL.
OBJOWNER Returns the name of the owner of the object that is affected by the DDL.
OBJTYPE Returns the type of object that is affected by the DDL, such as TABLE or INDEX)
OPTYPE Returns the operation type of the DDL, such as CREATE or ALTER.
TEXT Returns the first 200 characters of the text of the DDL statement.


GETENV
获取ogg信息
Example
TABLE fin.product, TOKENS (
TKN-OSUSER = @GETENV ("GGENVIRONMENT", "OSUSERNAME"),
TKN-DOMAIN = @GETENV ("GGENVIRONMENT", "DOMAINNAME"),
TKN-COMMIT-TS = @GETENV ("GGHEADER", "COMMITTIMESTAMP"),
TKN-TABLE = @GETENV ("GGHEADER", "TABLENAME"),
TKN-OP-TYPE = @GETENV ("GGHEADER", "OPTYPE"),
TKN-LENGTH = @GETENV ("GGHEADER", "RECORDLENGTH"),
TKN-LAG-SEC = @GETENV ("LAG", "SECONDS"),
TKN-DB-USER = @GETENV ("DBENVIRONMENT", "DBUSER"),
TKN-DB-VER = @GETENV ("DBENVIRONMENT", "DBVERSION"),
TKN-ROWID = @GETENV ("RECORD", "GDVN"));


GETVAL
获取存储过程或查询的values
Example
MAP schema.srctab, TARGET schema.targtab,
SQLEXEC (SPNAME lookup, ID lookup1, PARAMS (param1 = srccol)),
COLMAP (targcol1 = @GETVAL (lookup1.param2));
MAP schema.srctab, TARGET schema.targtab2,
SQLEXEC (SPNAME lookup, ID lookup2, PARAMS (param1 = srccol)),
COLMAP (targcol2= @GETVAL (lookup2.param2));




HEXTOBIN
十六进制转化为原始格式
Use the @HEXTOBIN function to convert a supplied string of hexadecimal data into raw format.
Syntax @HEXTOBIN(<data>)
Example @HEXTOBIN("414243") converts to three bytes: 0x41 0x42 0x43.


HIGHVAL | LOWVAL


NUMBIN
二进制字符串转化为number，convert a binary string of eight or fewer bytes into a number
Syntax @NUMBIN (<source column>)
Example The following combines @NUMBIN and @DATE to transform a 48-bit Tandem column to a 64-
bit Julian value for local time.
DATE = @DATE ("JTSLCT", "TTS" @NUMBIN (DATE))


NUMSTR
字符串转化为数字
Syntax @NUMSTR (<input>)
Example PAGE_NUM = @NUMSTR (ALPHA_PAGE_NO)


RANGE
对表的数据分成记几个部分，分别在不同的进程里应用，提高效率，可用于抽取和抽取
Syntax @RANGE (<range>, <total ranges> [, <column>] [, <column>] [, ...])
如果column不指定，默认用主键
Example 1 In the following example, the replication workload is split into three ranges (between three
Replicat processes) based on the ID column of the source acct table.
(Replicat group 1 parameter file)
MAP sales.acct, TARGET sales.acct, FILTER (@RANGE (1, 3, ID));
(Replicat group 2 parameter file)
MAP sales.acct, TARGET sales.acct, FILTER (@RANGE (2, 3, ID));
(Replicat group 3 parameter file)
MAP sales.acct, TARGET sales.acct, FILTER (@RANGE (3, 3, ID));


Example 2 In the following example, one Extract process splits the processing load into two trails.
Since no columns were defined on which to base the range calculation, Oracle GoldenGate
will use the primary key columns.
RMTTRAIL /ggs/dirdat/aa
TABLE fin.account, FILTER (@RANGE (1, 2));
RMTTRAIL /ggs/dirdat/bb
TABLE fin.account, FILTER (@RANGE (2, 2));


Example 3 In the following example, two tables have relative operations based on an order_ID column.
The order_master table has a key of order_ID, and the order_detail table has a key of order_ID and
item_number. Because the key order_ID establishes relativity, it is used in @RANGE filters for
both tables to preserve referential integrity. The load is split into two ranges.
(Parameter file #1)
MAP sales.order_master, TARGET sales.order_master,
FILTER (@RANGE (1, 2, order_ID));
MAP sales.order_detail, TARGET sales.order_detail,
FILTER (@RANGE (1, 2, order_ID));
(Parameter file #2)
MAP sales.order_master, TARGET sales.order_master,
FILTER (@RANGE (2, 2, order_ID));
MAP sales.order_detail, TARGET sales.order_detail,
FILTER (@RANGE (2, 2, order_ID));


STRCAT
连接字符串
Syntax @STRCAT (<string1>, <string2> [, ...])
Example PHONE_NO = @STRCAT (AREA_CODE, PREFIX, “-”, PHONE)


STRCMP
比较字符长短
1比2长返回1,1等于2返回0,1比2短返回-1
Syntax @STRCMP (<string1>, <string2>)
Example The following example compares two literal strings and returns 1 because the first string
is greater than the second one.
@STRNCMP ("JOHNSON", "JONES")


STREQ
比较字符是否相等，相等为1（TRUE），不等为0（FALSE）
This function does not support NCHAR or NVARCHAR data types.
Syntax @STREQ (<string1>, <string2>)
Example The following compares the value of the region column to the literal value “EAST”. If region = EAST, the record passes the filter.
FILTER (@STREQ (region, “EAST”))
Example You could use @STREQ in a comparison to determine a result, as shown in the following
example. If the state is “NY”, the expression returns “East Coast”. Otherwise, it returns
“Other”.
@IF (@STREQ (state, "NY"), "East Coast", "Other")


STREXT
截取字符
Syntax @STREXT (<string>, <begin position>, <end position>)
Example The following example uses three @STREXT functions to extract a phone number into three different columns.
AREA_CODE = @STREXT (PHONE, 1, 3),
PREFIX = @STREXT (PHONE, 4, 6),
PHONE_NO = @STREXT (PHONE, 7, 10)


STRFIND
查询字符（串）在字符串中的位置，查不到返回0
Syntax @STRFIND (<string>, "<search string>" [, < begin position>])
Example Assuming the string for the ACCT column is ABC123ABC, the following are possible results.
Function statement Result
@STRFIND (ACCT, "23") 5
@STRFIND (ACCT, "ZZ") 0
@STRFIND (ACCT, "ABC", 2) 7 (because the search started at the second character)


STRLEN
获取字符串长度
This function does not support NCHAR or NVARCHAR data types.
Syntax  @STRLEN (<string>)
Example @STRLEN (ID_NO)


STRLTRIM
去前（左）空格
Syntax @STRLTRIM (<string>)


STRRTRIM
去后（右）空格
Syntax @STRRTRIM (<string>)


STRTRIM
去前后（左右）空格
Syntax @STRTRIM (<string>)


STRNCAT
指定长度连接字符串
Syntax @STRNCAT (<string>, <max length> [, <string>, <max length>] [, ...] )
Example The following concatenates two strings and results in “ABC123.”
PHONE_NO = @STRNCAT ("ABCDEF", 3, "123456", 3)


STRNCMP
指定长度（从第一个字符开始）比较字符串，返回1（TRUE），0（FALSE）
Syntax @STRNCMP (<string1>, <string2>, <max length>)
Example The following example compares the first two characters of each string, as specified by a
<max length> of 2, and it returns 0 because both sets are the same.
@STRNCMP ("JOHNSON", "JONES", 2)


STRNUM
数字转字符
Syntax @STRNUM (<column>, {LEFT | LEFTSPACE, | RIGHT | RIGHTZERO} [<length>] )
LEFT 左对齐
LEFTSPACE 左对齐并填充空格
RIGHT 右对齐填充空格
RIGHTZERO 右对齐填充0


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


If an output <length> of 4 is specified in the preceding example, the following shows the
different types of results.
Function statement Result (# denotes a space)
CHAR1 = @STRNUM (NUM, LEFTSPACE, 4) 15##
CHAR1 = @STRNUM (NUM, RIGHTZERO, 4) 0015
CHAR1 = @STRNUM (NUM, RIGHT, 4) ##15


STRSUB
字符替换
Syntax @STRSUB
(<source string>, <search string>, <substitute string>
[, <search string>, <substitute string>] [, ...])
Example 1 The following returns xxABCxx.
@STRSUB ("123ABC123", "123", "xx")
Example 2 The following returns 023zBC023.
@STRSUB ("123ABC123", "A", "z", "1", "0")


STRUP
字符转换为大写
Syntax @STRUP (<string>)
Example The following returns "SALESPERSON."
@STRUP ("salesperson")


TOKEN
Syntax @TOKEN ("<token name>")
Example In the following example, 10 tokens are mapped to target columns.
MAP ora.oratest, TARGET ora.rpt,
COLMAP (
host = @token ("tk_host"),
gg_group = @token ("tk_group"),
osuser = @token ("tk_osuser"),
domain = @token ("tk_domain"),
ba_ind = @token ("tk_ba_ind"),
commit_ts = @token ("tk_commit_ts"),
pos = @token ("tk_pos"),
rba = @token ("tk_rba"),
tablename = @token ("tk_table"),
optype = @token ("tk_optype")
);


VALONEOF
字符串对比a list of values，If the value or column is in the list, 1 is returned; otherwise 0 is returned
Syntax @VALONEOF (<expression>, <value> [, <value>] [, ...])
Example In the following example, if STATE is CA or NY, the expression returns “COAST,” which is the
response returned by @IF when the value is non-zero (true). Otherwise, the expression
returns “MIDDLE.”
@IF (@VALONEOF (STATE, "CA", "NY"), "COAST", "MIDDLE")