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:
TheSQLEXECONBEFOREIMAGE
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_col
umn |
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 P
n
, 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.
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
.
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