Connecting Mule to an Oracle database (part 2)

This is part 2 of the multi-part article about connecting mule to an Oracle database.

In this part we will call a stored procedure (database package function) which returns a ref cursor containing database object types.

In part 1 you can find the prerequisites and links to the necessary database setup.

Introduction and setup

In part 1 will just selected the data directly from the database.

Besides the fact you are not always allowed from an architectural point view or even security wise to directly access the tables, it is also better to create an integration layer in the Oracle database which will allow for decoupling the backend database from the interface layer.

A common practice to do this when using the Oracle Middleware stack (among others)  is to make use of database packages and object types. But in contrast to selecting directly form the tables, Anypoint Studio does not give you easy support for transforming the outcome from a database call to a service response.

Thus we need to do some more work.

First create the necessary database objects:

@author_typ.typ
@author_tab.typ
@book_typ.typ
@book_tab.typ
@library.pks
@library.pkb

The library package contains 2 similar functions to achieve the same result: retrieving a list of authors with optional providing a filter on the lastname.

The first option will return a sys_refcursor type, which means that is will return a recordset. The records are of an Oracle object type.

The second option will return a collection type, an array of an Oracle object type.

Sys_refcursor return types are supported by Mule, so let’s get started with this option first.

 

Creating the flow

Create a new flow by dragging in a HTTP connector into the canvas.

Click on the flow and rename it to getAuthorListFlow.

demo-database-getauthorlistflow

Double click on the HTTP connector and change the Basic Settings:

Path: /authorlist

Allowed Methods: GET

 

Database connector

Then drag in a Database connector from the Mule Palette into the Process of the flow.

Select the Database connector and change the Display Name to: Get Author List from DB

Select the Connector configuration (if not already set) to: Oracle_Configuration_Demo

Select the operation: Stored Procedure

Query Type: Parameterized

Parameterized query:

{:authors = call library.get_authors(p_filter_name => :filter)}

The query contains 2 parameters: authors as the result being returned by the function call and filter as the parameter to filter the result on last name of a author.

 

Add the parameters:

Parameter Name IN/OUT Value Type
filter IN VARCHAR
authors OUT CURSOR

Leave the Value for both initially empty.

 

Transformation

Now drag a Transform Message from the Mule Palette after the Database connector.

demo-database-getauthorlistflow-transform-start

Now you notice that Anypoint Studio cannot determine what the Input Payload is based on the Stored Procedure call (look at the input on the left).

Change the DataWeave expression to:

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

And just try to execute the flow.

But now you get an error when starting the project!

cvc-complex-type.4: Attribute ‘value’ must appear on element ‘db:in-param’.

It seems that you must provide a default values for IN parameters.

So change the value of the filter parameter to a space character. Note that your database code need to be able to handle this (which has already done for you)!

 

Now you will receive the next error:

No reader method for property: shareBytes was found. (java.lang.RuntimeException).

If you change the DataWeave expression to:

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

you do not get an error anymore, but the result is not what you wanted. The structure of the type is shown.

demo-database-first_result

One way to solve this is first add a Java class to hold the type retrieved from the database.

Add the Java file Author.java to your src/main/java folder. You can find the file in the GitHub repository and drag and drop it in the folder.

 

Define the Input Payload by clicking on the Define link in Transform Message.

In the Select metadata type dialog click the Add button.

Enter as type id: AuthorListType

Select the Wrap in a collection checkbox at the bottom of the dialog.

Select Type: Java

Click on “Click here to select an option” and select Java object.

Type in: Author.

The added Author class will be shown.

demo-database-author-class

Click on Author.

The Author structure is shown in the dialog.

Click on Select.

 

Debugging

Before we go further, we will debug the application so we can see what is returned from the database.

Add a Logger component after the Database connector and set a breakpoint on the Logger.

Run the application in debug mode.

Send a request using Postman to http://localhost:8081/authorlist

Return to Anypoint Studio.

Open the Mule Debugger tab full screen.

demo-database-debugger

At Payload you see a HashMap with the first key being the name of the out parameter specified in de Stored Procedure call: authors.

Within the value, which is of type LinkedList, you see 4 records, representing the 4 author records in the AUTHORS table.

Each entry in the LinkedList is again a HashMap with all the same value for key: the select statement in the cursor of the sored procedure. The value of each entry is a Oracle STRUCT type representing the Oracle Database object type AUTHOR_TYP.

The data itself is not shown, neither are the attributes of the object type, but we can find more zooming into the descriptor.

demo-database-debugger-zooming1

After that find the pickler attribute.

demo-database-debugger-zooming2

Expand the attribute and expand attrTypes.

Now we see that the database object type contains 7 attributes. All of which are of the type OracleTypeCHAR (oracle.sql.CHAR).

demo-database-debugger-zooming3

But that is as far as you can get. You will not find the attribute names or the data itself. At least not readable.

But we know the structure of the type, so we also know to which attribute each of the attrTypes belong.

 

Global MEL function

We will now convert the LinkedList returned by the database call to our own Java class to make it readable and easy to transform.

To do this we need to add a global MEL (Mule Expression Language) function to our global config file.

 

Open the global.xml file.

Go to the Global Elements tab.

Click op Create.

Add a Global Configuration.

Click on OK.

Open the source XML by selecting the Configuration XML tab.

Find the <configuration> element and remove <http:config useTransportForUris="false"/>

Within the <configuration> element past the following:

<expression-language autoResolveVariables="false">
    <global-functions>
        def getData(obj) {
            java.util.LinkedList ll = obj.get('authors');
            int arraySize = ll.size();
            
            oracle.sql.STRUCT rec;
            Object[] attrValues;
            oracle.sql.CHAR name;
            oracle.sql.CHAR lastname;
            oracle.sql.CHAR initials;
            oracle.sql.CHAR firstname;
            oracle.sql.CHAR lastnamePrefix;
            oracle.sql.CHAR dateOfBirth;
            oracle.sql.CHAR nationality;
            
            mylibrary.Author author;
            mylibrary.Author[] authors;
            authors = new mylibrary.Author[arraySize];
                
            
                for (int i=0; i &lt; arraySize; i++) { 
                rec = ll[i].values().iterator().next();
                attrValues = rec.getOracleAttributes();
            
                name = (oracle.sql.CHAR) attrValues[0];
                lastname = (oracle.sql.CHAR) attrValues[1];
                initials = (oracle.sql.CHAR) attrValues[2];
                firstname = (oracle.sql.CHAR) attrValues[3];
                lastnamePrefix = (oracle.sql.CHAR) attrValues[4];
                dateOfBirth = (oracle.sql.CHAR) attrValues[5];
                nationality = (oracle.sql.CHAR) attrValues[6];
            
                author = new mylibrary.Author();
                author.setName(name);
                author.setLastname(lastname);
                author.setInitials(initials);
                author.setFirstname(firstname);
                author.setLastnamePrefix(lastnamePrefix);
                author.setDateOfBirth(dateOfBirth);
                author.setNationality(nationality);

                authors[i] = author;              		
            }
            
            return authors;
            
        }
    </global-functions>
</expression-language>

Save the global.xml file.

The function we just added will convert a provided payload (HashMap) to an Author<List>.

 

Modify transformation

Go back to our getAuthorListFlow, remove the Logger component and add a Set Payload Transformer.

The value for the Set Payload is: #[getData(payload)]

Set the DataWeave expression to:

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

And run the application.

Send in a request using Postman and we see the same values being returned as in Part 1.

Postman response 2

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

Leave a Reply

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