Passing Java Arrays in Oracle Stored Procedure From Mule ESB Flow

Objective

The objective of this tutorial is to demonstrate the workaround needed to invoke an Oracle Stored Procedure from Mule ESB flow by passing Java arrays as parameters.

The use case for this tutorial is a simple one such as inserting student records from a CSV file into an Oracle database table through a stored procedure, where one of the parameters is an array holding the student’s marks.

Pre-requisites

• Oracle XE 11.2g: http://www.oracle.com/technetwork/database/database-technologies/express-edition/downloads/index.html
• Java SE 8: http://www.oracle.com/technetwork/java/javase/downloads/jdk8-downloads-2133151.html
• Mule Anypoint Studio 5.4 with 3.7.3 Runtime: https://www.mulesoft.com/lp/dl/studio
• Mule ESB 3.7.3 (Optional): https://developer.mulesoft.com/download-mule-esb-runtime
• Mule Application Properties (mule-app.properties): Use the following properties or define them as per your system.

db.host=localhost
db.port=1521
db.instance=XE
db.user=scott
db.password=tiger

Backend Concepts

Let’s see a few concepts required from an Oracle PL/SQL perspective to fulfill this use case.

• To create an array in PL/SQL, first you have to create a user defined type and then use the type to create a field or parameter. For our use case, we are supposed to create a type as TABLE OF NUMBER.

CREATE OR REPLACE TYPE NUMBER_T IS TABLE OF NUMBER;

• Create the database table named STUDENTS, create the primary key constraint, create an index, and create the sequence for automatically creating the next sequence for STUDENT_ID.

CREATE TABLE STUDENTS (STUDENT_ID INTEGER NOT NULL, NAME VARCHAR2(100), ENG NUMBER, PHY NUMBER, CHEM NUMBER, MATH NUMBER);
CREATE UNIQUE INDEX STUDENTS_IDX1 ON STUDENTS(STUDENT_ID);
ALTER TABLE STUDENTS ADD CONSTRAINT STUDENTS_PK PRIMARY KEY (STUDENT_ID) USING INDEX;
CREATE SEQUENCE STUDENTS_SEQ START WITH 1 INCREMENT BY 1 CACHE 10000;

• Create the Stored Procedure which should be defined with two parameters. The first parameter is for the name of the student and the second parameter is for holding the marks and therefore, should be of type NUMBER_T.

CREATE OR REPLACE PROCEDURE INSERT_STUDENTS (I_NAME VARCHAR2, I_MARKS NUMBER_T)
IS
BEGIN
  INSERT INTO STUDENTS (STUDENT_ID, NAME, ENG, PHY, CHEM,
  MATH) VALUES STUDENTS_SEQ.NEXTVAL, I_NAME, I_MARKS (1),  
  I_MARKS (2), I_MARKS (3), I_MARKS (4));
END INSERT_STUDENTS;
/

Note: The parameter I_MARKS defined above is considered as an array of numbers in Oracle PL/SQL and NUMBER_T is known as a user defined type. The parameters I_NAME and I_MARKS are considered as IN parameters, means input parameters.

Mule Application

The Mule application flow begins with a File inbound endpoint that reads a ‘student.csv’ file from ‘src/main/resources/input’ folder and moves the file to ‘src/main/resources/output’ folder after reading is completed. The read contents are then transformed into a collection of Java objects of type ‘student.Student’ through the Transform Message component. Once the transformation is successfully completed, the transformed payload is iterated using For Each scope wrapped with a Database Connector in order to invoke the Oracle stored procedure with two parameters, where the first parameter is the student’s name and the second parameter is the student’s marks as a Java array.

The following figure depicts the project structure for this application.

Resources

This application uses one CSV file name ‘student.csv’ as a resource containing the student’s records which are then processed and stored in the underlining Oracle database table SCOTT.STUDENTS with the help of a stored procedure invocation.  The below figure depicts the structure of the CSV file:

The CSV file is placed in src/main/resources/input folder.

Global Configurable Elements

This application requires the following global configurable elements:

• Oracle Configuration

<db:oracle-config name="Oracle_Configuration"  host="${db.host}" port="${db.port}" instance="${db.instance}" user="${db.user}" password="${db.password}" doc:name="Oracle Configuration">
</db:oracle-config>

