ユーザー定義型 (UDT) を使用する Oracle ストアドプロシージャーのコールの例 - Mule 4

Oracle では、​ユーザー定義型 (UDT)​ と呼ばれるカスタムデータ型を作成できます。次の例は、Oracle データベースの UDT を使用するストアドプロシージャーをコールする、データベース用 Anypoint Connector (Database Connector) の ​Stored procedure​ 操作を Mule アプリケーション内で設定する方法を示しています。
このストアドプロシージャーでは次の 2 つのアクションを実行します。

  • 100 歳未満の個人の名、姓、年齢、電話番号をテーブルに挿入する

  • テーブルに追加されているすべての個人の電話番号のリストを返す

この例を実行するには、次の操作が必要です。

  • UDT、テーブル、およびストアドプロシージャーを作成するスクリプトを実行する

  • Oracle データベース接続を設定する

  • Mule アプリケーションを作成する

  • prepareStruct​ および ​prepareArray​ DataWeave 関数を使用してアプリケーションデータをカスタム型にマップする

  • アプリケーションを実行し、curl コマンドを使用してアプリケーションをテストする

Studio での Oracle UDT のフロー
Figure 1. UDT を使用する Oracle ストアドプロシージャーのフロー

prepareStruct および prepareArray 関数の使用

以前の Mule バージョンでは ​createStruct​ および ​createArray​ 関数が提供されましたが、これらは非推奨になりました。これらの関数では JDBC 値がすぐに作成されましたが、その値は、Database Connector 操作で使用された接続と同じ接続を使用して作成されている必要があります。新しい ​prepareStruct​ および ​prepareArray​ 関数を使用すると、Database Connector ではこれらの JDBC 値の作成を、適切な SQL 接続が使用可能になるまで遅延することができます。​createArray​ および ​createStruct​ 関数は、以前は Oracle 接続プロバイダーでのみ動作していました。​preprareStruct​ および ​prepareArray​ 関数は、汎用接続プロバイダーやデータソース参照接続プロバイダーなどのすべての接続プロバイダーと互換性が確保されるようになり、接続の負荷分散とフォールトトレランス URI を正常に設定できます。

非推奨関数 アクティブな関数

Db::createArray(configName, typeName, values): java.sql.Array

Db::prepareArray(typeName, values): java.util.List

Db::createStruct(configName, typeName, properties): java.sql.Struct

Db::prepareStruct(typeName, properties): java.util.List

パラメーター

Db::prepareArray(typeName, values): java.util.List​ 関数では、データベースに送信する配列を ​java.sql.Array​ として準備します。

パラメーター 説明

typeName

String (文字列)

作成する Array (配列) 型の名前。

values

List<Object>

データベースに渡す値。

java.util.List

List<Object>

戻り値は、後で配列を作成するためのすべての情報を保持する不変リストです。

Db::prepareStruct(typeName, properties): java.util.List​ 関数では、データベースに送信する​構造​を ​java.sql.Struct​ として準備します。

パラメーター 説明

typeName

String (文字列)

作成する Struct (構造) 型の名前。

properties

List<Object>

データベースに渡すプロパティ。

java.util.List

List<Object>

戻り値は、後で構造を作成するためのすべての情報を保持する不変リストです。

非推奨関数を使用する既存の Mule アプリケーションがある場合、​createStruct​ を ​prepareStruct​ に置き換えて、​createArray​ を ​prepareArray​ に置き換えます。また、最初のパラメーター ​configName​ を関数構造から削除します。

UDT、テーブル、およびストアドプロシージャーの作成

Oracle データベース接続を設定して Mule アプリケーションを作成する前に、任意の SQL クライアントツールを使用して、データベースに次の UDT を作成する ​.sql​ スクリプトを実行します。

  1. PEOPLE

  2. PERSON

  3. PHONE_NUMBER

  4. PHONE_NUMBER_ARRAY

  5. PHONE_BOOK

一部の UDT には別の UDT が含まれるため、UDT を定義する順序が重要になります。たとえば、オブジェクト型 ​PERSON​ は、そのメンバーの ​PHONE_NUMBER_ARRAY​ を定義する前に定義する必要があります。

このスクリプトでは、​HUMANS​ というテーブルと、​PROC_INSERT_HUMANS​ というストアドプロシージャーも作成されます。このストアドプロシージャーの目的は、入力パラメーターとして型 ​PEOPLE​ のオブジェクトを受け取って、そのオブジェクトから 100 歳未満の個人を取得してテーブル ​HUMANS​ に挿入することです。また、このストアドプロシージャーでは、​HUMANS​ テーブルにすでに挿入されているすべての個人の電話番号のリストで構成される型 ​PHONE_BOOK​ の出力パラメーターも定義されています。

