How to call Stored Procedure with return value from dblookup/dbreport in WSO2 ESB

I have tried many times to call stored procedure with return value from WSO2 ESB but unfortunately didn’t get success, later on I realized that there is no such functionality to call SP with return parameter from dblookup/dbreport (Based on my experience).

The reason could be like to give more support WSO2 DSS.

However you can call stored procedure which does not returns any value. I have written an example for this may help you, click here to get details.

Other option to create a custom mediator and call stored procedure which returns value, click here to see how custom mediator works.

And the best option is to use DSS and in DSS you can run any complex stored procedure and it’s very easy to use, click here to see how to Parameterized Stored procedure is called from WSO2 DSS.

Syntax:

<dblookup xmlns="http://ws.apache.org/ns/synapse">

<connection>

<pool>

<driver>oracle.jdbc.OracleDriver</driver>

<property name="autocommit" value="false"/>

<user>SYSTEM</user>

<password>SYSTEM</password>

<url>jdbc:oracle:thin:SYSTEM/SYSTEM@localhost:1521/xe</url>

</pool>

</connection>

<statement>

<sql>call UPDATE_EMP_DETAILS (?,?)</sql>

<parameter expression="synapse:get-property('EmpId')" type="VARCHAR"/>

<parameter expression="synapse:get-property('EmpAddress')" type="VARCHAR"/>

</statement>

</dblookup>

If you have any example or solution to call return value Stored Procedure from dblookup, please share with us, your response will be highly appreciated.