Stored Procedure Call

Let’s see how we can call the Stored Procedure from MuleSoft.

Create a project and add HTTP listener to call the API

Drag and Drop stored procedure connector

Create a DB connection

Add the SQL text for calling the procedure and the input parameter

Now add a transformation

Here metadata for the output will be pre-populated and we can map the data accordingly

Run the application using – http://localhost:8081/insert

And now we can see the data added in Target table –

Procedure we are calling here –

create or replace PROCEDURE add_job_history
( p_emp_id job_history.employee_id%type
, p_start_date job_history.start_date%type
, p_end_date job_history.end_date%type
, p_job_id job_history.job_id%type
, p_department_id job_history.department_id%type
)
IS
BEGIN
INSERT INTO job_history (employee_id, start_date, end_date,
job_id, department_id)
VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);
END add_job_history;

Sample application – http://mulesy.com/wp-content/uploads/2019/01/oracle-db-stored-procedure.zip