Common Errors - Invoking Oracle Stored procedure from MuleSoft 4.0 - DateTime formats and Default values

Introduction : 

In Mule implementation, a common mistake while invoking Oracle's stored procedure is, incorrectly defining the order of IN/OUT parameters. These IN and OUT parameters data types and format should match with Oracle DB stored procedure's definition.

Problem Statement: 

I have recently come across a scenario, where I need to invoke Oracle's DB stored procedure from Mule with IN/OUT parameters. out of which couple of fields are having Date as datatype. During the invocation, there were some errors appeared which is little tricky to understand, however, the root cause was incorrect format of the input Date type parameter.

I am taking a sample procedure and DB table mentioned below to illustrate the error scenarios with Oracle DB Procedure call and their possible solution.

Database table structure :

Oracle DB Procedure : XX_TEST_CREATE_ORDER

CREATE OR REPLACE PROCEDURE xx_test_create_order 
    i_orderid       IN NUMBER,
    i_ordernum      IN VARCHAR,
    i_customer_id   IN VARCHAR,
    i_item          IN VARCHAR,
    i_price         IN NUMBER,
    i_creation_date IN DATE,
    i_shipped_date  IN DATE,
    o_return_code   OUT NUMBER,
    o_return_status OUT VARCHAR
) IS
BEGIN
    INSERT INTO orders (
        orderid,
        ordernum,
        customer_id,
        item,
        price,
        creation_date,
        shipped_date
    ) VALUES (
        i_orderid,
        i_ordernum,
        i_customer_id,
        i_item,
        i_price,
        i_creation_date,
        i_shipped_date
    );
    o_return_code := 0;
    o_return_status := 'SUCCESS';
    COMMIT;
    o_return_code := 0;
    o_return_status := 'SUCCESS';
EXCEPTION
    WHEN OTHERS THEN
        o_return_code := 1;
        o_return_status := 'FAIL';
        raise_application_error(-20001, 'An error was encountered - '
                                        || sqlcode
                                        || ' -ERROR- '
                                        || sqlerrm);
END;

Error 1 :

Error message "Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff]".

Root cause: This error occurs when there is a mismatch of date type value passed is rejected by Oracle DB procedure.

Solution : In this scenario, We need to understand the correct format of the timestamp which is accepted by the database columns. Accordingly the values needs to be prepared/formatted in the transform message activity. For this example, I have two columns (Creation_Date, and Shipped_Date) with the datatype as date and below is the transform message code snippet to address the problem.

Note : Please make sure the date format in mule transformation that you are using is exactly matching the format defined in the database table.

%dw 2.0
output application/java
---
{
	i_orderid : payload.orderId,
	i_ordernum : payload.ordernum,
	i_customer_id : payload.customer_id,
	i_item : payload.item,
	i_shipped_date : payload.creation_date as String{format: "YYYY-MM-DD HH24:MI:SS"}++" 00:00:00",
	i_creation_date: payload.creation_date as String{format: "YYYY-MM-DD HH24:MI:SS"}++" 00:00:00",
	i_price: "124"
}

Error 2: The error message says "Could not resolve query: CALL XX_TEST_CREATE_ORDER(:i_orderid, :i_ordernum,:i_customer_id ,:i_item ,:i_price,:i_creation_date,:i_shipped_date,:o_return_code,:o_return_status)".

Root cause : In the transform message activity, Even if the attribute is missing from the request payload, the attribute should be default as null or "" (empty) in the transform message. Otherwise, the flow may end up in an error.

Solution : The mappings must be present for all the input object variables. If value of the attribute is empty or it does not exists in the request payload, then either null or default "" shoule be passed. Below is the code snippet for request payload and transform message.

Request Payload:
{
	"orderId":"12423",
	"ordernum":"PO1283",
    "item": "", -- Optional field
  	"customer_id": "Bayers",
  	"creation_date":"2021-07-19"
  }

