1. Oracle GoldenGate Classic Architecture Documentation
  2. Administer
  3. Customizing Oracle GoldenGate Processing
  4. Executing Commands, Stored Procedures, and Queries with SQLEXEC
  5. Additional SQLEXEC Guidelines

Additional SQLEXEC Guidelines

Observe the following SQLEXEC guidelines:

  • Up to 20 stored procedures or queries can be executed per TABLE or MAP entry. They execute in the order listed in the parameter statement.

  • A database login by the Oracle GoldenGate user must precede the SQLEXEC clause. Use the SOURCEDB and/or USERID or USERIDALIAS parameter in the Extract parameter file or the TARGETDB and/or USERID or USERIDALIAS parameter in the Replicat parameter file, as needed for the database type and configured authentication method.

  • The SQL is executed by the Oracle GoldenGate user. This user must have the privilege to execute stored procedures and call RDBM-supplied procedures.

  • Database operations within a stored procedure or query are committed in same context as the original transaction.

  • Do not use SQLEXEC to update the value of a primary key column. If SQLEXEC is used to update the value of a key column, then the Replicat process will not be able to perform a subsequent update or delete operation, because the original key value will be unavailable. If a key value must be changed, you can map the original key value to another column and then specify that column with the KEYCOLS option of the TABLE or MAP parameter.

  • For DB2, Oracle GoldenGate uses the ODBC SQLExecDirect function to execute a SQL statement dynamically. This means that the connected database server must be able to prepare the statement dynamically. ODBC prepares the SQL statement every time it is executed (at the requested interval). Typically, this does not present a problem to Oracle GoldenGate users. See the IBM DB2 documentation for more information.

  • Do not use SQLEXEC for objects being processing by a data-pump Extract in pass-through mode.

  • All object names in a SQLEXEC statement must be fully qualified with their two-part or three-part names, as appropriate for the database.

  • All objects that are affected by a SQLEXEC stored procedure or query must exist with the correct structures prior to the execution of the SQL. Consequently, DDL on these objects that affects structure (such as CREATE or ALTER) must happen before the SQLEXEC executes.

  • All objects affected by a standalone SQLEXEC statement must exist before the Oracle GoldenGate processes start. Because of this, DDL support must be disabled for those objects; otherwise, DDL operations could change the structure or delete the object before the SQLEXEC procedure or query executes on it.