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

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

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

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

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

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

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

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

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

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

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

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​ コンポーネントと、アプリケーションデータをカスタムユーザデータ型の例にマップする ​createStruct​ および ​createArray​ 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 (出力)]​ 項目で、​createStruct​ および ​createArray​ 関数が含まれる次の DataWeave 式を指定します。

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

createStruct​ 関数では、​Name​ 型および対応するプロパティに基づいて JDBC 構造オブジェクトが作成されます。この例では、型 ​PERSON​ と ​PHONE_NUMBER​ が構造オブジェクトです。この関数では、次のパラメータがこの順序で想定されます。

  • configName
    Struct​ 型を作成する設定の名前を含む文字列 (この例では ​dbConfig​)

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

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

createArray​ 関数では、​Array​ 型に基づいて JDBC 配列オブジェクトが作成されます。この例では、型 ​PHONE_NUMBER_ARRAY​、​PEOPLE​、および ​PHONE_BOOK​ が配列オブジェクトです。この関数では、次のパラメータがこの順序で想定されます。

  • configName
    Array​ 型を作成する設定の名前を含む文字列 (この例では ​dbConfig​)

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

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

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

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

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 Parametrs (出力パラメータ)]​ 項目で ​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::createArray("dbConfig", "PHONE_NUMBER_ARRAY",[Db::createStruct("dbConfig", "PHONE_NUMBER", [phoneNumber.areaCode, phoneNumber.phoneNumber])])
				fun toPerson(person) = Db::createStruct("dbConfig", "PERSON", [person.name, person.surname, person.age, toPhoneNumberArray(person.phoneNumber)])
				---
				Db::createArray("dbConfig", "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>