SQLEXECを使用したコマンド、ストアド・プロシージャおよび問合せの実行

Oracle GoldenGateのSQLEXECパラメータによって、ExtractおよびReplicatでデータベースと通信し、次の処理を実行できます。

データベース・コマンド、ストアド・プロシージャまたはSQL問合せの実行によるデータベース機能の実行、結果の返却(SELECT文)またはDML操作の実行(INSERT、UPDATE、DELETE)が可能です。
プロシージャから出力パラメータを取得してFILTER句またはCOLMAP句に入力できます。

ノート:

SQLEXECは最小限のグローバリゼーション・サポートを提供します。ソース・キャプチャのキャプチャ・パラメータ・ファイルでSQLEXECを使用するには、ソース.prmファイルのクライアント文字セットが同じであるか、ソース・データベースの文字セットのスーパーセットであることを確認します。

SQLEXECを使用した処理の実行

SQLEXECによって、Oracle GoldenGateではデータベースのネイティブSQLを使用してカスタム処理命令を実行できるため、Oracle GoldenGateとデータベース両方の機能が拡張されます。

ストアド・プロシージャおよび問合せを使用して、データベースを対象とするデータの選択または挿入、データの集計、データの非正規化または正規化、あるいは入力にデータベース操作を必要とする他の任意の機能を実行できます。Oracle GoldenGateでは、入力を取得するストアド・プロシージャと出力を生成するストアド・プロシージャがサポートされます。
データベース・コマンドを発行して、Oracle GoldenGate処理を効率化するために必要なデータベース機能を実行できます(ターゲット表に対するトリガーを無効化して、その後再度有効化するなど)。

SQLEXECの使用

SQLEXECパラメータは、次の方法で使用できます。

TABLE文またはMAP文の句として
ExtractまたはReplicatのパラメータ・ファイルのルート・レベルに存在するスタンドアロン・パラメータとして

スタンドアロン文としてのSQLEXECの適用

ExtractまたはReplicatのパラメータ・ファイルでスタンドアロン・パラメータ文として使用する場合、SQLEXECは、ストアド・プロシージャ、問合せまたはデータベース・コマンドを実行できます。この場合、このパラメータを特定の表に関連付ける必要はなく、一般的なSQL操作を実行するために使用できます。

たとえば、Oracle GoldenGateのデータベース・ユーザー・アカウントがアイドル時にタイムアウトされるように構成されている場合、SQLEXECを使用して、Oracle GoldenGateが見かけ上アイドル状態とならないように、定義した間隔で問合せを実行できます。別の例としては、SQLEXECを使用して、ターゲット・トリガーの無効化などの重要なデータベース・コマンドを発行できます。スタンドアロンのSQLEXEC文では、入力パラメータを受け入れることや、出力パラメータを戻すことはできません。

パラメータ構文	目的
`SQLEXEC 'call procedure_name()'`	ストアド・プロシージャの実行
`SQLEXEC 'sql_query'`	問合せの実行
`SQLEXEC 'database_command'`	データベース・コマンドの実行

引数説明

引数	説明
`'call procedure_name ()'`	実行するストアド・プロシージャ名を指定します。文は一重引用符で囲む必要があります。例: `SQLEXEC 'call prc_job_count ()'`
`'sql_query'`	実行する問合せ名を指定します。問合せは、全体を1行で記述し、一重引用符で囲む必要があります。大/小文字の区別されるオプション名を、データベースに格納されているとおりに指定します。たとえば、大/小文字が区別されるOracleオブジェクト名の場合は二重引用符で囲みます。 `SQLEXEC 'SELECT "col1" from "schema"."table"'`
`'database_command'`	実行するデータベース・コマンドを指定します。データベースにとって有効なコマンドである必要があります。

'call
procedure_name ()'

実行するストアド・プロシージャ名を指定します。文は一重引用符で囲む必要があります。

例:

SQLEXEC 'call prc_job_count ()'

'sql_query'

実行する問合せ名を指定します。問合せは、全体を1行で記述し、一重引用符で囲む必要があります。

大/小文字の区別されるオプション名を、データベースに格納されているとおりに指定します。たとえば、大/小文字が区別されるOracleオブジェクト名の場合は二重引用符で囲みます。

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

