SQLEXEC

有效对象

提取和复制

说明

使用 SQLEXEC 参数在 Oracle GoldenGate 处理的上下文中执行存储过程、查询或数据库命令。借助 SQLEXEC，Oracle GoldenGate 可以直接与数据库通信，以执行数据库支持的任何工作。此工作可以是同步流程的一部分，例如检索列转换的值，也可以独立于提取或复制数据，例如执行在数据库中执行操作的存储过程。

注：SQLEXEC 提供最少的全球化支持。要在源捕获的捕获参数文件中使用 SQLEXEC，请确保源 .prm 文件中的客户机字符集是源数据库字符集的相同字符集或超集。

SQLEXEC 的工作方式如下：

注：SQLEXECONBEFOREIMAGE 参数支持对“图像前”记录执行 SQLEXEC。

注意：当 EXEC 类型为 TRANSACTION、SOURCEROW 或 ONCE 时，在 PK 更新或统一更新记录上将忽略 SQLEXECONBEFOREIMAGE 参数。

独立 SQLEXEC

独立 SQLEXEC 参数是在参数文件的根级别使用的参数，并且独立于 TABLE 或 MAP 参数执行操作。以下是使用独立 SQLEXEC 参数的准则。

获取有关使用独立 SQLEXEC 的更多信息

有关如何使用 SQLEXEC 的更多信息，请参见 Use SQLEXEC to Execute Commands，Stored Procedures，and Queries 。

独立 SQLEXEC 的语法

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

调用 <procedure_name ()'>

指定要执行的存储过程的名称。将语句用单引号引起来。call 关键字是必需的。以下是使用独立 SQLEXEC 执行过程的示例：

SQLEXEC 'call prc_job_count ()'

<'SQL_query'>

指定要执行的查询的名称。将查询用单引号引起来。以数据库所需的相同格式指定区分大小写的对象名称。以下是使用独立 SQLEXEC 执行查询的示例：

SQLEXEC ' select x from dual '

对于多行查询，在每行上使用单引号。为了获得最佳效果，请在每个开始引号之后和每个结束引号之前（或至少在每个结束引号之前）键入空格。

<'database_command'>

执行数据库命令。以下是使用独立 SQLEXEC 执行数据库命令的示例：

SQLEXEC 'SET TRIGGERS OFF'

EVERY <n> {SECONDS | MINUTES | HOURS | DAYS}

导致按定义的间隔执行独立存储过程和查询，例如：

SQLEXEC 'call prc_job_count ()' EVERY 30 SECONDS

间隔必须为整数，正整数。

ONEXIT

在 Extract 或 Replicat 进程适度停止时执行 SQL，例如：

SQLEXEC 'call prc_job_count ()' ONEXIT

THREADS (<threadID>[, <threadID>][, ...][, <thread_range>[, <thread_range>][, ...])

仅为协调 Replicat 的指定线程执行 SQLEXEC。

<threadID>[, <threadID>][, ...]

指定一个线程 ID，或以 threadID, threadID, threadID 格式指定逗号分隔的线程列表。

[, <thread_range>[, <thread_range>][, ...]

以 threadIDlow-threadIDhigh 形式指定线程范围，或以 threadIDlow-threadIDhigh，threadIDlow-threadIDhigh 格式指定的逗号分隔的范围列表。

允许组合使用这些格式，例如 threadID、threadID、threadIDlow-threadIDhigh。

如果未使用 THREADS 子句，则 SQL 将由 ADD REPLICAT 命令为此 Replicat 组配置的所有线程执行。但是，如果 SQL 满足屏障事务处理的条件，则无论实际的线程映射如何，线程 0 都处理整个 SQLEXEC 语句。

TABLE 或 MAP 参数中的 SQLEXEC

TABLE 或 MAP 参数中的 SQLEXEC 参数可用于执行不接受参数的存储过程或查询。以下是 MAP 或 TABLE 语句中使用的 SQLEXEC 相关项和限制：

TABLE 或 MAP 中 SQLEXEC 的语法

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>]

执行存储过程。

SPNAME <procedure_name>

指定要执行的过程的名称。

以下示例显示了名为 lookup 的存储过程的单个执行。在这种情况下，将使用过程的实际名称。不需要逻辑名称。

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

ID <logical_name>

定义过程的可选逻辑名称。例如，名为 lookup 的过程的逻辑名称可以是 lookup1、lookup2 等。使用此选项可在 MAP 语句中多次执行该过程。每个 MAP 语句最多可以执行 20 次过程。执行一次过程时，不需要 ID。

以下示例显示了如何使用 ID 选项启用从查找表中获取值的存储过程的多次执行。值将映射到目标列。

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 = lookup
1.long_name, & birth_state_long = lookup2.long_name);

`ID ，查询 ' '

执行查询。

ID <logical_name>

定义查询的逻辑名称。需要提供逻辑名称，以便从查询结果中提取值。<ID logical_name> 引用查询返回的列值。

QUERY '<SQL_query>'

指定要对数据库执行的 SQL 查询语法。该查询可以返回带有 SELECT 语句的结果，也可以执行 INSERT、UPDATE 或 DELETE 语句。SELECT 语句应仅返回一行。如果返回多行，则仅处理第一行。不要为任何 SELECT statements.The 查询指定 INTO ... 子句，对于要对其执行查询的数据库，标准查询语言必须有效。大多数查询都需要输入参数的占位符。在查询中指定参数的方式取决于数据库类型，如下所示：

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

查询必须包含在一行单引号内。任何数据库的参数名称周围不需要引号。

以下示例分别说明了 SQLEXEC 查询用于 Oracle 和 SQL Server 查询。

Oracle 示例：

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 示例：

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

定义过程或查询是否接受参数，如果是，则将参数映射到输入源。必须使用 PARAMS 子句或 NOPARAMS。

OPTIONAL REQUIRED

确定在缺少参数值时是否执行过程或查询。

OPTIONAL 指示 SQL 执行不需要参数值。如果数据库操作中缺少必需的源列，或者由于缺少源列而无法成功完成列转换函数，则 SQL 仍会执行。OPTIONAL 是除 Oracle 以外的所有数据库的默认值。对于 Oracle，在检索存储过程定义时，是否自动确定参数是可选的。

REQUIRED 指示必须存在参数值。如果参数值不存在，则不会执行 SQL。

<parameter_name> = {<source_column> | <OGG_function>}

将参数的名称映射到提供输入的列或函数。SQLEXEC 支持以下数据类型作为输入和输出参数。

<parameter_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));
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> 是提供输入的源列的名称。默认情况下，如果日志中不存在指定的列（因为记录仅包含已更新的列的值），则参数将采用该过程或查询为该参数指定的任何默认值。

<OGG_function> 是为提供输入而执行的 Oracle GoldenGate 列转换函数的名称。请参阅“表和列映射函数”。

要将存储过程中的输出值或查询作为输入传递到 FILTER 或 COLMAP 子句，请使用以下语法：

{<procedure_name> | <logical_name>}.<parameter>

其中：

以下示例适用于一组 Oracle 源表和目标表以及查找表。这些示例说明如何为存储过程的单个实例和存储过程的多个实例传递表的参数。

源表客户：

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

目标表 cust_extended：

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

查找表 state_lookup

abbreviation     Char(2)
long_name        Varchar(30)

以下示例显示了如何使用执行一次的存储过程从查找表中获取值。在处理 cust 表中的记录时，Oracle GoldenGate 将在执行列映射之前执行 lookup 存储过程。过程中的 long_name 参数接受来自 birth_state 源 column.The 值的输入映射到 COLMAP 语句中的目标列 birth_state_long。

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

以下示例显示了如何使用 ID 选项启用从查找表中获取值的存储过程的多次执行。值将映射到目标列。

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

使用 AFTERFILTER 和 BEFOREFILTER 指定何时执行与 MAP 语句的 FILTER 子句相关的存储过程或查询。

AFTERFILTER

使 SQL 在 FILTER 语句之后执行。这样可以跳过执行 SQL 的开销，除非筛选器成功。这是默认设置。

BEFOREFILTER

导致 SQL 在 FILTER 语句之前执行，因此结果可以在筛选器中使用。

下面是使用 BEFOREFILTER 的示例。

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

使用 ALLPARAMS 作为全局规则，确定存储过程或查询是否必须存在所有指定的参数才能执行。在 PARAMS 子句中建立的各个参数的规则将覆盖使用 ALLPARAMS 的全局规则集。

OPTIONAL

允许 SQL 执行是否存在所有参数。这是默认设置。

REQUIRED

需要提供所有参数，SQL 才能执行。

下面是使用 OPTIONAL 的示例。

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

使用 ERROR 定义对与存储过程或查询关联的错误的响应。如果没有显式错误处理，Oracle GoldenGate 进程将异常终止。确保您的过程向进程返回错误并使用 ERROR 指定响应。

使用 Oracle GoldenGate 21c，该功能允许您根据 SQL 错误类型指定不同的行为。

以下示例演示了在执行存储过程查找时检测到 SQL 错误代码 1403 或 1405 的异常终止 ER 进程。将报告所有其他错误并继续复制。

SQLEXEC ( SPNAME lookup,
  PARAMS( long_name = birth_state, short_name = state),
  ERROR REPORT, ERROR FATAL (1403, 1405) );

IGNORE

导致 Oracle GoldenGate 忽略与存储过程或查询关联的所有错误并继续处理。任何生成的参数提取都会导致“列缺失”条件。这是默认设置。

REPORT

确保与存储过程或查询关联的所有错误都报告到放弃文件。该报告可用于跟踪错误原因。它既包括错误说明，也包括传递给过程或查询的参数值。报告错误后，Oracle GoldenGate 将继续处理。

RAISE

根据 REPERROR 参数设置的规则处理错误。在处理错误之前，Oracle GoldenGate 将继续处理与当前 MAP 语句关联的其他存储过程或查询。

FINAL

与 RAISE 类似，只是当遇到与过程或查询关联的错误时，会绕过剩余的存储过程和查询。错误处理在错误之后立即调用。

FATAL

导致 Oracle GoldenGate 在遇到与过程或查询关联的错误时立即异常终止。

EXEC {MAP ONCE TRANSACTION SOURCEROW}

使用 EXEC 控制 MAP 语句中存储过程或查询的执行频率，以及提取输出参数时将结果视为有效的时间。

MAP

对为其指定的每个源 - 目标表映射执行该过程或查询一次。使用 MAP 会使结果对具有相同源表的任何后续映射无效。缺省值为 MAP。

以下示例显示了不正确的使用 MAP 缺省值。由于 MAP 是缺省值，因此无需在 SQLEXEC 语句中显式列出它。在此示例中，源表在单独的 MAP 参数中映射到两个不同的目标表。在这种情况下，结果仅对第一个映射有效。过程 lookup 的结果将在执行第二个 MAP 参数时过期，而第二个 MAP 将导致“列缺失”条件。要正确实现这一点以便每个 MAP 返回有效结果，应使用 SOURCEROW。

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

首次调用关联的 MAP 语句时，在 Oracle GoldenGate 运行过程中执行一次过程或查询。只要该处理仍在运行，结果就会保持有效。

下面是使用 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

对每个源事务处理执行该过程或查询一次。结果对于事务处理的所有操作仍然有效。

下面是使用 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

按源行操作执行该过程或查询一次。在将源表与多个目标表同步时使用此选项，以便为每个源 - 目标映射调用过程或查询的结果。

下面是使用 SOURCEROW 的示例。在这种情况下，第二个映射返回一个有效值，因为该过程在每个源行操作上执行。

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>

使用 MAXVARCHARLEN 指定为存储过程或查询中任何参数的输出值分配的最大字节长度。超过此最大值后，输出值将被截断。缺省值为 255 字节，不带显式 MAXVARCHARLEN 子句。有效值范围为 50 到 32767 字节。

以下示例将输出值的字节长度限制为 100。

MAXVARCHARLEN 100

PARAMBUFSIZE <bytes>

使用 PARAMBUFSIZE 指定存储 SQLEXEC 参数信息的内存缓冲区允许的最大字节数，包括输入和输出参数。缺省值为 10,000 字节，不带显式 PARAMBUFSIZE 子句。有效值范围为 1000 到 2000000 字节。每当为参数分配的内存在最大值的 500 字节内时，Oracle GoldenGate 都会发出警告。

以下示例将缓冲区增加到 15,000 字节。

PARAMBUFSIZE 15000
TRACE {ALL ERROR}

使用 TRACE 将 SQLEXEC 输入和输出参数记录到报告文件中。

下面是启用了 SQLEXEC 跟踪的示例报告文件：

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

将每次调用过程或查询的输入和输出参数写入到报告文件中。这是默认设置。

ERROR

仅在发生 SQL 错误后，才将每次调用过程或查询的输入和输出参数写入报表文件。