• Global Function Configuration

The main purpose of this global function is to create an oracle.sql.ARRAY object with the given type name NUMBER_T and the elements of the Java array (Student Marks) so that the marks parameter of the invoking stored procedure can be mapped with the ARRAY type, as depicted in the below screenshot.

• Oracle Configuration with Data Source

<db:oracle-config name="Oracle_Configuration" doc:name="Oracle Configuration" dataSource-ref="studentDataSource">
</db:oracle-config>

<spring:beans>
<spring:bean id="studentDataSource" name="studentDataSource" class="oracle.jdbc.pool.OracleDataSource">
            <spring:property name="password" value="tiger"/>
            <spring:property name="URL" value="jdbc:oracle:thin:@localhost:1521:XE"/>
            <spring:property name="user" value="scott"/>
            <spring:property name="dataSourceName" value="ds"/>
    </spring:bean>
</spring:beans>

• Global Function Configuration   

<configuration doc:name="Configuration">
<expression-language autoResolveVariables="false">
<global-functions>

def castArray(marks,reg) {

javax.sql.DataSource ds = (javax.sql.DataSource)reg.get("studentDataSource");
oracle.jdbc.OracleConnection con = (oracle.jdbc.OracleConnection)ds.getConnection();
oracle.sql.ARRAY lMarks = con.createARRAY("SCOTT.NUMBER_T", marks);
con.close();
return lMarks;

}
</global-functions>
</expression-language>
</configuration>

Flow Elements Configuration

• File Inbound Endpoint

The following properties pertaining to the General Tab should be set:

• Transform Message Component

This component is used to transform the CSV file contents into a collection of Java objects of type student.Student class. Each record of the CSV file is transformed into a Java object of type student.Student class. The below figure depicts the transformation:

Set the metadata for Input and Output sections of the Transform Message component. The metadata for Input section is derived from the CSV file. The metadata for Output section is created with a Java Collection of student.Student class.

The structure of student.Student.java class is defined below:

package student;
public class Student implements java.io.Serializable {

    private static final long serialVersionUID = 7940976767680103084L;
      private String name;
      private float[] marks;

    public Student() {
            super();
      }

      public String getName() {
            return name;
      }

      public void setName(String name) {
            this.name = name;
      }

      public float[] getMarks() {
            return marks;
      }

      public void setMarks(float[] marks) {
            this.marks = marks;
      }
}

• For Each Scope

The For Each scope is used for wrapping a Database Connector so that the stored procedure can be invoked for each student data and subsequently stored in the underlying Oracle database. The following figure depicts the properties to be defined for For Each scope:

The following table shows the properties to be set for ‘For Each’ scope:

• Database Connector

The properties depicted in the figure below should be set for the Database Connector:

The table below shows the properties to be set for the Database Connector:

The point to be noted here is regarding the parameter section of the Database Connector, as depicted above where the value for marks parameter is defined as the global function call castArray which takes the Java array marks defined in student.Student class and app.registry as the parameters and creates oracle.sql.ARRAY object.

Conclusion

To conclude this tutorial, let us execute this application and check the result.

• The below figure shows that the STUDENTS table is empty.

• Run the application with the following records in the student.csv file using the Global Function ‘castArray’ written with Mule internals.

• The below screen shot justifies that the above CSV records were inserted into the database table Students:

• Run the application with the following records in the student.csv file using the Global Function ‘castArray’ written without Mule internals.

• The below screen shot justifies that the above CSV records were inserted into the database table Students.

• Complete XML Code for this tutorial:

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