'database_command'

実行するデータベース・コマンドを指定します。データベースにとって有効なコマンドである必要があります。

SQLEXECには、処理動作、メモリー使用およびエラー処理を制御するためのオプションがあります。詳細は、Oracle GoldenGateパラメータおよび機能リファレンスのSQLEXECを参照してください。

TABLE文またはMAP文内でのSQLEXECの適用

TABLE文またはMAP文内で使用する場合、SQLEXECでパラメータを受け渡したり、受け入れたりすることができます。プロシージャおよび問合せに使用できますが、データベース・コマンドには使用できません。

構文

この構文は、TABLE文またはMAP文内でプロシージャを実行します。

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

引数	説明
`SPNAME`	ストアド・プロシージャを実行するための句を開始する必須キーワード。
`sp_name`	実行するストアド・プロシージャの名前を指定します。
`ID logical_name`	プロシージャの論理名を定義します。このオプションを使用して、`TABLE`文または`MAP`文内でプロシージャを複数回実行します。プロシージャを1回のみ実行する場合は不要です。
`PARAMS param_spec \| NOPARAMS`	プロシージャでパラメータを受け入れるかどうかを指定します。これらのオプションのいずれかを使用する必要があります(「入力パラメータと出力パラメータの使用」を参照)。

構文

この構文は、TABLE文またはMAP文内で問合せを実行します。

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

引数説明

引数	説明
`ID logical_name`	問合せの論理名を定義します。問合せの結果から値を抽出するには、論理名が必要です。`ID` `logical_name`は、問合せによって戻される列値を参照します。
`QUERY ' sql_query '`	データベースに対して実行するSQL問合せの構文を指定します。これにより、`SELECT`文を使用して結果を戻すか、`INSERT`文、`UPDATE`文または`DELETE`文を使用してデータベースを変更することが可能になります。問合せは、一重引用符で囲み、全体を1行で記述する必要があります。大/小文字が区別されるオブジェクト名を、データベースに格納されているとおりに指定します。たとえば、Oracleの大/小文字が区別される名前の場合は引用符で囲みます。 `SQLEXEC 'SELECT "col1" from "schema"."table"'`
`PARAMS param_spec \| NOPARAMS`	問合せでパラメータを受け入れるかどうかを定義します。これらのオプションのいずれかを使用する必要があります(「入力パラメータと出力パラメータの使用」を参照)。

ID logical_name

問合せの論理名を定義します。問合せの結果から値を抽出するには、論理名が必要です。ID logical_nameは、問合せによって戻される列値を参照します。

QUERY ' sql_query '

データベースに対して実行するSQL問合せの構文を指定します。これにより、SELECT文を使用して結果を戻すか、INSERT文、UPDATE文またはDELETE文を使用してデータベースを変更することが可能になります。問合せは、一重引用符で囲み、全体を1行で記述する必要があります。大/小文字が区別されるオブジェクト名を、データベースに格納されているとおりに指定します。たとえば、Oracleの大/小文字が区別される名前の場合は引用符で囲みます。

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

PARAMS param_spec |
NOPARAMS

問合せでパラメータを受け入れるかどうかを定義します。これらのオプションのいずれかを使用する必要があります(「入力パラメータと出力パラメータの使用」を参照)。

現在のデータベースではなく、異なるデータベースにある表で問合せを実行する場合は、異なるデータベース名を表で指定する必要があります。データベース名および表名の間の区切り記号はコロン(:)にする必要があります。

使用例を次に示します。

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

入力パラメータと出力パラメータの使用

Oracle GoldenGateでは、TABLE文またはMAP文内のSQLEXECで実行されるプロシージャまたは問合せを対象として入力値と出力値を受け渡すためのオプションを提供しています。

入力パラメータへの値の受渡し

ストアド・プロシージャまたは問合せ内の入力パラメータにデータ値を渡すには、SQLEXECのPARAMSオプションを使用します。

構文

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

説明:

