1. Oracle GoldenGate Microservices Documentation
  2. Administer
  3. Data Management
  4. Execute Commands, Stored Procedures, and Queries with SQLEXEC

Execute Commands, Stored Procedures, and Queries with SQLEXEC

The SQLEXEC parameter of Oracle GoldenGate enables Extract and Replicat to communicate with the database to do the following:

  • Execute a database command, stored procedure, or SQL query to perform a database function, return results (SELECT statements) or perform DML (INSERT, UPDATE, DELETE) operations.

  • Retrieve output parameters from a procedure for input to a FILTER or COLMAP clause.

Note:

SQLEXEC provides minimal globalization support. To use SQLEXEC in the capture parameter file of the source capture, make sure that the client character set in the source .prm file is either the same or a superset of the source database character set.

Performing Processing with SQLEXEC

SQLEXEC extends the functionality of both Oracle GoldenGate and the database by allowing Oracle GoldenGate to use the native SQL of the database to execute custom processing instructions.

  • Stored procedures and queries can be used to select or insert data into the database, to aggregate data, to denormalize or normalize data, or to perform any other function that requires database operations as input. Oracle GoldenGate supports stored procedures that accept input and those that produce output.

  • Database commands can be issued to perform database functions required to facilitate Oracle GoldenGate processing, such as disabling triggers on target tables and then enabling them again.

Using SQLEXEC

The SQLEXEC parameter can be used as follows:

  • as a clause of a TABLE or MAP statement

  • as a standalone parameter at the root level of the Extract or Replicat parameter file.

Apply SQLEXEC as a Standalone Statement

When used as a standalone parameter statement in the Extract or Replicat parameter file, SQLEXEC can execute a stored procedure, query, or database command. As such, it need not be tied to any specific table and can be used to perform general SQL operations.

For example, if the Oracle GoldenGate database user account is configured to time-out when idle, you could use SQLEXEC to execute a query at a defined interval, so that Oracle GoldenGate does not appear idle. As another example, you could use SQLEXEC to issue an essential database command, such as to disable target triggers. A standalone SQLEXEC statement cannot accept input parameters or return output parameters.

Parameter syntax Purpose 
SQLEXEC 'call procedure_name()'

Execute a stored procedure

 
SQLEXEC 'sql_query'

Execute a query

 
SQLEXEC 'database_command'

Execute a database command
Argument Description 
'call
procedure_name ()'

Specifies the name of a stored procedure to execute. The statement must be enclosed within single quotes.

Example:

SQLEXEC 'call prc_job_count ()'
 
'sql_query'

Specifies the name of a query to execute. The query must be contained all on one line and enclosed within single quotes.

Specify case-sensitive object names the way they are stored in the database, such as within double quotes for Oracle object names that are case-sensitive.

SQLEXEC 'SELECT "col1" from "schema"."table"'

'database_command'

Specifies a database command to execute. Must be a valid command for the database.

SQLEXEC provides options to control processing behavior, memory usage, and error handling. For more information, see SQLEXEC in the Parameters and Functions Reference for Oracle GoldenGate.

Apply SQLEXEC within a TABLE or MAP Statement

When used within a TABLE or MAP statement, SQLEXEC can pass and accept parameters. It can be used for procedures and queries, but not for database commands.

Syntax

This syntax executes a procedure within a TABLE or MAP statement. 

SQLEXEC (SPNAME sp_name,
[ID logical_name,]
{PARAMS param_spec | NOPARAMS})
Argument Description 
SPNAME

Required keyword that begins a clause to execute a stored procedure.

 
sp_name

Specifies the name of the stored procedure to execute. 

ID logical_name

Defines a logical name for the procedure. Use this option to execute the procedure multiple times within a TABLE or MAP statement. Not required when executing a procedure only once. 

PARAMS param_spec |
NOPARAMS

Specifies whether or not the procedure accepts parameters. One of these options must be used (see Using Input and Output Parameters).

Syntax

This syntax executes a query within a TABLE or MAP statement. 

SQLEXEC (ID logical_name, QUERY ' query ',
{PARAMS param_spec | NOPARAMS})
Argument Description 
ID logical_name

Defines a logical name for the query. A logical name is required in order to extract values from the query results. ID logical_name references the column values returned by the query. 

QUERY ' sql_query '

Specifies the SQL query syntax to execute against the database. It can either return results with a SELECT statement or change the database with an INSERT, UPDATE, or DELETE statement. The query must be within single quotes and must be contained all on one line. Specify case-sensitive object names the way they are stored in the database, such as within quotes for Oracle case-sensitive names. 