この UDT、テーブル、およびストアドプロシージャーを作成するために実行するスクリプトは次のとおりです。

--CREATE CUSTOM DATA TYPES (UDTs)
CREATE OR REPLACE TYPE PHONE_NUMBER AS object(areaCode VARCHAR2(10), phoneNumber VARCHAR2(20));
CREATE OR REPLACE TYPE PHONE_NUMBER_ARRAY AS VARRAY(10) OF PHONE_NUMBER;
CREATE OR REPLACE TYPE PERSON IS OBJECT(firstName VARCHAR(50), surname VARCHAR(50), age NUMBER, phoneNumbers PHONE_NUMBER_ARRAY);
CREATE OR REPLACE TYPE PEOPLE IS TABLE OF PERSON;

CREATE OR REPLACE TYPE PHONE_BOOK AS VARRAY(10) OF VARCHAR2(200);


--CREATE TABLE HUMANS;
CREATE TABLE HUMANS(
  firstName VARCHAR(50),
  surname VARCHAR(50),
  age NUMBER,
  phoneNumbers PHONE_NUMBER_ARRAY
);

--CREATE STORED PROCEDURE
create or replace procedure PROC_INSERT_HUMANS(people IN PEOPLE, contactList OUT PHONE_BOOK) IS
    phoneBook PHONE_BOOK := PHONE_BOOK();
    BEGIN
        FOR idx IN people.first .. people.last LOOP
            IF people(idx).age < 100 THEN
                INSERT INTO HUMANS(firstName, surname, age, phoneNumbers)
                VALUES(people(idx).firstName, people(idx).surname, people(idx).age, people(idx).phoneNumbers);
            END IF;
        END LOOP;

        FOR humanContact IN (SELECT * FROM  HUMANS h, TABLE(h.phoneNumbers) n) LOOP
            phoneBook.EXTEND(1);
            phoneBook(phoneBook.COUNT) := humanContact.surname || ',' || humanContact.firstname || ':' || humanContact.areaCode ||'-'|| humanContact.phoneNumber;
        END LOOP;

        contactList := phonebook;

        COMMIT;
END PROC_INSERT_HUMANS;

Oracle データベース接続の設定

Oracle データベース内で UDT、テーブル、ストアドプロシージャーを作成したら、Anypoint Studio に移動して Oracle データベース接続を設定します。

  1. Studio で新しい Mule プロジェクトを作成します。

  2. [Global Elements (グローバル要素)]​ ビューに移動します。

  3. [Create (作成)]​ をクリックして、​[Choose Global Type (グローバル種別の選択)]​ ビューを開きます。

  4. [Filter (検索条件)]​ 項目に「​http​」と入力し、​[HTTP Listener config (HTTP リスナー設定)]​ を選択して ​[OK]​ をクリックします。
    この設定は、Mule アプリケーションフローを開始する HTTP ​Listener​ ソース用です。

  5. [HTTP Listener config (HTTP リスナー設定)]​ ウィンドウで、次のパラメーターを設定します。

    • Protocol (プロトコル)​: HTTP (Default)

    • Host (ホスト)​: All Interfaces [0.0.0.0] (default)

    • Port (ポート)​: 8081

  1. [OK]​ をクリックします。

  2. [Global Elements (グローバル要素)]​ ビューで ​[Create (作成)]​ をクリックして ​[Choose Global Type (グローバル種別の選択)]​ ビューを開きます。

  3. [Filter (検索条件)]​ 項目に「​database​」と入力し、​[Database Config (データベース設定)]​ を選択して ​[OK]​ をクリックします。

  4. [Database Config (データベース設定)]​ ウィンドウで ​[Name (名前)]​ 項目を ​dbConfig​ に設定します。

  5. [Connection (接続)]​ 項目で ​[Oracle Connection (Oracle 接続)]​ を選択します。

  6. [Configure (設定)]​ をクリックし、必要な Oracle JDBC ドライバーを追加して、次のいずれかを選択します。

    • Use local file (ローカルファイルを使用)
      ローカルファイルを使用してライブラリをインストールします。

    • Add Maven dependency (Maven 連動関係を追加)
      Maven 連動関係をインストールしてプロジェクトに追加します。

  7. [Connection (接続)]​ セクションで、次のパラメーターを設定します。

    • Host (ホスト)​: localhost

    • Port (ポート)​: 1521

    • User (ユーザー)​: SYS as SYSDBA

    • Password (パスワード)​: Oradoc_db1

    • Instance (インスタンス)​: ORCLCDB

  8. [Advanced (詳細)]​ タブで ​[Column types (列の型)]​ 項目を ​[Edit inline (インライン編集)]​ に設定します。

  9. プラス記号 ​(+)​ をクリックして、新しい列の型を追加し、すでに作成されているすべての UDT をリストします。

  10. 次の値を ​[Id]​ および ​[Type name (型名)]​ 項目に指定します。

    Id 型名

    2003

    PEOPLE

    2003

    PHONE_NUMBER

    2008

    PERSON

    2003

    PHONE_ARRAY

    2003

    PHONE_BOOK

  11. [Finish (完了)]​ をクリックして、​[Column Type (列の型)]​ ウィンドウを閉じます。

  12. [Test Connection (接続をテスト)]​ をクリックして、Mule がデータベースに接続できることを確認します。

  13. [OK]​ をクリックします。