OPTIONALは、SQLの実行にパラメータ値が必要ないことを示します。必要なソース列がデータベース操作で見つからない場合、またはソース列が見つからないために列変換ファンクションを完了できない場合でも、SQLは実行されます。
REQUIREDは、パラメータ値が存在している必要があることを示します。パラメータ値が存在しない場合、SQLは実行されません。
paramは次のいずれかとなります。
- ストアド・プロシージャの場合、ストアド・プロシージャ内の入力を受け付ける任意のパラメータ名(参照表の列など)です。
- Oracle問合せの場合、問合せの任意の入力パラメータの名前(先頭のコロンを除く)です。たとえば、:param1の場合は、PARAMS句ではparam1と指定します。
- Oracle以外の問合せの場合はpn (nは1から始まるパラメータの番号)です。たとえば、パラメータが2個の問合せでは、paramエントリはp1およびp2です。
{source_column | function}は、プロシージャへの入力を提供する列またはOracle GoldenGate変換関数です。

出力パラメータへの値の受渡し

ストアド・プロシージャまたは問合せからの値を入力値としてFILTER句またはCOLMAP句に渡すには、次の構文を使用します。

構文

{procedure_name | logical_name}.parameter

説明:

procedure_nameは、ストアド・プロシージャの実際の名前です。この引数は、現在のOracle GoldenGateプロセスの有効期間中にプロシージャを1回実行する場合にのみ使用します。
logical_nameは、SQLEXECのIDオプションで指定した論理名です。この引数は、問合せを実行する場合、またはストアド・プロシージャを複数回実行する場合に使用します。
parameterは、パラメータの名前またはRETURN_VALUE (戻り値を抽出する場合)です。

パラメータを使用するSQLEXECの例

これらの例では、入力パラメータと出力パラメータを使用するストアド・プロシージャと問合せを使用します。

ノート:

プロシージャまたは問合せにパラメータが含まれる場合、追加のSQLEXECオプションを使用できます。Oracle GoldenGateパラメータおよび機能リファレンスのSQLEXECを参照してください。

例11-3 ストアド・プロシージャを使用するSQLEXEC

この例では、SQLEXECを使用して、コードに基づいて説明を戻す問合せを実行するLOOKUPというストアド・プロシージャを実行します。その後、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;

MAP文の内容:

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は、LOOKUPストアド・プロシージャを実行します。SQLEXEC句内のPARAMS (code_param = account_code)文では、code_paramをプロシージャ・パラメータとして識別し、account表のaccount_code列から入力値を受け入れます。

Replicatでは、列マップを実行する前にLOOKUPストアド・プロシージャが実行されるため、COLMAP句によりその結果を抽出してnewacct_val列にマップできます。

例11-4 問合せを使用するSQLEXEC

この例では、前述の例で使用されているものと同じロジックを実装していますが、ストアド・プロシージャのかわりにSQL問合せを実行し、列マップで@GETVAL関数を使用します。

問合せは1行である必要があります。また、Oracle GoldenGateのパラメータ文を複数の行に分割する場合、行末にアンパサンド(&)が必要です。

Oracleデータベースの問合せ:

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

Oracle以外のデータベースの問合せ:

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

SQLEXECエラーの処理

SQLEXECを実装する場合に考慮する必要のあるエラー状態には、次の2つのタイプがあります。

列マップで、ソース・データベースの操作から欠落している列が必要とされる場合。これは、データベースで、すべての列の値でなく、変更された列の値だけが記録される場合の更新操作で発生する可能性があります。デフォルトでは、必要な列が欠落している場合、またはOracle GoldenGate列変換関数で列の欠落状態が発生した場合には、ストアド・プロシージャは実行されません。その後、ストアド・プロシージャから出力パラメータを抽出しようとすると、COLMAP句またはFILTER句で列の欠落状態が発生します。
データベースでエラーが生成された場合。

データベース・エラーの処理

SQLEXEC句のERRORオプションを使用して、Oracle GoldenGateに次のいずれかの方法でレスポンスするように指示します。

表11-2 ERRORオプション

