How To Call DB Procedure Asynchronously From OIC
In this article, I’ll demonstrate how to call DB procedure asynchronously from OIC and the steps involved in the process.
Prerequisites
- Oracle Integration Cloud Instance
- ATP
DB Call Time limit
DB call from OIC has 240 seconds time limit. Below is the details from oracle documentation, for more details click here

Example: I have created a schedule integration which calls a DB Procedure (INSERT_DATA) and Procedure takes 5 minutes to complete. In this case DB call in OIC integration errors as show below. Even if the Procedure does not have OUT parameters, Procedure will be executed in synchronous mode and it waits for the completion of procedure execution.

Solution
If we can call the DB Procedure asynchronously, we can over come this limitation. First we need to create a wrapper procedure which calls the main procedure using DBMS Scheduler option inside the wrapper procedure. I have implemented the same and below is the result.

Below is the OIC flow, package and package code I have used. I have called the RUN_JOB procedure (wrapper procedure) which calls the INSERT_DATA (main procedure) internally. In OIC mapper i have passed the input parameters (OIC instance Id from integration metadata mapper and hardcoded the main procedure name ‘INSERT_DATA’ ) to RUN_JOB procedure.


CREATE OR REPLACE PACKAGE DB_SCH
AS
PROCEDURE RUN_JOB (
p_oic_instance_id IN VARCHAR2,
p_proc_name IN VARCHAR2
);
PROCEDURE INSERT_DATA (
p_oic_instance_id IN VARCHAR2
);
END DB_SCH ;
create or replace PACKAGE BODY DB_SCH AS
PROCEDURE RUN_JOB (
p_oic_instance_id IN VARCHAR2,
p_proc_name IN VARCHAR2
) IS
l_job_action VARCHAR2(1000);
BEGIN
l_job_action := 'DECLARE
P_OIC_INSTANCE_ID VARCHAR2(200);
BEGIN
P_OIC_INSTANCE_ID :='|| p_oic_instance_id ||';
DB_SCH.'||p_proc_name||'(
P_OIC_INSTANCE_ID => P_OIC_INSTANCE_ID
);
END;' ;
dbms_scheduler.create_job(job_name => 'test_job', job_type => 'PLSQL_BLOCK', job_action => l_job_action, start_date => systimestamp
, auto_drop => true,enabled => true);
END;
PROCEDURE INSERT_DATA (
p_oic_instance_id IN VARCHAR2
) IS
IN_TIME number ; --num seconds
v_now DATE ;
BEGIN
SELECT SYSDATE
INTO v_now
FROM DUAL;
-- 2) Loop until the original timestamp plus the amount of seconds <= current date
LOOP
EXIT WHEN v_now + (60 * (1/86400)) <= SYSDATE;
END LOOP;
INSERT INTO OIC_TEMP VALUES (p_oic_instance_id);
END;
END DB_SCH;
Note : I have intentionally kept wait in code to test the use case.
Further Readings
How to create a BIP report in Oracle SaaS
How to create and test custom ESS job in Oracle SaaS
Merging two CSV files into a single file in Oracle Integration
ERP Integration using File Based Data Import: Oracle Integration
Your article helped me a lot. what do you think? I want to share your article to my website: gate.io
yes sure…you can share buddy!
Enjoyed examining this, very good stuff, appreciate it.
I appreciate you sharing this article.Really looking forward to read more. Cool.
Thank you Pawan, it is very useful scenario.
Im grateful for the article.Really thank you! Cool.
I truly appreciate this blog post.Really thank you! Much obliged.
Your article helped me a lot, is there any more related content? Thanks!