First, you should avoid using SQLEXEC unless you have no other choice. This is because SQLEXEC could slow down the extract and replicat process. Oracle GoldenGate has to connect to the database to execute the request for each row or each transaction. The connection round trip and the execution of the commands take time.
SQLEXEC can run both as a standalone call or with the MAP configuration. For the standalone call, it will only run once when extract and replicat starts. When running with the MAP configuration, there are four options:
- MAP is the default option. Executes the procedure or query once for each source-target table map for which it is specified. You don't need to specify any EXEC option for this configuration.
- ONCE executes the procedure or query once during the Oracle GoldenGate run, upon the first invocation of the associated MAP statement. The syntax is EXEC ONCE.
- TRANSACTION executes the procedure or query once per source transaction. The results remain valid for all operations of the transaction.The syntax is EXEC TRANSACTION .
- SOURCEROW: executes the procedure or query once per source row operation. This option is useful when having one source table mapping to multiple target tables, the procedure or query are invoked for each source-target mapping. The syntax is EXEC SOURCEROW.
- Burleson Consulting,GoldenGate SQLEXEC parameter tips, April 17, 2015
- Oracle GoldenGate for Filtering and Transformation Data, May 9 2015