次のスクリーンショットは、Studio での HTTP リスナーのグローバル要素設定を示しています。

HTTP リスナーのグローバル要素設定
Figure 2. HTTP リスナー設定

次のスクリーンショットは、Studio でのデータベースのグローバル要素設定を示しています。

「Host (ホスト)」、「Port (ポート)」、「User (ユーザー)」、「Password (パスワード)」項目を設定するデータベースのグローバル要素設定。
Figure 3. [Database Config (データベース設定)] の [General (一般)] 設定
「Column types (列の型)」 項目を設定するデータベースのグローバル要素詳細設定
Figure 4. [Database Config (データベース設定)] の [Advanced (詳細)] 設定

Mule アプリケーションの作成、実行、テスト

Oracle データベース接続を設定したら、Mule アプリケーションを作成、実行、およびテストして、ストアドプロシージャーをコールします。

HTTP リスナーと Set Payload コンポーネントの設定

Mule フローを作成する手順は、次のとおりです。

  1. [Mule Palette (Mule パレット)]​ ビューで、[HTTP] の ​[Listener]​ ソースを選択してキャンバスにドラッグします。
    このソースは受信 HTTP メッセージ属性をリスンすることでフローを開始します。

  2. [Connector configuration (コネクタ設定)]​ 項目で ​HTTP_Listener_config​ グローバル設定を選択します。

  3. [Path (パス)]​ 項目を ​/oracle​ に設定します。

  4. [Set Payload]​ コンポーネントを ​[Listener]​ の右にドラッグします。
    このコンポーネントでは、処理のためにストアドプロシージャーに送信する項目のリストを作成します。

  5. [Value (値)]​ 項目で、リストする項目を指定します。

    [{'name':'Anthony J', 'surname':'Crowley', 'age': 6000, 'phoneNumber': {'areaCode':'020', 'phoneNumber': '777'}},
    	{'name':'A.Z', 'surname':'Fell', 'age': 6000, 'phoneNumber':{'areaCode':'020', 'phoneNumber': '888'}},
    	{'name':'Adam', 'surname':'Young', 'age': 11, 'phoneNumber':{'areaCode':'046', 'phoneNumber': '666'}},
    	{'name':'Anathema', 'surname':'Device', 'age': 27, 'phoneNumber':{'areaCode':'020', 'phoneNumber': '123'}},
    	]
  6. [Mime Type (MIME タイプ)]​ 項目を ​application/java​ に設定します。

Set payload コンポーネントの設定
Figure 5. Set Payload の設定

DataWeave 関数を使用した Transform Message コンポーネントの設定

Transform Message​ コンポーネントと、アプリケーションデータをカスタムユーザーデータ型の例にマップする ​prepareStruct​ および ​prepareArray​ DataWeave 関数を使用して、Mule アプリケーションの作成を継続します。

  1. [Transform Message]​ コンポーネントを ​[Set Payload]​ の右にドラッグします。
    このコンポーネントでは、ストアドプロシージャーで入力パラメーターとして想定されているデータ型 ​PEOPLE​ にマップできるオブジェクトに JSON オブジェクトが変換されます。

  2. Studio キャンバスでコンポーネントをダブルクリックして、名前を ​Transform Message - Prepare UDT​ に設定します。

  3. コンポーネントの ​[Output (出力)]​ ソースコードビューで ​[Edit Current Target (現在の対象を編集)]​ ボタンをクリックします。

  4. [Selection dialog (選択ダイアログ)]​ ボックスで ​[Output (出力)]​ 項目を ​Variable​、​[Variable name (変数名)]​ を ​in_people_tab​ に設定し、​[OK]​ をクリックします。

  5. [Output (出力)]​ 項目で、​prepareStruct​ および ​prepareArray​ 関数が含まれる次の DataWeave 式を指定します。

    %dw 2.0
    output application/java
    
    fun toPhoneNumberArray(phoneNumber) = Db::prepareArray("PHONE_NUMBER_ARRAY",[Db::prepareStruct("PHONE_NUMBER", [phoneNumber.areaCode, phoneNumber.phoneNumber])])
    fun toPerson(person) = Db::prepareStruct("PERSON", [person.name, person.surname, person.age, toPhoneNumberArray(person.phoneNumber)])
    ---
    Db::prepareArray("PEOPLE", payload map (item, index) -> ( toPerson(item) ) )
    Transform Message の設定
    Figure 6. Transform Message の設定

