Contact Us 1-800-596-4880

Call Oracle Stored Procedure That Uses User-Defined Types (UDT) Example - Mule 4

Oracle enables you to create your custom data types, known as user-defined types (UDTs). The following example shows how to configure Anypoint Connector for Database (Database Connector) Stored procedure operation in a Mule application to call a stored procedure that uses UDTs from an Oracle database.
The stored procedure performs two actions:

  • Inserts the first name, last name, age, and phone number of people whose age is less than 100 into a table

  • Returns a list of phone numbers of all the people added to the table

To accomplish this example, you must:

  • Run a script to create UDTs, the table, and the stored procedure

  • Configure an Oracle database connection

  • Create the Mule application

  • Use prepareStruct and prepareArray DataWeave functions to map application data to custom types

  • Run and test the application with curl commands

The Oracle UDTs flow in Studio
Figure 1. Oracle Stored procedure with UDTs Flow

Using prepareStruct and prepareArray Functions

Previous Mule versions provided the createStruct and createArray functions, which are now deprecated. These functions created JDBC values right away, when they must have been created with the same connection used in the Database Connector operation. The new prepareStruct and prepareArray functions enable Database Connector to defer these JDBC values creation until the right SQL connection is available. The createArray and createStruct functions used to work only with an Oracle Connection provider. Now, the preprareStruct and prepareArray functions are compatible with all connection providers, such as the Generic Connection provider or the DataSource Reference connection provider for example, where you can successfully configure load balancing and fault tolerance URI for the connection.

Deprecated Function Active Function

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

Parameters

The Db::prepareArray(typeName, values): java.util.List function prepares an array to send to the database as a java.sql.Array.

Parameter Type Description

typeName

String

Name of the Array type to create.

values

List<Object>

Values to pass to the database.

java.util.List

List<Object>

The return value is an immutable list that holds all the information for creating an array later.

The Db::prepareStruct(typeName, properties): java.util.List function prepares a struct to send to the database as a java.sql.Struct.

Parameter Type Description

typeName

String

Name of the Struct type to create.

properties

List<Object>

Properties to pass to the database.

java.util.List

List<Object>

The return value is an immutable list that holds all the information for creating a struct later.

If you have existing Mule apps that use the deprecated functions, replace createStruct with prepareStruct and createArray with prepareArray. Additionally, remove the first parameter configName from the function structure.

Create the UDTs, the Table and the Stored Procedure

Before configuring the Oracle database connection and creating the Mule application, use your desired SQL client tool to run an .sql script that creates in the database, these UDTs:

  1. PEOPLE

  2. PERSON

  3. PHONE_NUMBER

  4. PHONE_NUMBER_ARRAY

  5. PHONE_BOOK

Because some UDTs contain others, the order in which they are defined is important. For example, the object type PERSON must be defined before its member PHONE_NUMBER_ARRAY is defined.

The script also creates a table called HUMANS and a stored procedure called PROC_INSERT_HUMANS. This stored procedure is intended to receive as an input parameter an object of type PEOPLE, from which it takes all the people whose age is less than 100 and inserts them into the table HUMANS. The stored procedure also defines an output parameter of type PHONE_BOOK that comprises a list of the phone numbers of all the people previously inserted into the HUMANS table

The script that runs to create these UDTs, the table, and the stored procedure is as follows:

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

Configure an Oracle Database Connection

After you create your UDTs, the table and stored procedure in the Oracle database, go to Anypoint Studio to configure the Oracle database connection:

  1. Create a new Mule project in Studio.

  2. Navigate to the Global Elements view.

  3. Click Create to open the Choose Global Type view.

  4. In the Filter field, type http, select HTTP Listener config, and click OK.
    This configuration is for the HTTP Listener source that initiates the Mule application flow.

  5. In the HTTP Listener config window, set the following parameters:

    • Protocol: HTTP (Default)

    • Host: All Interfaces [0.0.0.0] (default)

    • Port: 8081

  1. Click OK.

  2. In the Global Elements view, click Create to open the Choose Global Type view.

  3. In the Filter field, type database, select Database Config, and click OK.

  4. In the Database Config window, set the Name field to dbConfig.

  5. In the Connection field, select Oracle Connection.

  6. Click Configure to add the required Oracle JDBC driver and select either:

    • Use local file
      Installs the library by using a local file.

    • Add Maven dependency
      Installs a Maven dependency to add to the project.

  7. In the Connection section, set the following parameters:

    • Host: localhost

    • Port: 1521

    • User: SYS as SYSDBA

    • Password: Oradoc_db1

    • Instance: ORCLCDB

  8. On the Advanced tab, set the Column types field to Edit inline.

  9. Click the plus sign (+) to add new column types to list all the UDTs that were previously created.

  10. Specify the following values in the Id and Type name fields:

    Id Type name

    2003

    PEOPLE

    2003

    PHONE_NUMBER

    2008

    PERSON

    2003

    PHONE_ARRAY

    2003

    PHONE_BOOK

  11. Click Finish to close the Column type window.

  12. Click Test Connection to confirm that Mule can connect to the database.

  13. Click OK.

