SQLEXEC Examples Using Parameters
These examples use stored procedures and queries with input and output parameters.
Note:
Additional SQLEXEC
options are available for use when a procedure
or query includes parameters. See SQLEXEC
in the Parameters and Functions Reference
for Oracle GoldenGate.
Example 11-3 SQLEXEC with a Stored Procedure
This example uses SQLEXEC
to run a stored procedure named LOOKUP
that performs a query to return a description based on a code. It then maps the results to a target column named NEWACCT_VAL
.
CREATE OR REPLACE PROCEDURE LOOKUP
(CODE_PARAM IN VARCHAR2, DESC_PARAM OUT VARCHAR2)
BEGIN
SELECT DESC_COL
INTO DESC_PARAM
FROM LOOKUP_TABLE
WHERE CODE_COL = CODE_PARAM
END;
Contents of MAP
statement:
MAP sales.account, TARGET sales.newacct, &
SQLEXEC (SPNAME lookup, PARAMS (code_param = account_code)), &
COLMAP (newacct_id = account_id, newacct_val = lookup.desc_param);
SQLEXEC
executes the LOOKUP
stored procedure. Within the SQLEXEC
clause, the PARAMS (code_param = account_code)
statement identifies code_param
as the procedure parameter to accept input from the account_code
column in the account
table.
Replicat executes the LOOKUP
stored procedure prior to executing the column map, so that the COLMAP
clause can extract and map the results to the newacct_val
column.
Example 11-4 SQLEXEC with a Query
This example implements the same logic as used in the previous example, but it executes a SQL query instead of a stored procedure and uses the @GETVAL
function in the column map.
A query must be on one line. To split an Oracle GoldenGate parameter statement into multiple lines, an ampersand (&
) line terminator is required.
Query for an Oracle database:
MAP sales.account, TARGET sales.newacct, &
SQLEXEC (ID lookup, &
QUERY 'select desc_col desc_param from lookup_table where code_col = :code_param', &
PARAMS (code_param = account_code)), &
COLMAP (newacct_id = account_id, newacct_val = &
@getval (lookup.desc_param));
Query for a non-Oracle database:
MAP sales.account, TARGET sales.newacct, &
SQLEXEC (ID lookup, &
QUERY 'select desc_col desc_param from lookup_table where code_col = ?', &
PARAMS (p1 = account_code)), &
COLMAP (newacct_id = account_id, newacct_val = &
@getval (lookup.desc_param));