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.
- Oracle Integration Cloud Instance
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.
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.