SQLEXEC 'SELECT "col1" from "schema"."table"'
 
PARAMS param_spec |
NOPARAMS

Defines whether or not the query accepts parameters. One of these options must be used (see Using Input and Output Parameters).

If you want to execute a query on a table residing on a different database than the current database, then the different database name has to be specified with the table. The delimiter between the database name and the tablename should be a colon (:).

The following are some example use cases:

select col1 from db1:tab1
select col2 from db2:schema2.tab2
select col3 from tab3
select col3 from schema4.tab4

Using Input and Output Parameters

Oracle GoldenGate provides options for passing input and output values to and from a procedure or query that is executed with SQLEXEC within a TABLE or MAP statement.

Passing Values to Input Parameters

To pass data values to input parameters within a stored procedure or query, use the PARAMS option of SQLEXEC.

Syntax 

PARAMS ([OPTIONAL | REQUIRED] param = {source_column | function}
[, ...] )

Where:

  • OPTIONAL indicates that a parameter value is not required for the SQL to execute. If a required source column is missing from the database operation, or if a column-conversion function cannot complete successfully because a source column is missing, the SQL executes anyway.

  • REQUIRED indicates that a parameter value must be present. If the parameter value is not present, the SQL will not be executed.

  • param is one of the following:

    • For a stored procedure, it is the name of any parameter in the procedure that can accept input, such as a column in a lookup table.

    • For an Oracle query, it is the name of any input parameter in the query excluding the leading colon. For example, :param1 would be specified as param1 in the PARAMS clause.

    • For a non-Oracle query, it is pn, where n is the number of the parameter within the statement, starting from 1. For example, in a query with two parameters, the param entries are p1 and p2.

  • {source_column | function} is the column or Oracle GoldenGate conversion function that provides input to the procedure.

Passing Values to Output Parameters

To pass values from a stored procedure or query as input to a FILTER or COLMAP clause, use the following syntax:

Syntax 

{procedure_name | logical_name}.parameter

Where:

  • procedure_name is the actual name of the stored procedure. Use this argument only if executing a procedure one time during the life of the current Oracle GoldenGate process.

  • logical_name is the logical name specified with the ID option of SQLEXEC. Use this argument if executing a query or a stored procedure that will be executed multiple times.

  • parameter is either the name of the parameter or RETURN_VALUE, if extracting returned values.

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 9-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 9-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));

Handling SQLEXEC Errors

There are two types of error conditions to consider when implementing SQLEXEC:

  • The column map requires a column that is missing from the source database operation. This can occur for an update operation if the database only logs the values of columns that changed, rather than all of the column values. By default, when a required column is missing, or when an Oracle GoldenGate column-conversion function results in a "column missing" condition, the stored procedure does not execute. Subsequent attempts to extract an output parameter from the stored procedure results in a "column missing condition" in the COLMAP or FILTER clause.

  • The database generates an error.

Handling Database Errors

Use the ERROR option in the SQLEXEC clause to direct Oracle GoldenGate to respond in one of the following ways:

Table 9-1 ERROR Options

Action Description 
IGNORE

Causes Oracle GoldenGate to ignore all errors associated with the stored procedure or query and continue processing. Any resulting parameter extraction results in a "column missing" condition. This is the default.

 
REPORT

Ensures that all errors associated with the stored procedure or query are reported to the discard file. The report is useful for tracing the cause of the error. It includes both an error description and the value of the parameters passed to and from the procedure or query. Oracle GoldenGate continues processing after reporting the error.

 
RAISE

Handles errors according to rules set by a REPERROR parameter specified in the Replicat parameter file. Oracle GoldenGate continues processing other stored procedures or queries associated with the current TABLE or MAP statement before processing the error. 

FINAL

Performs in a similar way to RAISE except that when an error associated with a procedure or query is encountered, any remaining stored procedures and queries are bypassed. Error processing is called immediately after the error. 

FATAL

Causes Oracle GoldenGate to abend immediately upon encountering an error associated with a procedure or query.

Handling Missing Column Values

Use the @COLTEST function to test the results of the parameter that was passed, and then map an alternative value for the column to compensate for missing values, if desired. Otherwise, to ensure that column values are available, you can use the FETCHCOLS or FETCHCOLSEXCEPT option of the TABLE parameter to fetch the values from the database if they are not present in the log. As an alternative to fetching columns, you can enable supplemental logging for those columns.

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 USERIDALIAS parameter in the Extract parameter file or the TARGETDB and 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.

  • 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 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.