prepareStruct​ では、データベース操作の実行時に ​Name​ 型および対応するプロパティに基づいて JDBC 構造オブジェクトを作成するように Mule に指示します。この例では、型 ​PERSON​ と ​PHONE_NUMBER​ が構造オブジェクトです。この関数では、次のパラメーターがこの順序で想定されます。

  • typeName
    作成する ​Struct​ 型の名前を含む文字列 (この例では ​PERSON​ または ​PHONE_NUMBER​)

  • values
    Struct​ プロパティに準拠する値の配列 (この例では ​PHONE_NUMBER​、​['046', '666']​)

Mule アプリケーションで Database Connector 操作を実行するときに、​prepareArray​ 関数では、​Array​ 型に基づいて JDBC 配列オブジェクトを作成するように Mule に指示します。この例では、型 ​PHONE_NUMBER_ARRAY​、​PEOPLE​、および ​PHONE_BOOK​ が配列オブジェクトです。この関数では、次のパラメーターがこの順序で想定されます。

  • typeName
    作成する ​Array​ 型の名前を含む文字列 (この例では ​PEOPLE​ または ​PHONE_NUMBER_ARRAY​)

  • values
    Array​ 型に準拠する値の配列 (この例では ​prepareStruct​ 関数を使用して作成された、各配列が ​PERSON​ オブジェクトを表す配列)

prepareStruct​ および ​prepareArray​ 関数では、要求された SQL 値を作成するためのすべての情報を保持する特別な配列が返されます。操作時にパラメーターデータが再帰的に分析され、これらの配列が適切な SQL オブジェクトに置き換えられます。

これらの関数を組み合わせて使用して、サブタイプまたはネストされた型を作成できます。この例では、型 ​PERSON​ に ​PHONE_NUMBER_ARRAY​ オブジェクトが含まれます。同時に型 ​PHONE_NUMBER_ARRAY​ が ​PHONE_NUMBER​ の配列として定義されます。

また、​Array​ 型に基づいて UDT に一致させるときに ​prepareArray​ 関数を使用する場合、複合型に合わせてこれらの関数を組み合わせることができます。

Stored Procedure 操作の設定

Database Connector の ​Stored Procedure​ 操作を使用して、UDT を使用する Oracle ストアドプロシージャーをコールします。

  1. [Stored procedure]​ 操作を ​[Transform Message]​ の右にドラッグします。
    この操作では、Database Connector を使用してストアドプロシージャーがコールされます。

  2. [Connector configuration (コネクタ設定)]​ 項目で ​dbConfig​ グローバル設定を選択します。

  3. [SQL Query Text (SQL クエリテキスト)]​ 項目に「​{ call proc_insert_humans(:people, :phoneBook) }​」と入力します。

  4. [Input Parameters (入力パラメーター)]​ 項目に「​{ people: vars.in_people_tab}​」と入力します
    このステップでは、​in_people_tab​ という変数に保存された変換の出力が入力パラメーターにマップされます。

  5. [Output Parameters (出力パラメーター)]​ 項目で ​Edit inline​ を選択し、プラス記号 (​+​) をクリックしてカスタムパラメーターを設定します。

    • Key (キー)​: phoneBook

    • Custom type (カスタム型)​: PHONE_BOOK

ストアドプロシージャーの設定
Figure 7. ストアドプロシージャーの設定
  1. 2 つ目の ​[Transform Message]​ コンポーネントを ​[Stored Procedure]​ の右にドラッグします。

  2. Studio キャンバスでコンポーネントをダブルクリックして、名前を ​Transform Message - response to JSON​ に設定します。

  3. コンポーネントの ​[Output (出力)]​ ソースコードビューで、次の DataWeave 式を設定します。

%dw 2.0
output application/json
---
payload

Mule アプリケーションの実行とテスト

