January 16 2017

A tenet of any integration toolset is the ability to interact with databases. With standards in place, this for the most part is fairly easy. But sometimes, the particulars of a database can create some unexpected complexity. One example of this is an integration solution developed within Mulesoft that invokes Oracle PL/SQL stored procedures and functions. In this scenario, knowing about some of the complexity beforehand will ensure less frustration by a developer during the development phase.

In this blog post, I will share insights needed to circumvent some of the gotcha’s that will help you succeed integrating PL/SQL with Mulesoft.

How to call a stored procedure or function

Starting from the basics, calling a PL/SQL stored procedure or function starts with a database connector within a flow. Once the connection details are specified, the following is the syntax for invoking the stored proc:

        {call procedureName(:parameter1, :parameter2, ….)}

For a function, use the following syntax:

      { :returnValue = call functionName(:parameter1, :parameter2,…)}

One aspect that may be overlooked, but will definitely cause grief during unit testing is permissions. Ensure the user account specified in the database connection has the correct database grant to execute the procedure or function.

Parameters and JDBC

Data Types

Mulesoft utilizes JDBC capabilities for the connectivity to relational databases. Given this, there are considerations that must be taken into account when connecting to Oracle PL/SQL. Input and output parameters for a given function or procedure need to be specified, which also includes the parameter type definition. Understanding the conversion from JDBC data type to PL/SQL type will ensure that these parameters are understood and interpreted correctly by PL/SQL. Further details can be found here

Booleans

One caveat for the data type translations is with boolean values. Basically, there is no translation within JDBC for Oracle PL/SQL boolean parameters. This will definitely be an issue for many Oracle stored procedures. For example, within the Oracle HRMS APIs, a common parameter passed into the stored procedures is a boolean field called ‘p_validate’. Given the lack of translation between JDBC and PL/SQL, the best work around for this is to create a wrapper for the procedure. The wrapper interface should have an integer defined for the boolean value. Within the wrapper, this integer value will be translated to boolean, then invoke the stored procedure with the other values passed in as the wrapper parameters. The Mule flow will call this wrapper with an integer value of 0 or 1 for corresponding the boolean value.

Optional Parameters

Many stored procedures and functions have parameters that are optional fields. For example, the HRMS APIs (i.e. hr_organization_api.update_organization) have many input fields, but only several of which are required. When invoked by a Mulesoft flow, ALL of the parameters, optional or not, need to be specified. For those optional fields that you do not want the existing value altered, pass in the default value for the parameter. The stored procedure header will contain the defaults for reference. The following is a snippet of an HRMS stored procedure with default values for the optional fields:

PROCEDURE update_organization
     (p_validate         IN  BOOLEAN   DEFAULT false
     ,p_effective_date   IN  DATE
     ,p_language_code    IN  VARCHAR2  DEFAULT hr_api.userenv_lang
     ,p_name             IN  VARCHAR2  DEFAULT hr_api.g_varchar2
     ,p_organization_id  IN  NUMBER
     ,p_cost_allocation_keyflex_id     IN  NUMBER    DEFAULT hr_api.g_number
     ,p_location_id      IN  NUMBER    DEFAULT hr_api.g_number
     ,p_date_from        IN  DATE      DEFAULT hr_api.g_date
     ,p_date_to          IN  DATE      DEFAULT hr_api.g_date
     ,p_internal_external_flag         IN  VARCHAR2  DEFAULT hr_api.g_varchar2
… )

Default values can be null, a boolean value, or like in the above example, a database constant. In this scenario, these values cannot be used within the Mulesoft flow, but rather the translation of it. For example:

PL/SQL MuleSoft
hr_api.g_varchar2 $Sys_Def$
hr_api.g_number -987123654
hr_api.g_date 4712-01-01
hr_api.userenv_lang US

The following shows how these values are specified when invoking the stored procedure in Mulesoft:

Invoke storedProc in Mulesoft

User Defined Types

Parameters for an Oracle stored procedure can not only be the data types as mentioned above, but can also be User Defined Types (UDT) such as structure or arrays. For Mulesoft pre-3.9.0 runtime, invoking a stored procedure with UDT parameters can only be accomplished within Java code. This link provides a good example of how this is accomplished. The Java class can then be invoked within the Mulesoft flow.

 

About the Author

Bio

Jennie has over 25 years of information technology experience, the majority of time spent in application integration. She has broad experience in integration architecture design and implementation utilizing several integration toolsets. As an architect, she has experience developing SOA/API Governance Framework and Reference Architectures.

Join the Conversation

Amber Angela
September 11, 2017

Information and yield strictures for a specified capacity or method should be indicated, which additionally incorporates the limitation sort explanation. Inside the covering, at Custom Assignment that point conjure the put away method with alternate esteems go in as the cover constraints.

Ashley Taylor
September 12, 2017

I will be going on a major street trip with my family, so I need to ensure that my auto is prepared. It bodes well that my tires are appropriately adjusted Help Me With My Homework and adjusted!

Enter your first name. It will only be used to display with your comment.
Enter your email. This will be used to validate you as a real user but will NOT be displayed with the comment.
By submitting this form, you accept the Mollom privacy policy.