<mule xmlns:mulexml="http://www.mulesoft.org/schema/mule/xml" xmlns:tracking="http://www.mulesoft.org/schema/mule/ee/tracking"
xmlns:scripting="http://www.mulesoft.org/schema/mule/scripting"
xmlns:db="http://www.mulesoft.org/schema/mule/db" xmlns:file="http://www.mulesoft.org/schema/mule/file"
xmlns:json="http://www.mulesoft.org/schema/mule/json" xmlns:dw="http://www.mulesoft.org/schema/mule/ee/dw"
xmlns:metadata="http://www.mulesoft.org/schema/mule/metadata"
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:spring="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-current.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/ee/dw http://www.mulesoft.org/schema/mule/ee/dw/current/dw.xsd
http://www.mulesoft.org/schema/mule/core http://www.mulesoft.org/schema/mule/core/current/mule.xsd
http://www.mulesoft.org/schema/mule/json http://www.mulesoft.org/schema/mule/json/current/mule-json.xsd
http://www.mulesoft.org/schema/mule/file http://www.mulesoft.org/schema/mule/file/current/mule-file.xsd
http://www.mulesoft.org/schema/mule/db http://www.mulesoft.org/schema/mule/db/current/mule-db.xsd
http://www.mulesoft.org/schema/mule/scripting http://www.mulesoft.org/schema/mule/scripting/current/mule-scripting.xsd
http://www.mulesoft.org/schema/mule/ee/tracking http://www.mulesoft.org/schema/mule/ee/tracking/current/mule-tracking-ee.xsd
http://www.mulesoft.org/schema/mule/xml http://www.mulesoft.org/schema/mule/xml/current/mule-xml.xsd">
<db:oracle-config name="Oracle_Configuration" 
    doc:name="Oracle Configuration" dataSource-ref="studentDataSource">
</db:oracle-config>
<configuration doc:name="Configuration">
<expression-language autoResolveVariables="false">
<global-functions>

def castArray(marks,reg) {

javax.sql.DataSource ds = (javax.sql.DataSource)reg.get("studentDataSource");
oracle.jdbc.OracleConnection con = (oracle.jdbc.OracleConnection)ds.getConnection();
oracle.sql.ARRAY lMarks = con.createARRAY("SCOTT.NUMBER_T", marks);
con.close();
return lMarks;

}
</global-functions>
</expression-language>
</configuration>
    <spring:beans>
        <spring:bean id="studentDataSource" name="studentDataSource" class="oracle.jdbc.pool.OracleDataSource">
            <spring:property name="password" value="tiger"/>
            <spring:property name="URL" value="jdbc:oracle:thin:@localhost:1521:XE"/>
            <spring:property name="user" value="scott"/>
            <spring:property name="dataSourceName" value="ds"/>
        </spring:bean>
    </spring:beans>

<flow name="PassingJavaArraysInOracleStoredProcedureFlow">
        <file:inbound-endpoint path="C:\Users\pbgho\AnypointStudio\workspace\Passing_Java_Arrays_in_Oracle_Stored_Procedure_from_Mule_ESB_Flow\src\main\resources\input" moveToPattern="student.csv" moveToDirectory="C:\Users\pbgho\AnypointStudio\workspace\Passing_Java_Arrays_in_Oracle_Stored_Procedure_from_Mule_ESB_Flow\src\main\resources\output" responseTimeout="10000" metadata:id="46b599ed-e0e6-46cc-93c9-0564bb8aff2f" doc:name="Students CSV File"/>
        <dw:transform-message metadata:id="eee60e7a-a87d-483b-bd84-1568e4ccda39" doc:name="Transform Message">
            <dw:set-payload><![CDATA[%dw 1.0
%output application/java
---
payload map ((payload01 , indexOfPayload01) -> {
name: payload01.Name,
marks: [payload01.Eng, payload01.Phy, payload01.Chem, payload01.Math]
}  as :object {class: "student.Student"})]]></dw:set-payload>
        </dw:transform-message>
        <foreach doc:name="For Each" collection="#[payload]">
            <db:stored-procedure config-ref="Oracle_Configuration" doc:name="Oracle Database 11.2g">
                <db:parameterized-query><![CDATA[{call scott.insert_students(:sname, :marks)}]]></db:parameterized-query>
                <db:in-param name="sname" type="VARCHAR" value="#[payload.getName()]"/>
                <db:in-param name="marks" type="ARRAY" value="#[castArray(payload.getMarks(),app.registry)]"/>
            </db:stored-procedure>
        </foreach>
        <logger message="Completed" level="INFO" doc:name="Logger"/>

</flow>


</mule>

I hope this tutorial will help those who were posting issues regarding stored procedure invocation issues in forums. You should also try for INOUT and OUT parameter types.

Author: Priyobroto Ghosh

Email: pbghosh@gmail.com