Connecting Mule to an Oracle database (part 1)

As I have a background in Oracle database and middleware development and just started to getting to know Mulesoft, I was wondering how I could create a service to get data from an Oracle database using Mule.

I started with the basics: selecting data from a database table.

In this part I will setup the environment and select the data. In part 2 I will discuss calling an Oracle stored procedure with database object types in the response.

Let’s get started.

Prerequisites

First of all you need to have access to an Oracle database. I use a locally installed Oracle 11g Express Edition (free). This comes with SQL*Plus, the command line tool to interact with the database. But you can of course use some of the available IDE’s, such as SQL Developer (free, from Oracle, runs on Windows, Mac and Linux), PL/SQL Developer (commercial, from Allround Automations, runs on Windows) or Navicat for Oracle (commercial, from Navicat, runs on Windows, Mac and Linux).

And you need to have Mulesoft Anypoint Studio and Postman (for testing) installed.

 

Setup

After installing software, we first will install the necessary database objects.

If using the Oracle XE database, make sure it is running.

Start SQL*plus and connect as a DBA user (w.g. the SYSTEM user on XE).

sqlplus

Run the following statements to create a database schema user:

CREATE USER DEMO IDENTIFIED BY DEMO;
GRANT CONNECT, RESOURCE TO DEMO;
CONNECT DEMO/DEMO@XE

Download the database scripts from GitHub:

Run the following scripts for creating the necessary database objects:

@create_example_tables.sql
@create_example_sequences.sql
@create_example_triggers.sql

Run the following scripts for loading the example data into the tables:

@author_data.sql
@book_data.sql

The database setup is finished.

 

Mule

Next, start Mule Anypoint Studio.

Create a New Mule Project using the menu: File > New > Mule Project.

Create New Mule Project

Name the project: demo-library

Leave all settings to the default and press Finish.

The project is now shown in the Package Explorer.

Anypoint Studio - Package Explorer

 

Global configuration

First we will create a new Mule Configuration File for holding the global elements.

Right Click on src/main/app en select New > Mule Configuration File

New Mule Configuration File

Name the file global and press Finish.

The global configuration file is opened.

Select the Global Elements tab.

Global elements tab

Press the Create button.

In the Filter box type: Oracle

Filter Global Elements

Select the Oracle Configuration Connector and press OK.

Enter Oracle_Configuration_Demo as Name

Enter the Database configuration parameters:

Host: localhost

Port: 1521

Instance: XE

User: demo

Password: demo (the password of the demo user)

demo-oracle-configuration-1

As you can see a driver is to be added.

Press the Add File button.

And navigate to the folder in which the ojdbc6.jar file is located.

Select the file and press Open.

If everything went fine the driver is being copied to the project and a green mark appears.

demo-global-elements-tab-oracle-connector-jarfile-ok

Press Test Connection to check the connection.

A Test connection successful should appear.

Press twice on OK.

 

Next we will add a HTTP Listener configuration.

Again press the Create button.

Enter HTTP in the filter box.

HTTP Listener

Select the HTTP Listener Configuration connector.

And press OK.

Accept the default settings and press OK.

Save the file.

 

The flow

HTTP listener

Open the demo-library flow xml.

Empty Mule flow

Drag in a HTTP listener from the Mule Palette.

Type http in the filter box and drag the HTTP endpoint into the empty workspace.

HTTP connector

After dropping the HTTP endpoint on the canvas the flow is shown.

demo-demo-library-flow-http

Double click on the HTTP endpoint in the flow.

The HTTP tab is shown.

HTTP Connector config

Change the Path to: /authors

And Allowed Methods to: GET

Click in the canvas and save the file.

 

Database connector

Now we will add a Database connector to the Process.

Enter oracle in the Mule Palette.

demo-palette-database-connector

And drag the Database connect into the flow.

demo-demo-library-flow-database

Double click on the Database connector to show the Database (if not already being shown).

demo-database-tab

Rename the Display Name to: Retrieve list of authors

And select as Operation: Select

Set Query Type to Parameterized (default).

And enter the following query:

select lastname
      ,initials
      ,firstname
      ,lastname_prefix
      ,date_of_birth
      ,nationality
from   authors
order  by lastname

Save the file.

 

Transformation

Next, add a Transform Message transformer component into the process.

Enter: transform in the Mule Palette filter.

demo-palette-transform-message

Drag the Transform Message component in the Process, just behind the Database connector.

demo-demo-library-flow-transform-message

And double click on the Transform Message component.

As you will notice the columns of the selected Oracle database table (as mentioned in the query) are show on the left hand side.

demo-demo-library-flow-transform-message-dataweave-empty

Change in the Output block the DataWeave code to:

%dw 1.0
%output application/json
---
payload

You can preview the transformation by clicking on the Preview button.

You will see the message that you should create sample data.

demo-demo-library-flow-dataweave-create-sample-data

Click on the message.

A sample data file is created and the result is show.

Update the sample data to represent some real values instead of the “????”.

The Preview will be updated along.

Save the file.

 

Now you are ready to test the flow with the real data from the Oracle Database.

Right-Click in the blank canvas and select Run project demo-library.

demo-demo-library-flow-run

The Mule engine is started.

And if everything is well you should see that the application has been deployed.

demo-demo-library-flow-console-deployed

Start Postman.

Select the Get method and enter the endpoint: http://localhost:8081/authors

Postman

And press Send.

You should see 4 authors in the Body and a HTTP status of 200.

Postman response

We can change the output format to get rid of the uppercase column names.

Go back to Anypoint studio and change the DataWeave transformation to:

%dw 1.0
%output application/json 
---
authors: payload map {
	author: {
		name: $.LASTNAME,
		initials: $.INITIALS,
		nationality: $.NATIONALITY
	}
}

Save the file.

If the application is still running it will be redeployed, otherwise run the project again.

When the application is successfully deployed go back to Postman and send another request.

Postman response 2

Now lets say we want to show the output as XML.

Go back to Anypoint Studio and change the DataWeave transformation.

Change %output application/json into %output application/xml

But this not work!

Transform warning

Click on the warning.

Error

We can solve this by changing the transformation to:

%dw 1.0
%output application/xml 
---
authors: {(payload map {
	author: {
		name: $.LASTNAME,
		initials: $.INITIALS,
		nationality: $.NATIONALITY
	}
})}

Save the file and, after successful deployment, send a new request using Postman.

And now the output is XML.

Postman response xml

In the next part we will use a stored procedure call instead of directly selecting from the database.

Connecting Mule to an Oracle database (part 1)
Tagged on:     

Leave a Reply

Your email address will not be published. Required fields are marked *