アクション	説明
IGNORE	Oracle GoldenGateに、ストアド・プロシージャまたは問合せに関連するすべてのエラーを無視させ、処理を継続させます。どのパラメータ抽出の結果も、列の欠落状態となります。これはデフォルトです。
REPORT	ストアド・プロシージャまたは問合せに関連するすべてのエラーが破棄ファイルにレポートされるようにします。このレポートは、エラーの原因の追跡に役立ちます。ここには、エラーの説明と、プロシージャまたは問合せとやり取りしたパラメータの値の両方が含まれます。Oracle GoldenGateで、エラーのレポート後、処理が続けられます。
RAISE	Replicatのパラメータ・ファイルに指定された`REPERROR`パラメータの設定によるルールに従ってエラーを処理します。Oracle GoldenGateは、エラーを処理する前に、現在の`TABLE`文または`MAP`文に関連付けられた他のストアド・プロシージャまたは問合せの処理を継続します。
FINAL	実行方法は`RAISE`とほぼ同じですが、プロシージャまたは問合せに関連付けられたエラーが発生すると、残りのすべてのストアド・プロシージャおよび問合せは省略されます。エラー処理は、エラーの直後にコールされます。
FATAL	プロシージャまたは問合せに関連するエラーを検出したときに、即座にOracle GoldenGateを異常終了させます。

紛失した列値の処理

@COLTEST関数を使用して、渡されたパラメータの結果をテストし、必要に応じて欠落値を埋めるために列の代替値をマップします。別の方法として、列値を使用できるようにするため、TABLEパラメータのFETCHCOLSオプションまたはFETCHCOLSEXCEPTオプションを使用して、ログに存在しない値をデータベースからフェッチできます。列をフェッチするかわりに、該当する列のサプリメンタル・ロギングを有効化できます。

SQLEXECのその他のガイドライン

次のSQLEXECガイドラインに従ってください。

TABLEまたはMAPの1つのエントリで、最大20のプロシージャまたは問合せを実行できます。これらは、パラメータ文にリストされている順序で実行されます。
SQLEXEC句よりも先に、Oracle GoldenGateユーザーのデータベース・ログイン情報を指定する必要があります。データベース・タイプおよび構成済の認証方式に応じて、Extractパラメータ・ファイルのSOURCEDBおよびUSERIDALIASパラメータ、またはReplicatパラメータ・ファイルのTARGETDBおよびUSERIDALIASパラメータを使用します。
SQLは、Oracle GoldenGateユーザーによって実行されます。このユーザーは、ストアド・プロシージャを実行し、RDBM提供のプロシージャをコールする権限を持っている必要があります。
ストアド・プロシージャまたは問合せ内のデータベース操作は、元のトランザクションと同じコンテキストでコミットされます。
SQLEXECを使用して主キー列の値を更新しないでください。SQLEXECを使用してキー列の値を更新すると、元のキー値が使用できなくなるため、Replicatプロセスで後続の更新操作または削除操作を実行できなくなります。キー値を変更する必要がある場合、元のキー値を別の列にマップして、TABLEパラメータまたはMAPパラメータのKEYCOLSオプションを使用してその列を指定できます。
Db2では、Oracle GoldenGateはODBCのSQLExecDirect関数を使用してSQL文を動的に実行します。つまり、接続先のデータベース・サーバーは、文を動的に準備できる必要があります。ODBCは、実行のたびに(リクエストされる間隔で)SQL文を準備します。通常は、このことがOracle GoldenGateユーザーの問題になることはありません。詳細は、IBM Db2のドキュメントを参照してください。
SQLEXEC文中のオブジェクト名はすべて、データベースに応じて、2つまたは3つの部分からなる完全修飾名である必要があります。
SQLEXECのストアド・プロシージャまたは問合せの影響を受けるすべてのオブジェクトは、SQLの実行前に適切な構造で存在している必要があります。したがって、これらのオブジェクトの構造に影響するDDL (CREATEやALTERなど)は、SQLEXECの実行前に実行される必要があります。
単独SQLEXEC文によって影響を受けるすべてのオブジェクトは、Oracle GoldenGateプロセスの起動前に存在している必要があります。このため、DDLサポートは、これらのオブジェクトに対して無効にする必要があります。そうしないと、SQLEXECのプロシージャまたは問合せが実行される前に、DDL操作によって構造が変更されたり、オブジェクトが削除されたりする可能性があります。