Mule アプリケーションを実行およびテストする手順は、次のとおりです。

  1. Studio でプロジェクトを保存します。

  2. ターミナルで curl コマンド「​curl localhost:8081/oracle​」を使用して、アプリケーションをテストします。
    このストアドプロシージャーでは、テーブル ​HUMANS​ に追加されているすべての個人の電話番号のリストが返されます。

UDT を使用する Oracle ストアドプロシージャーをコールするための XML

この例のフローをすばやく Mule アプリケーションに読み込むには、次のコードを Studio XML エディターに貼り付けます。

<?xml version="1.0" encoding="UTF-8"?>

<mule xmlns:ee="http://www.mulesoft.org/schema/mule/ee/core"
	xmlns:db="http://www.mulesoft.org/schema/mule/db" xmlns:http="http://www.mulesoft.org/schema/mule/http"
	xmlns="http://www.mulesoft.org/schema/mule/core"
	xmlns:doc="http://www.mulesoft.org/schema/mule/documentation" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="
http://www.mulesoft.org/schema/mule/ee/core http://www.mulesoft.org/schema/mule/ee/core/current/mule-ee.xsd http://www.mulesoft.org/schema/mule/core http://www.mulesoft.org/schema/mule/core/current/mule.xsd
http://www.mulesoft.org/schema/mule/http http://www.mulesoft.org/schema/mule/http/current/mule-http.xsd
http://www.mulesoft.org/schema/mule/db http://www.mulesoft.org/schema/mule/db/current/mule-db.xsd">

	<http:listener-config name="HTTP_Listener_config" >
		<http:listener-connection host="0.0.0.0" port="8081" />
	</http:listener-config>

	<db:config name="dbConfig" >
		<db:oracle-connection host="localhost" user="SYS as SYSDBA" password="Oradoc_db1" instance="ORCLCDB">
			<db:column-types>
				<db:column-type id="2003" typeName="PEOPLE"/>
				<db:column-type id="2003" typeName="PHONE_NUMBER"/>
				<db:column-type id="2008" typeName="PERSON" />
				<db:column-type id="2003" typeName="PHONE_NUMBER_ARRAY"/>
				<db:column-type id="2003" typeName="PHONE_BOOK"/>
			</db:column-types>
		</db:oracle-connection>
	</db:config>

	<flow name="oracle-udtsFlow" >
		<http:listener config-ref="HTTP_Listener_config" path="/oracle"/>

		<set-payload value="#[[{'name':'Anthony J', 'surname':'Crowley', 'age': 6000, 'phoneNumber': {'areaCode':'020', 'phoneNumber': '777'}},
	{'name':'A.Z', 'surname':'Fell', 'age': 6000, 'phoneNumber':{'areaCode':'020', 'phoneNumber': '888'}},
	{'name':'Adam', 'surname':'Young', 'age': 11, 'phoneNumber':{'areaCode':'046', 'phoneNumber': '666'}},
	{'name':'Anathema', 'surname':'Device', 'age': 27, 'phoneNumber':{'areaCode':'020', 'phoneNumber': '123'}},
	]]" mimeType="application/java"/>


	<ee:transform doc:name="Transform Message - Prepare UDT">
			<ee:variables>
				<ee:set-variable variableName="in_people_tab"><![CDATA[%dw 2.0
				output application/java
				fun toPhoneNumberArray(phoneNumber) = Db::prepareArray("PHONE_NUMBER_ARRAY",[Db::prepareStruct("PHONE_NUMBER", [phoneNumber.areaCode, phoneNumber.phoneNumber])])
				fun toPerson(person) = Db::prepareStruct("PERSON", [person.name, person.surname, person.age, toPhoneNumberArray(person.phoneNumber)])
				---
				Db::prepareArray("PEOPLE", payload map (item, index) -> ( toPerson(item) ) )
				]]></ee:set-variable>
			</ee:variables>
		</ee:transform>
		<db:stored-procedure config-ref="dbConfig">
			<db:sql><![CDATA[{ call proc_insert_humans(:people, :phoneBook) }]]></db:sql>
			<db:input-parameters><![CDATA[{
				people: vars.in_people_tab
			}]]></db:input-parameters>
			<db:output-parameters >
				<db:output-parameter key="phoneBook" customType="PHONE_BOOK" />
			</db:output-parameters>
		</db:stored-procedure>
		<ee:transform doc:name="Transform Message - response to JSON" >
			<ee:message >
				<ee:set-payload ><![CDATA[%dw 2.0
					output application/json
					---
					payload]]></ee:set-payload>
			</ee:message>
		</ee:transform>
	</flow>

</mule>