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

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 Mule 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 Mule 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 Mule:

storedProc

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 Mule 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 Mule flow.

 

Join the Conversation

About the Author