Transform Message : 
  %dw 2.0
  output application/java
  ---
  {
  	i_orderid : payload.orderId,
  	i_ordernum : payload.ordernum,
  	i_customer_id : payload.customer_id,
  	i_item : payload.item default "", --Even if item is not present, it should have default value as ""
  	i_shipped_date : payload.creation_date as String{format: "YYYY-MM-DD HH24:MI:SS"}++" 00:00:00",
  	i_creation_date: payload.creation_date as String{format: "YYYY-MM-DD HH24:MI:SS"}++" 00:00:00",
  	i_price: "124"
  }

Note : In some scenarios, if the DB column has enabled not null check, then transformation logic has to be handled as needed.

Mule Flow:

Complete Mule flow XML:

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


<mule xmlns:ee="http://www.mulesoft.org/schema/mule/ee/core"
	xmlns:db="http://www.mulesoft.org/schema/mule/db"
	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:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://www.mulesoft.org/schema/mule/core http://www.mulesoft.org/schema/mule/core/current/mule.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/db http://www.mulesoft.org/schema/mule/db/current/mule-db.xsd
http://www.mulesoft.org/schema/mule/ee/core http://www.mulesoft.org/schema/mule/ee/core/current/mule-ee.xsd">
	<http:listener-config name="HTTP_Listener_config"
		doc:name="HTTP Listener config"
		doc:id="0058fa34-6e5b-4ab3-9e3c-7ea88bcc9ab4">
		<http:listener-connection host="0.0.0.0"
			port="8081" />
	</http:listener-config>
	<db:config name="OracleXE_Database_Config" doc:name="Database Config" doc:id="0dfc1806-8ded-4a4a-80b7-6180b99c69d8" >
		<db:oracle-connection host="localhost" user="hr" password="hr" instance="xe" />
	</db:config>
	<flow name="oracle_storedprocedureFlow" doc:id="14e90688-9069-401c-a207-54593e7a2c84" >
		<http:listener doc:name="Listener" doc:id="7f727dc7-c40a-4f6c-9851-747ee3352dcd" config-ref="HTTP_Listener_config" path="/createOrder" outputMimeType="application/json" allowedMethods="POST"/>
		<logger level="INFO" doc:name="Logger" doc:id="6f10f271-e247-4e2f-94aa-b31f7171c167" message="#[payload]"/>
		<ee:transform doc:name="Transform Message" doc:id="a4bd3b23-2ac6-4f84-973b-cf2123a4e277" >
			<ee:message >
				<ee:set-payload ><![CDATA[%dw 2.0
output application/json
---
{
i_orderid : payload.orderId,
i_ordernum : payload.ordernum,
i_customer_id : payload.customer_id,
i_item : payload.item default "",
i_shipped_date : payload.creation_date as String{format: "yyyy-MM-dd hh:mm:ss"}++" 00:00:00",
i_creation_date: payload.creation_date as String{format: "YYYY-MM-DD HH24:MI:SS"}++" 00:00:00",
i_price: "124"
}


]]></ee:set-payload>
			</ee:message>
		</ee:transform>
		<logger level="INFO" doc:name="Logger" doc:id="a01b897a-6867-4fdc-9330-f832a12359a9" message="#[payload]"/>
		<db:stored-procedure doc:name="Stored procedure" doc:id="2e1cc53f-7eb0-40a9-8f8b-9d6107f58775" config-ref="OracleXE_Database_Config">
			<db:sql ><![CDATA[CALL XX_TEST_CREATE_ORDER(:i_orderid, :i_ordernum,:i_customer_id ,:i_item ,:i_price,:i_creation_date,:i_shipped_date,:o_return_code,:o_return_status)]]></db:sql>
			<db:input-parameters ><![CDATA[#[payload]]]></db:input-parameters>
			<db:output-parameters >
				<db:output-parameter key="o_return_code" type="INTEGER" />
				<db:output-parameter key="o_return_status" type="VARCHAR" />
			</db:output-parameters>
		</db:stored-procedure>
		<logger level="INFO" doc:name="Logger" doc:id="eef5801e-aea2-4ac3-91db-26bb20c5d9b6" message="#[payload]"/>
	</flow>
</mule>

References : https://docs.mulesoft.com/db-connector/1.10/database-stored-procedure

Happy Learning...!

Please share your valuable feedback 😊