oracle goldengate 呼叫儲存過程案例

paulyibinyi發表於2011-10-25

Example 1 The following example shows the use of a stored procedure that executes once to get a value
from the lookup table. The value is mapped to the target column in the COLMAP statement.


TABLE sales.cust, TARGET sales.cust_extended, &
SQLEXEC (SPNAME lookup, &
PARAMS (long_name = birth_state)), &
COLMAP (custid = custid, &
birth_state_long = lookup.long_name);

 


Example 2 The following example shows multiple executions of a stored procedure that gets values
from a lookup table. The values are mapped to target columns.


TABLE sales.cust, TARGET sales.cust_extended, &
SQLEXEC (SPNAME lookup, ID lookup1, &
PARAMS (long_name = current_residence_state)), &
SQLEXEC (SPNAME lookup, ID lookup2, &
PARAMS (long_name = birth_state)), &
COLMAP (custid = custid, &
current_residence_state_long = lookup1.long_name, &
birth_state_long = lookup2.long_name);

 
 
Example 3 The following example illustrates the use of ID . It enables each column map to
call a stored procedure named lookup separately and refer to its own results by means of
lookup1 and lookup2.
 

TABLE sales.srctab, TARGET sales.targtab, &
SQLEXEC (SPNAME lookup, ID lookup1, PARAMS (param1 = srccol)), &
COLMAP (targcol1 = lookup1.param2), &
SQLEXEC (SPNAME lookup, ID lookup2, PARAMS (param1 = srccol)), &
COLMAP, (targcol2 = lookup2.param2);
 
用ID別名來區分 可以呼叫包頭和包體
 

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/7199859/viewspace-709624/,如需轉載,請註明出處,否則將追究法律責任。

相關文章