1. Reference for Oracle GoldenGate
  2. Oracle GoldenGate Common Components
  3. Oracle GoldenGate Parameters
  4. SQLEXEC

SQLEXEC

Valid For

Extract and Replicat

Description

Use the SQLEXEC parameter to execute a stored procedure, query, or database command within the context of Oracle GoldenGate processing. SQLEXEC enables Oracle GoldenGate to communicate directly with the database to perform any work that is supported by the database. This work can be part of the synchronization process, such as retrieving values for column conversion, or it can be independent of extracting or replicating data, such as executing a stored procedure that executes an action within the database.

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.

SQLEXEC works as follows:

  • As a standalone statement at the root level of a parameter file to execute a SQL stored procedure or query or to execute a database command. As a standalone statement, SQLEXEC executes independently of a TABLE or MAP statement during Oracle GoldenGate processing. When used in a standalone SQLEXEC parameter, a query or procedure cannot include parameters. See "Standalone SQLEXEC".

  • As part of a TABLE or MAP parameter to execute a stored procedure or query with or without parameters. When used with parameters, the procedure or query that is executed can accept input parameters from source or target rows and pass output parameters. See "SQLEXEC in a TABLE or MAP Parameter".

Caution:

Use caution when executing SQLEXEC procedures against the database, especially against the production database. Any changes that are committed by the procedure can result in overwriting existing data.

Note:

The SQLEXECONBEFOREIMAGE parameter supports SQLEXEC execution on Before Image records.

Standalone SQLEXEC

A standalone SQLEXEC parameter is one that is used at the root level of a parameter file and acts independently of a TABLE or MAP parameter. The following are guidelines for using a standalone SQLEXEC parameter.

  • A standalone SQLEXEC statement executes in the order in which it appears in the parameter file relative to other parameters.

  • A SQLEXEC procedure or query must contain all exception handling.

  • A query or procedure must be structured correctly when executing a SQLEXEC statement, with legal SQL syntax for the database; otherwise Replicat will abend, regardless of any error-handling rules that are in place. Refer to the SQL reference guide provided by the database vendor for permissible SQL syntax.

  • A database credential for the Oracle GoldenGate user must precede the SQLEXEC clause. For Extract, use the SOURCEDB and USERID or USERIDALIAS parameters as appropriate for the database. For Replicat, use the TARGETDB and USERID or USERIDALIAS parameters, as appropriate.

  • The database credential that the Oracle GoldenGate process uses is the one that executes the SQL. This credential must have the privilege to execute commands and stored procedures and call database-supplied procedures.

  • A standalone SQLEXEC statement cannot be used to get input parameters from records or pass output parameters. You can use stored procedures and queries with parameters by using a SQLEXEC statement within a TABLE or MAP statement. See "SQLEXEC in a TABLE or MAP Parameter".

  • 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 of, or delete an object, before the SQLEXEC procedure or query executes on it.

  • Object names must be fully qualified in their two-part or three-part name format.

  • For DB2 on z/OS, Oracle GoldenGate uses the ODBC SQLExecDirect function to execute a SQL statement dynamically. ODBC prepares the SQL statement every time that it is executed, at a specified interval. To support this function, the connected database server must be configured to prepare SQL dynamically. See the DB2 for z/OS documentation for more information.

Getting More Information about Using Standalone SQLEXEC

See Administering Oracle GoldenGate for more information about how to use SQLEXEC.

Syntax for Standalone SQLEXEC

