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博客

oracle goldengate 调用存储过程案例

最新文章