Another powerful feature of GoldenGate is the SQLEXEC parameter. We will discuss when and how to use it as a standalone statement or in a TABLE or MAP statement to fulfill your data transformation requirements. SQLEXEC is valid for Extract and Replicat processes and can execute SQL statements or PL/SQL stored procedures.
On the target database, the SQLEXEC parameter in the MAP statement allows external calls to be made through a SQL interface that supports the execution of native SQL and PL/SQL stored procedures. This option is typically invoked to perform database lookups, thus obtaining data required to resolve a mapping and can only be executed by the GoldenGate (GGADMIN) database user.
The following code maps data from the CREDITCARD_ACCOUNT table to the NEW_ACCOUNT table. The Extract process executes the LOOKUP_ACCOUNT stored procedure prior to executing the column map. This stored procedure has two parameters: IN and OUT. The IN parameter accepts an account code and is named CODE_IN_PARAM. The value returned by the stored procedure's OUT parameter is obtained by the @GETVAL function and is used in the COLMAP statement to populate the NEW_ACCOUNT_NAME field.
The SPNAME Replicat parameter specifies the name of the PL/SQL stored procedure, whereas PARAMS specifies its parameters, as shown in the following code:
MAP SRC.CREDITCARD_ACCOUNTS, TARGET TGT.NEW_ACCOUNT, & SQLEXEC (SPNAME LOOKUP_ACCOUNT, & PARAMS (CODE_IN_PARAM = ACCOUNT_CODE)), & COLMAP (USEDEFAULTS, & NEW_ACCOUNT_ID = ACCOUNT_ID, & NEW_ACCOUNT_NAME = @GETVAL(LOOKUP_ACCOUNT.CODE_OUT_PARAM));
To pass values from a stored procedure or query as input to a FILTER or COLMAP statement, we must specify the stored procedure name, followed by the OUT parameter name.
It is also possible to perform the same lookup operation using SQL. The following code illustrates this using the same logic and parameter names:
MAP SRC.CREDITCARD_ACCOUNTS, TARGET TGT.NEW_ACCOUNT, & SQLEXEC (ID LOOKUP_ACCOUNT, & QUERY "SELECT ACCOUNT_NAME FROM ACCOUNT WHERE ACCOUNT_CODE = :CODE_IN_PARAM", & PARAMS (CODE_IN_PARAM = ACCOUNT_CODE)), & COLMAP (NEW_ACCOUNT_ID = ACCOUNT_ID, & NEW_ACCOUNT_NAME = @GETVAL (LOOKUP_ACCOUNT.CODE_OUT_PARAM));
:CODE_IN_PARAM becomes a bind variable because the input to the SQL query that drives the lookup is based on account code, while the CODE_OUT_PARAM remains as the OUT parameter that populates the NEW_ACCOUNT_NAME field.
As we have referenced the LOOKUP_ACCOUNT procedure in both examples, here is the source code:
CREATE OR REPLACE PROCEDURE LOOKUP_ACCOUNT
(CODE_IN_PARAM IN VARCHAR2, CODE_OUT_PARAM OUT VARCHAR2) AS
BEGIN
SELECT ACCOUNT_NAME
INTO CODE_OUT_PARAM
FROM ACCOUNT
WHERE ACCOUNT_CODE = CODE_IN_PARAM;
END;Rather than having GoldenGate apply the data changes to the target database, it is possible to have SQLEXEC perform this via INSERT, UPDATE, or DELETE commands. Using SQLEXEC in the standalone mode to execute DML against the target database necessitates the DBOP keyword in the configuration to commit the changes; otherwise, the transaction will roll back.
The following code calls the AUDIT_TXN procedure that inserts a record into an audit table, keeping a history of GoldenGate transactions. On successful execution, the DBOP keyword ensures that the transaction is committed to the target database:
SQLEXEC (SPNAME audit_txn, &
PARAMS (hostname = @GETENV ("GGENVIRONMENT","HOSTNAME"), &
@GETENV("GGENVIRONMENT","OSUSERNAME"), &
@GETENV ("GGHEADER", "OPTYPE"), &
@GETENV("GGHEADER","COMMITTIMESTAMP"), &
@GETENV ("GGHEADER", "TABLENAME"), &
ALLPARAMS REQUIRED, ERROR REPORT, DBOP)Let's take a closer look at error handling within a SQLEXEC procedure call.
When you use
SQLEXEC, database errors must be handled; otherwise, GoldenGate will abend the process regardless of the severity. Fortunately, this is made easy via the ERROR option of the SQLEXEC parameter. For calls to stored procedures, the error handling logic must be included in the procedure's PL/SQL EXCEPTION block to raise only those errors that you want GoldenGate to handle.
The following options will help you decide what GoldenGate will do when a database error is raised:
IGNORE: This states that GoldenGate does not handle any errors returned by the query or stored procedure. This is the default.REPORT: This states that all errors returned by the query or stored procedure are reported to the discard file. GoldenGate continues processing after reporting the error.RAISE: This handles errors set by aREPERRORparameter specified in the process' parameter file. However, GoldenGate continues processing other stored procedures or queries.FINAL: This acts in the same manner asRAISE, except that the error is processed immediately.FATAL: This states that GoldenGate abends the process immediately.
It is recommended to record the error to assist with troubleshooting. This is achieved through the REPORT, RAISE, and FINAL options. Having said that, should a process abend, GoldenGate will always write the error to the process' report file.
SQL and stored procedures can be executed from GoldenGate as one-off statements or scheduled to run periodically. Although you would probably leave any job scheduling to the DBMS_SCHEDULER package in the Oracle database, the following code shows a one-off SQL execution, a procedure call scheduled to run daily, and another to run every 30 seconds from within the GoldenGate environment:
SQLEXEC "select sysdate from dual" SQLEXEC "call etl_proc ()" EVERY 1 DAYS SQLEXEC "call check_exceptions_table ()" EVERY 30 SECONDS
Note
SQLEXEC is a parameter and not a command. SQLEXEC and its associated expressions must exist in a parameter file and cannot be called directly from the GGSCI command prompt. Also, SQL statements are expressed in double quotes with no terminating semicolon and are executed in the order they appear after the DBLOGIN specification.