I have created a stored procedure which needs to be called from the mule soft. Stored procedure has been created in Oracle and it inserts the details in an employee table. If insertion is success or failed then it gives a result code and results description as a part of output.
Once we are ready with Stored procedure, can go ahead creating a project in Anypoint Studio.
Create a Mule project with the name of “CallStoredProcedure”.
Drag connection in canvas as mention below starting with Http, variable, database and then a logger to log values.
Now click on “Http” connection and configure the values.
Clcik on “+” icon in “Connector Configuration” and fill the below details (automatically filled) and click “OK”.
Now click on Variable and provide a static value to it, in my case I have given “111” value to Emp_ID variable.
Now click on the Database connector to connect the Oracle Database and then we can call our stored procedure from there.
Fill the necessary detail to connect the Oracle Database. You need to add oracle connector driver to make it work otherwise it will give error. I have added a JDBC connector from my local machine.
Now click on “Test Connection” to test our connectivity. If everything goes successful then it will give you success message else failed message. Click on OK button to proceed further.
Now connectivity is fine and now time to call stored procedure. To do this you need to choose “Stored Procedure” in Operation drop down and write below query in “Parameterized Query” window.
Now add the parameter by clicking the “+” icon and define “IN/OUT” parameter. You can provide the values into it in “Values” section. I have taken EMP_ID value from the variable, EMP_NAME is a static value as “SHRI” and getting address from URL as dynamic value.
Now click on the logger connector to log the payload values.
We are all set to go, now run the program and hit the “localhost:8081?ADDRESS=Raebareli” URL in your favorite browser.
Just after hitting the URL you can check the records in Database table.
Just look into the logs, you will be able to find proper output there of the stored procedure.
You can make any decision based on the output provided by the stored procedure.
Mule Canvas Code: