平方英呎

有效對象

擷取與 Replicat

說明

您可以使用 SQLEXEC 參數，在 Oracle GoldenGate 處理的相關資訊環境中執行預存程序、查詢或資料庫命令。SQLEXEC 可讓 Oracle GoldenGate 直接與資料庫通訊，以執行資料庫支援的任何工作。此工作可以是同步化程序的一部分，例如擷取資料欄轉換的值，或獨立於擷取或複製資料，例如執行在資料庫中執行動作的預存程序。

注意：SQLEXEC 提供最小的全球化支援。若要在來源擷取的擷取參數檔中使用 SQLEXEC，請確定來源 .prm 檔案中的從屬端字元集與來源資料庫字元集相同或超集。

SQLEXEC 的運作方式如下：

注意：SQLEXECONBEFOREIMAGE 參數支援在「影像之前」記錄上執行 SQLEXEC。

注意：當 EXEC 類型為 TRANSACTION、SOURCEROW 或 ONCE 時，主索引鍵更新或統一更新記錄上會忽略 SQLEXECONBEFOREIMAGE 參數。

獨立 SQLEXEC

獨立的 SQLEXEC 參數是在參數檔的根層次使用，並且獨立於 TABLE 或 MAP 參數。下列為使用獨立 SQLEXEC 參數的準則。

取得使用獨立 SQLEXEC 的詳細資訊

如需有關如何使用 SQLEXEC 的詳細資訊，請參閱使用 SQLEXEC 執行命令、預存程序和查詢。

獨立 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 符合屏障交易的條件，則無論實際繫線對應為何，整個 SQLEXEC 敘述句都會由繫線 0 處理。

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 = ?'

查詢必須包含在單引號內的單行中。在任何資料庫的參數名稱旁邊都不需要引號。

下列範例說明分別針對 Oracle 和 SQL Server 查詢使用 SQLEXEC 查詢。

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 錯誤後，才將程序或查詢每次呼叫的輸入與輸出參數寫入至報告檔案。