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

Import Suppliers using FBDI in Oracle Integration