The following screenshot shows the HTTP Listener global element configuration in Studio:

The HTTP Listener global element configuration
Figure 2. HTTP Listener configuration

The following screenshots show the Database global element configuration in Studio:

The database global element configuration setting the Host, Port, User, and Password fields.
Figure 3. Database Config General configuration
The database global element advanced configuration setting the Column types field
Figure 4. Database Config Advanced configuration

Create, Run and Test the Mule Application

After you configure the Oracle database connection, create, run an test the Mule application to call the stored procedure.

Configure the HTTP Listener and Set Payload Component

To create the Mule flow:

  1. In the Mule Palette view, select the HTTP Listener source and drag it on to the canvas.
    The source initiates the flow by listening for incoming HTTP message attributes.

  2. In the Connector configuration field, select HTTP_Listener_config global configuration.

  3. Set the Path field to /oracle.

  4. Drag a Set Payload component to the right of Listener.
    This component creates a list of items to send to the stored procedure for processing.

  5. In the Value field, specify items to be listed:

    [{'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. Set the Mime Type field to application/java.

The Set payload component configuration
Figure 5. Set Payload configuration

Configure the Transform Message Component with DataWeave Functions

Continue creating the Mule application using a Transform Message component with prepareStruct and prepareArray DataWeave functions that map application data to the example custom user data types:

  1. Drag a Transform Message component to the right of Set Payload.
    This component transforms the JSON objects into an object that can be mapped to the data type PEOPLE that the stored procedure expects as an input parameter.

  2. Double-click the component in the Studio canvas and set the name to Transform Message - Prepare UDT.

  3. In the Output source code view of the component, click the Edit Current Target button.

  4. In the Selection dialog box, set the Output field to Variable and the Variable name to in_people_tab, and then click OK.

  5. In the Output field, specify the following DataWeave expression that contains the prepareStruct and prepareArray functions:

    %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 configuration
    Figure 6. Transform Message configuration

The prepareStruct instructs Mule to create JDBC struct objects based on the Name type and their corresponding properties when a database operation is performed. In the example, types PERSON and PHONE_NUMBER are struct objects. The function expects the following parameters in order:

  • typeName
    A string with the name of the Struct type to create: in this example, PERSON or PHONE_NUMBER

  • values
    An array of values that conforms to the Struct properties: in this example, for PHONE_NUMBER, ['046', '666']

When the Mule app performs a Database Connector operation, the prepareArray function instructs Mule to create JDBC array objects based on the Array type. In this example, types PHONE_NUMBER_ARRAY, PEOPLE, and PHONE_BOOK are array objects. The function expects the following parameters in order:

  • typeName
    A string with the name of the Array type to create: in this example, PEOPLE or PHONE_NUMBER_ARRAY

  • values
    An array of values that conforms to the Array type: in this example, is an array of arrays, each of those representing PERSON objects, created using the prepareStruct function

Both prepareStruct and prepareArray functions return special arrays that hold all the information to create the requested SQL values. At operation time the parameter data is recursively analyzed and these arrays are replaced with the proper SQL object.

Note that you can combine and use these functions to create subtypes or nested types. In this example, the type PERSON contains the PHONE_NUMBER_ARRAY objects. At the same time, the type PHONE_NUMBER_ARRAY is defined as an array of PHONE_NUMBER.

Additionally, you can combine these functions for complex types if you use the prepareArray function when you are matching to a UDT based on the Array type.

Configure the Stored Procedure Operation

Use the Database Connector Stored Procedure operation to call the Oracle stored procedure with UDTs:

  1. Drag a Stored procedure operation to the right of Transform Message.
    This operation calls the stored procedure using Database Connector.

  2. In the Connector configuration field, select dbConfig global configuration.

  3. In the SQL Query Text field, enter { call proc_insert_humans(:people, :phoneBook) }.

  4. In the Input Parameters field, enter { people: vars.in_people_tab}
    This step maps the output of the transformation, which is stored in the variable called in_people_tab to the input parameter.

  5. In the Output Parameters field, select Edit inline and click the plus sign (+) to set a custom parameter:

    • Key: phoneBook

    • Custom type: PHONE_BOOK

Stored procedure configuration
Figure 7. Stored procedure configuration
  1. Drag a second Transform Message component to the right of Stored procedure.

  2. Double-click the component in the Studio canvas and set the name to Transform Message - response to JSON.

  3. In the Output source code view of the component, set the following DataWeave expression:

%dw 2.0
output application/json
---
payload

Run and Test the Mule Application

To complete and test the Mule application:

  1. Save the project in Studio.

  2. Test the app by using the following curl command in your terminal: curl localhost:8081/oracle.
    The stored procedure should return the list of phone numbers of all the people added to the table HUMANS.

XML for Calling Oracle Stored Procedure with UDTs

Paste this code into your Studio XML editor to quickly load the flow for this example into your Mule app:

<?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>
View on GitHub