SQLEXEC 
{'call procedure_name()' | 'SQL_query' | 'database_command'}
[EVERY n {SECONDS | MINUTES | HOURS | DAYS}]
[ONEXIT]
[, THREADS (threadID[, threadID][, ...][, thread_range[, thread_range][, ...])]
'call procedure_name ()'

Specifies the name of a stored procedure to execute. Enclose the statement within single quotes. The call keyword is required. The following is an example of how to execute a procedure with standalone SQLEXEC: 

SQLEXEC 'call prc_job_count ()'
'SQL_query'

Specifies the name of a query to execute. Enclose the query within single quotes. Specify case-sensitive object names in the same format required by the database. The following is an example of how to execute a query with standalone SQLEXEC: 

SQLEXEC ' select x from dual '

For a multi-line query, use the single quotes on each line. For best results, type a space after each begin quote and before each end quote (or at least before each end quote).

'database_command'

Executes a database command. The following is an example of how to execute a database command with standalone SQLEXEC: 

SQLEXEC 'SET TRIGGERS OFF'
EVERY n {SECONDS | MINUTES | HOURS | DAYS}

Causes a standalone stored procedure or query to execute at a defined interval, for example:

SQLEXEC 'call prc_job_count ()' EVERY 30 SECONDS

The interval must be a whole, positive integer.

ONEXIT

Executes the SQL when the Extract or Replicat process stops gracefully, for example:

SQLEXEC 'call prc_job_count ()' ONEXIT
THREADS (threadID[, threadID][, ...][, thread_range[, thread_range][, ...])

Executes SQLEXEC only for the specified thread or threads of a coordinated Replicat.

threadID[, threadID][, ...]

Specifies a thread ID or a comma-delimited list of threads in the format of threadID, threadID, threadID.

[, thread_range[, thread_range][, ...]

Specifies a range of threads in the form of threadIDlow-threadIDhigh or a comma-delimted list of ranges in the format of threadIDlow-threadIDhigh, threadIDlow-threadIDhigh.

A combination of these formats is permitted, such as threadID, threadID, threadIDlow-threadIDhigh.

If no THREADS clause is used, the SQL is executed by all of the threads that were configured for this Replicat group by the ADD REPLICAT command. However, if the SQL satisfies the criteria for a barrier transaction, the entire SQLEXEC statement is processed by thread 0 regardless of the actual thread mapping.

SQLEXEC in a TABLE or MAP Parameter

A SQLEXEC parameter in a TABLE or MAP parameter can be used to execute a stored procedure or query that does or does not accept parameters. The following are SQLEXEC dependencies and restrictions when used in a MAP or TABLE statement:

  • The SQL is executed by the database user under which the Oracle GoldenGate process is running. This user must have the privilege to execute stored procedures and call database-supplied procedures.

  • A query or procedure must be structured correctly when executing a SQLEXEC statement. If Replicat encounters a problem with the query or procedure, the process abends immediately, despite any error-handling rules that are in place. Refer to the SQL reference guide provided by the database vendor for permissible SQL syntax.

  • The COMMIT operation of a Replicat transaction to the target database also commits any DML changes that are made in a SQLEXEC statement within the boundary of the original source transaction. This is not true for Extract, because Extract does not perform SQL transactions. When using SQLEXEC for Extract, you can either enable implicit commits or execute an explicit commit within the SQLEXEC procedure.

  • Specify literals in single quotes. Specify case-sensitive object names the same way they are specified in the database.

  • Do not use SQLEXEC to change the value of a primary key column. The primary key value is passed from Extract to Replicat. Without it, Replicat operations cannot be completed. If primary key values must be changed with SQLEXEC, you may be able to avoid errors by mapping the original key value to another column and then defining that column as a substitute key with the KEYCOLS option of the TABLE and MAP parameters.

  • For DB2 on z/OS, Oracle GoldenGate uses the ODBC SQLExecDirect function to execute a SQL statement dynamically. ODBC prepares the SQL statement every time that it is executed, at a specified interval. To support this function, the connected database server must be configured to prepare SQL dynamically. See the DB2 for z/OS documentation for more information.

  • When using Oracle GoldenGate to replicate DDL, all objects that are affected by a 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 execute before the SQLEXEC executes.

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

  • Do not use SQLEXEC for tables being processed in pass-through mode by a data-pump Extract group.

  • The following data types are supported by SQLEXEC for input and output parameters.

    • Numeric data types

    • Date data types

    • Character data types

  • When executed by a coordinated Replicat, SQLEXEC is executed by the thread or threads that are specified with the THREAD or THREADRANGE option of the MAP statement. However, if the SQLEXEC is specified in a MAP parameter that contains the COORDINATED keyword, it is executed as a barrier transaction automatically by the thread with the lowest ID number, regardless of the actual thread mapping.

Getting More Information About Using SQLEXEC in TABLE and MAP

For more information about how to use SQLEXEC, see Administering Oracle GoldenGate.

For more information about TABLE and MAP, see "TABLE | MAP".

Syntax for SQLEXEC in TABLE or MAP

SQLEXEC (
{SPNAME procedure_name[, ID logical_name] | 
   ID logical_name, QUERY ' SQL_query '}
{, PARAMS [OPTIONAL | REQUIRED] parameter_name = {source_column | OGG_function} |
   NOPARAMS}
[, AFTERFILTER | BEFOREFILTER]
[, ALLPARAMS {OPTIONAL | REQUIRED}]
[, ERROR {IGNORE | REPORT | RAISE | FINAL | FATAL}]
[, EXEC {MAP | ONCE | TRANSACTION | SOURCEROW}][, MAXVARCHARLEN bytes]
[, PARAMBUFSIZE bytes]
[, TRACE] 
[, ...]
[, BEFORE_col1 = @BEFORE(col1),
)
SPNAME procedure_name[, ID logical_name]

Executes a stored procedure.

SPNAME procedure_name

Specifies the name of the procedure to execute.

The following example shows a single execution of a stored procedure named lookup. In this case, the actual name of the procedure is used. A logical name is not needed. 

SQLEXEC (SPNAME lookup), PARAMS (param1 = srccol)), &
COLMAP (targcol = lookup.param1);
ID logical_name

Defines an optional logical name for the procedure. For example, logical names for a procedure named lookup might be lookup1, lookup2, and so forth. Use this option to execute the procedure multiple times within a MAP statement. A procedure can execute up to 20 times per MAP statement. ID is not required when executing a procedure once.

The following example shows the use of the ID option to enable multiple executions of a stored procedure that gets values from a lookup table. The values are mapped to target columns. 

SQLEXEC (SPNAME lookup, ID lookup1, &
  PARAMS (long_name = current_residence_state)), &
SQLEXEC (SPNAME lookup, ID lookup2, &
  PARAMS (long_name = birth_state)), &
COLMAP (custid = custid, current_residence_state_long = lookup1.long_name, & birth_state_long = lookup2.long_name);
ID logical_name, QUERY ' SQL_query '

Executes a query.

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. The query can either return results with a SELECT statement or execute an INSERT, UPDATE, or DELETE statement. A SELECT statement should only return one row. If multiple rows are returned, only the first row is processed. Do not specify an INTO ... clause for any SELECT statements.The query must be valid, standard query language for the database against which it is being executed. Most queries require placeholders for input parameters. How parameters are specified within the query depends on the database type, as follows:

  • For Oracle, input parameters are specified by using a colon (:) followed by the parameter name, as in the following example.

    'SELECT NAME FROM ACCOUNT WHERE SSN = :SSN AND ACCOUNT = :ACCT'

  • For other databases, input parameters are specified by using a question mark, as in the following example.

    'SELECT NAME FROM ACCOUNT WHERE SSN = ? AND ACCOUNT = ?'

The query must be contained on one line, within single quotes. Quotation marks are not required around a parameter name for any database.

The following examples illustrate the use of a SQLEXEC query for Oracle and SQL Server queries, respectively.

Oracle example:

MAP sales.account, TARGET sales.newacct, &
  SQLEXEC (ID lookup, &
  QUERY 'select desc_col into desc_param from lookup_table &
  where code_col = :code_param', &
  PARAMS (code_param = account_code)), &
  COLMAP (newacct_id = account_id, newacct_val = lookup.desc_param);

SQL Server example:

MAP sales.account, TARGET sales.newacct, &
  SQLEXEC (ID lookup, &
  QUERY 'select desc_col into desc_param from lookup_table &
  where code_col = ?', &
  PARAMS (p1 = account_code)), &
  COLMAP (newacct_id = account_id, &
  newacct_val = lookup.desc_param);
PARAMS [OPTIONAL | REQUIRED] parameter_name = {source_column | OGG_function} |
NOPARAMS

Defines whether or not the procedure or query accepts parameters and, if yes, maps the parameters to the input source. Either a PARAMS clause or NOPARAMS must be used.

OPTIONAL | REQUIRED

Determines whether or not the procedure or query executes when parameter values are missing.

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. OPTIONAL is the default for all databases except Oracle. For Oracle, whether or not a parameter is optional is automatically determined when retrieving the stored procedure definition.

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

parameter_name = {source_column | OGG_function}

Maps the name of a parameter to a column or function that provides the input. The following data types are supported by SQLEXEC for input and output parameters.

  • Numeric data types

  • Date data types

  • Character data types

parameter_name is one of the following:

  • For a stored procedure, it is the name of any parameter in the procedure that can accept input.

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

    SQLEXEC (ID appphone, QUERY ' select per_type from ps_personal_data '
    ' where emplid = :vemplid '
    ' and per_status = 'N' and per_type = 'A' ',
    PARAMS (vemplid = emplid)),
TOKENS (applid = @GETVAL(appphone.per_type));

  • 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 parameter_name entries are p1 and p2. Consider whether the database requires the p to be upper or lower case. 

    SQLEXEC (ID appphone, QUERY ' select per_type from ps_personal_data '
    ' where emplid = ? '
    ' and per_status = 'N' and per_type = 'A' ',
    PARAMS (p1 = emplid)),
TOKENS (applid = @GETVAL(appphone.per_type));

source_column is the name of a source column that provides the input. By default, if the specified column is not present in the log (because the record only contains the values of columns that were updated) the parameter assumes any default value specified by the procedure or query for the parameter.

OGG_function is the name of an Oracle GoldenGate column-conversion function that executes to provide the input. See "Column Conversion Functions".

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

{procedure_name | logical_name}.parameter

Where:

  • procedure_name is the actual name of a stored procedure, which must match the value given for SPNAME in the SQLEXEC statement. Use this argument only if executing a procedure one time during the course of the Oracle GoldenGate run.

  • logical_name is the logical name specified with the ID option of SQLEXEC. Use this argument to pass input values from either a query or an instance of a stored procedure when the procedure executes multiple times within a MAP statement.

  • parameter is the name of a parameter or RETURN_VALUE if extracting returned values. By default, output values are truncated at 255 bytes per parameter. If output parameters must be longer, use the MAXVARCHARLEN option.

Note:

As an alternative to the preceding syntax, you can use the @GETVAL function. See "@GETVAL" for more information.

The following examples apply to a set of Oracle source and target tables and a lookup table. These examples show how parameters for the tables are passed for a single instance of a stored procedure and multiple instances of a stored procedure.

Source table cust:

custid                     Number
current_residence_state    Char(2)
birth_state                Char(2)

Target table cust_extended:

custid                          Number
current_residence_state_long    Varchar(30)
birth_state_long                Varchar(30)

Lookup table state_lookup

abbreviation     Char(2)
long_name        Varchar(30)

The following example shows the use of a stored procedure that executes once to get a value from the lookup table. When processing records from the cust table, Oracle GoldenGate executes the lookup stored procedure before executing the column map. The long_name parameter in the procedure accepts input from the birth_state source column.The value is mapped to the target column birth_state_long in the COLMAP statement. 

MAP sales.cust, TARGET sales.cust_extended, &
SQLEXEC (SPNAME lookup, &
PARAMS (long_name = birth_state)), &
COLMAP (custid = custid, &
birth_state_long = lookup.long_name);

The following example shows the use of the ID option to enable multiple executions of a stored procedure that gets values from a lookup table. The values are mapped to target columns. 

MAP sales.cust, TARGET sales.cust_extended, &
SQLEXEC (SPNAME lookup, ID lookup1, &
PARAMS (long_name = current_residence_state)), &
SQLEXEC (SPNAME lookup, ID lookup2, &
PARAMS (long_name = birth_state)), &
COLMAP (custid = custid, current_residence_state_long = lookup1.long_name, & birth_state_long = lookup2.long_name);
AFTERFILTER | BEFOREFILTER

Use AFTERFILTER and BEFOREFILTER to specify when to execute the stored procedure or query in relation to the FILTER clause of a MAP statement.

AFTERFILTER

Causes the SQL to execute after the FILTER statement. This enables you to skip the overhead of executing the SQL unless the filter is successful. This is the default.

BEFOREFILTER

Causes the SQL to execute before the FILTER statement, so the results can be used in the filter.

The following is an example using BEFOREFILTER. 

SQLEXEC (SPNAME check, NOPARAMS, BEFOREFILTER)
ALLPARAMS [OPTIONAL | REQUIRED]

Use ALLPARAMS as a global rule that determines whether or not all of the specified parameters must be present for the stored procedure or query to execute. Rules for individual parameters established within the PARAMS clause override the global rule set with ALLPARAMS.

OPTIONAL

Permits the SQL to execute whether or not all of the parameters are present. This is the default.

REQUIRED

Requires all of the parameters to be present for the SQL to execute.

The following is an example using OPTIONAL. 

SQLEXEC (SPNAME lookup,
PARAMS (long_name = birth_state, short_name = state),
ALLPARAMS OPTIONAL)
ERROR {IGNORE | REPORT | RAISE | FINAL | FATAL}

Use ERROR to define a response to errors associated with the stored procedure or query. Without explicit error handling, the Oracle GoldenGate process abends on errors. Make certain your procedures return errors to the process and specify the responses with ERROR.

IGNORE

Causes Oracle GoldenGate to ignore all errors associated with the stored procedure or query and continue processing. Any resulting parameter extraction results in "column missing" conditions. 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. Oracle GoldenGate continues processing other stored procedures or queries associated with the current MAP statement before processing the error.

FINAL

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

FATAL

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

EXEC {MAP | ONCE | TRANSACTION | SOURCEROW}

Use EXEC to control the frequency with which a stored procedure or query in a MAP statement executes and how long the results are considered valid, if extracting output parameters.

MAP

Executes the procedure or query once for each source-target table map for which it is specified. Using MAP renders the results invalid for any subsequent maps that have the same source table. MAP is the default.

The following example shows the incorrect use of the default of MAP. Because MAP is the default, it need not be explicitly listed in the SQLEXEC statement. In this example, a source table is mapped in separate MAP parameters to two different target tables. In this case, the results are valid only for the first mapping. The results of the procedure lookup are expired by the time the second MAP parameter executes, and the second MAP results in a "column missing" condition. To implement this correctly so that each MAP returns valid results, SOURCEROW should be used. 

MAP sales.srctab, TARGET sales.targtab, &
SQLEXEC (SPNAME lookup, PARAMS (param1 = srccol)), &
COLMAP (targcol = lookup.param2);

MAP sales.srctab, TARGET sales.targtab2, &
COLMAP (targcol2 = lookup.param2);
ONCE

Executes the procedure or query once during the course of the Oracle GoldenGate run, upon the first invocation of the associated MAP statement. The results remain valid for as long as the process remains running.

The following is an example of using ONCE. 

MAP sales.cust, TARGET sales.cust_extended, &
SQLEXEC (SPNAME lookup, PARAMS (long_name = birth_state), EXEC ONCE), &
COLMAP (custid = custid, &
birth_state_long = lookup.long_name);
TRANSACTION

Executes the procedure or query once per source transaction. The results remain valid for all operations of the transaction.

The following is an example of using TRANSACTION. 

MAP sales.cust, TARGET sales.cust_extended, &
SQLEXEC (SPNAME lookup, PARAMS (long_name = birth_state), EXEC TRANSACTION), &
COLMAP (custid = custid, &
birth_state_long = lookup.long_name);
SOURCEROW

Executes the procedure or query once per source row operation. Use this option when you are synchronizing a source table with more than one target table, so that the results of the procedure or query are invoked for each source-target mapping.

The following is an example of using SOURCEROW. In this case, the second map returns a valid value because the procedure executes on every source row operation. 

MAP sales.srctab, TARGET sales.targtab, &
SQLEXEC (SPNAME lookup, PARAMS (param1 = srccol), EXEC SOURCEROW), &
COLMAP (targcol = lookup.param2);

MAP sales.srctab, TARGET sales.targtab2, &
COLMAP (targcol2 = lookup.param2);
MAXVARCHARLEN bytes

Use MAXVARCHARLEN to specify the maximum byte length allocated for the output value of any parameter in a stored procedure or query. Beyond this maximum, the output values are truncated. The default is 255 bytes without an explicit MAXVARCHARLEN clause. The valid range of values is from 50 to 32767 bytes.

The following example limits the byte length of output values to 100.

MAXVARCHARLEN 100
PARAMBUFSIZE bytes

Use PARAMBUFSIZE to specify the maximum number of bytes allowed for the memory buffer that stores SQLEXEC parameter information, including both input and output parameters. The default is 10,000 bytes without an explicit PARAMBUFSIZE clause. The valid range of values is from 1000 to 2000000 bytes. Oracle GoldenGate issues a warning whenever the memory allocated for parameters is within 500 bytes of the maximum.

The following example increases the buffer to 15,000 bytes.

PARAMBUFSIZE 15000
TRACE {ALL | ERROR}

Use TRACE to log SQLEXEC input and output parameters to the report file.

The following is a sample report file with SQLEXEC tracing enabled: 

Input parameter values...
LMS_TABLE: INTERACTION_ATTR_VALUES
   KEY1: 2818249
  KEY2: 1
Report File:
From Table MASTER.INTERACTION_ATTR_VALUES to MASTER.INTERACTION_ATTR_VALUES:
       #  inserts:      0
       #  updates:      0
       #  deletes:      0
       #  discards:     1

  Stored procedure GGS_INTERACTION_ATTR_VALUES:
       attempts:        2
       successful:      0
ALL

Writes the input and output parameters for each invocation of the procedure or query to the report file. This is the default.

ERROR

Writes the input and output parameters for each invocation of the procedure or query to the report file only after a SQL error occurs.