---
schema_version: "1"
id: 7b2ffd51c6c982df270aaad276218f594161bfbb65d366bfe950533f6ecde6aa
title: Call Oracle Stored Procedure That Uses User-Defined Types (UDT) Example
collection: mulesoft
version: current
locale: en-us
portal_url: https://docs.salesforce.com/mulesoft/current/en-us/db-connector/latest/database-connector-udt-stored-procedure.md
canonical_url: https://docs.mulesoft.com/db-connector/latest/database-connector-udt-stored-procedure
source_site: docs.mulesoft.com
content_type: documentation
content_hash: 6ecb9a55964ceffe44a46cac74276040cfd00ac3c6866d609a3aba078b154038
description: MuleSoft Documentation Site
release: "1.16"
latest: true
---
---
product: Database Connector
version: 1.16
is-latest-version: true
---

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

> For the full documentation index, see: https://docs.mulesoft.com/llms.txt

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](/mulesoft/current/en-us/db-connector/latest/_images/database-oracle-udt-flow.png)

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.

<table><colgroup><col> <col></colgroup><thead><tr><th>Deprecated Function</th><th>Active Function</th></tr></thead><tbody><tr><td><p><code>Db::createArray(configName, typeName, values): java.sql.Array</code></p></td><td><p><code>Db::prepareArray(typeName, values): java.util.List</code></p></td></tr><tr><td><p><code>Db::createStruct(configName, typeName, properties): java.sql.Struct</code></p></td><td><p><code>Db::prepareStruct(typeName, properties): java.util.List</code></p></td></tr></tbody></table>

### Parameters

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

<table><colgroup><col> <col> <col></colgroup><thead><tr><th>Parameter</th><th>Type</th><th>Description</th></tr></thead><tbody><tr><td><p><code>typeName</code></p></td><td><p>String</p></td><td><p>Name of the Array type to create.</p></td></tr><tr><td><p><code>values</code></p></td><td><p>List&lt;Object&gt;</p></td><td><p>Values to pass to the database.</p></td></tr><tr><td><p><code>java.util.List</code></p></td><td><p>List&lt;Object&gt;</p></td><td><p>The return value is an immutable list that holds all the information for creating an array later.</p></td></tr></tbody></table>

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

<table><colgroup><col> <col> <col></colgroup><thead><tr><th>Parameter</th><th>Type</th><th>Description</th></tr></thead><tbody><tr><td><p><code>typeName</code></p></td><td><p>String</p></td><td><p>Name of the Struct type to create.</p></td></tr><tr><td><p><code>properties</code></p></td><td><p>List&lt;Object&gt;</p></td><td><p>Properties to pass to the database.</p></td></tr><tr><td><p><code>java.util.List</code></p></td><td><p>List&lt;Object&gt;</p></td><td><p>The return value is an immutable list that holds all the information for creating a struct later.</p></td></tr></tbody></table>

> [!NOTE] 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:

```xml
--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`

6.  Click **OK**.

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

8.  In the **Filter** field, type `database`, select **Database Config**, and click **OK**.

9.  In the **Database Config** window, set the **Name** field to `dbConfig`.

10. In the **Connection** field, select **Oracle Connection**.

11. 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.

12. In the **Connection** section, set the following parameters:

    - **Host**: `localhost`

    - **Port**: `1521`

    - **User**: `SYS as SYSDBA`

    - **Password**: `Oradoc_db1`

    - **Instance**: `ORCLCDB`

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

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

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

    <table><colgroup><col> <col></colgroup><thead><tr><th>Id</th><th>Type name</th></tr></thead><tbody><tr><td><div><div><p>2003</p></div></div></td><td><div><div><p><code>PEOPLE</code></p></div></div></td></tr><tr><td><div><div><p>2003</p></div></div></td><td><div><div><p><code>PHONE_NUMBER</code></p></div></div></td></tr><tr><td><div><div><p>2008</p></div></div></td><td><div><div><p><code>PERSON</code></p></div></div></td></tr><tr><td><div><div><p>2003</p></div></div></td><td><div><div><p><code>PHONE_ARRAY</code></p></div></div></td></tr><tr><td><div><div><p>2003</p></div></div></td><td><div><div><p><code>PHONE_BOOK</code></p></div></div></td></tr></tbody></table>

16. Click **Finish** to close the **Column type** window.

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

18. Click **OK**.

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

![The HTTP Listener global element configuration](/mulesoft/current/en-us/db-connector/latest/_images/database-oracleudt-example-1.png)

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.](/mulesoft/current/en-us/db-connector/latest/_images/database-oracleudt-example-2.png)

Figure 3. Database Config General configuration

![The database global element advanced configuration setting the Column types field](/mulesoft/current/en-us/db-connector/latest/_images/database-oracleudt-example-3.png)

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:

    ```xml
    [{'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](/mulesoft/current/en-us/db-connector/latest/_images/database-oracleudt-example-4.png)

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:

    ```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 configuration](/mulesoft/current/en-us/db-connector/latest/_images/database-oracleudt-example-6.png)

    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](/mulesoft/current/en-us/db-connector/latest/_images/database-oracleudt-example-5.png)

Figure 7. Stored procedure configuration

6.  Drag a second **Transform Message** component to the right of **Stored procedure**.

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

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

```DataWeave
%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
<?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>
```

## See Also

- [Database Connector Examples](https://docs.mulesoft.com/db-connector/latest/database-connector-examples)

- [Salesforce Help](https://help.salesforce.com)