OIC/Integration/14 → Call DB Procedure Asynchronously from OIC
--
This is a post on Oracle Integration Cloud highlighting how to call a DB Procedure Asynchronously, in case the procedure takes longer than 4 minutes.
(1) Flow: [a] Trigger → [b] Map to DB Procedure → [c] Call DB Procedure → [d] Map to Reply → [e] Reply.
(2) Flow Description: Here we are creating an App Driven Orchestration flow exposing via REST which can be invoked with employee details such as empId, empName etc. From the flow it will call a DB stored procedure which takes more that 4 minutes (240 seconds) to complete. In OIC DB stored procedure call there is a limitation, which needs stored procedure calls to get completed withing 4 minutes. To replicate this, we have introduced a 5-minute delay in our main Stored Procedure through a loop. So initially it will fail. Then we will introduce another Stored Procedure as wrapper which will use DBMS Scheduler feature to schedule a DBMS job and send the response back to OIC flow instantly. In the back end the DBMS job will do it’s work asynchronously. In this way, the flow will result in success and Asynchronously DBMS job can complete its’ work.
(3) Steps:
*** Pre-Requisite:
Need to have “EMP” table.
CREATE TABLE "<schema>"."EMP"
( "EMP_ID" NUMBER NOT NULL ENABLE,
"EMP_NAME" VARCHAR2(50 BYTE),
"CRT_UPD_DATE_TIME" TIMESTAMP (6) DEFAULT CURRENT_TIMESTAMP
);Need to create the following package and procedure in Oracle DB.
--------------------------------------------------------
-- DDL for Package PKG_EMP_DTLS
--------------------------------------------------------
CREATE OR REPLACE PACKAGE "<schema>"."PKG_EMP_DTLS" AS
PROCEDURE proc_insert_emp (p_emp_id IN emp.emp_id%TYPE,
p_emp_name IN emp.emp_name%TYPE,
status OUT VARCHAR2,
status_desc OUT VARCHAR2);
END PKG_EMP_DTLS;
/
--------------------------------------------------------
-- DDL for Package Body PKG_EMP_DTLS
--------------------------------------------------------
CREATE OR REPLACE PACKAGE BODY "<schema>"."PKG_EMP_DTLS" AS
PROCEDURE proc_insert_emp (p_emp_id IN emp.emp_id%TYPE,
p_emp_name IN emp.emp_name%TYPE,
status OUT VARCHAR2,
status_desc OUT VARCHAR2) AS
BEGIN
DECLARE
v_curr_time TIMESTAMP;
v_error VARCHAR2(100);
BEGIN
SELECT SYSTIMESTAMP INTO v_curr_time FROM DUAL;
DBMS_OUTPUT.PUT_LINE('Start:' || v_curr_time);
LOOP
EXIT WHEN v_curr_time + (300 * (1/86400)) <= SYSTIMESTAMP;
END LOOP;
DBMS_OUTPUT.PUT_LINE('End:' || SYSTIMESTAMP);
INSERT INTO emp (emp_id,emp_name,crt_upd_date_time)
VALUES (p_emp_id,p_emp_name,systimestamp);
status := '0';
v_error := 'No error';
EXCEPTION
WHEN OTHERS THEN
status := '-1';
v_error := SQLERRM;
status_desc := v_error;
END;
END proc_insert_emp;
END PKG_EMP_DTLS;
/Now, go to OIC console → Integrations → Create an App Driven Integration with your project name.
[a] Trigger:
Use the following XSD, “RestDBAsyncProcCallSchema.xsd” :
<?xml version="1.0" encoding="windows-1252" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.rest.db.aync.proc.call.com"
targetNamespace="http://www.rest.db.aync.proc.call.com" elementFormDefault="qualified">
<xsd:element name="empDetailsReq">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="empDetail">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="empId" type="xsd:string" minOccurs="0"/>
<xsd:element name="empName" type="xsd:string" minOccurs="0"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="empDetailsResp">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="result" type="xsd:string" minOccurs="0"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
[c] Call DB Procedure:
Flow looks like:
[b] Map to DB Procedure:
[d] Map to Reply:
Test using below payload:
<empDetailsReq xmlns="http://www.rest.db.aync.proc.call.com">
<empDetail>
<empId>106</empId>
<empName>Navin Shetty</empName>
</empDetail>
</empDetailsReq>
Resulted in error as the main procedure takes 5 minutes to execute.
To avoid the time out issue, we will modify the package. Add one wrapper procedure in the existing package:
The complte modified Package looks like as the following:
--------------------------------------------------------
-- DDL for Package PKG_EMP_DTLS
--------------------------------------------------------
CREATE OR REPLACE PACKAGE "<schema>"."PKG_EMP_DTLS" AS
PROCEDURE proc_schedule_job (p_employee_id IN emp.emp_id%TYPE,
p_employee_name IN emp.emp_name%TYPE,
p_pkg_name IN VARCHAR2,
p_proc_name IN VARCHAR2,
p_job_status OUT VARCHAR2);
PROCEDURE proc_insert_emp (p_emp_id IN emp.emp_id%TYPE,
p_emp_name IN emp.emp_name%TYPE,
status OUT VARCHAR2,
status_desc OUT VARCHAR2);
END PKG_EMP_DTLS;
/
--------------------------------------------------------
-- DDL for Package Body PKG_EMP_DTLS
--------------------------------------------------------
CREATE OR REPLACE PACKAGE BODY "<schema>"."PKG_EMP_DTLS" AS
PROCEDURE proc_schedule_job (
p_employee_id IN emp.emp_id%TYPE,
p_employee_name IN emp.emp_name%TYPE,
p_pkg_name IN VARCHAR2,
p_proc_name IN VARCHAR2,
p_job_status OUT VARCHAR2
) IS
v_job_action VARCHAR2(1000);
BEGIN
v_job_action := 'DECLARE
v_emp_id emp.emp_id%TYPE;
v_emp_name emp.emp_name%TYPE;
v_status VARCHAR2(10);
v_status_desc VARCHAR2(500);
v_quote VARCHAR2(10);
BEGIN
'|| p_pkg_name ||'.'|| p_proc_name || '(p_emp_id => ' || p_employee_id || ', p_emp_name => ''' || p_employee_name || ''', status => v_status, status_desc => v_status_desc);
END;';
DBMS_OUTPUT.PUT_LINE('=>' || v_job_action);
dbms_scheduler.create_job(job_name => 'emp_run_job',
job_type => 'PLSQL_BLOCK',
job_action => v_job_action,
start_date => systimestamp,
auto_drop => true,
enabled => true);
p_job_status := '0';
END;
PROCEDURE proc_insert_emp (p_emp_id IN emp.emp_id%TYPE,
p_emp_name IN emp.emp_name%TYPE,
status OUT VARCHAR2,
status_desc OUT VARCHAR2) AS
BEGIN
DECLARE
v_curr_time TIMESTAMP;
v_error VARCHAR2(100);
BEGIN
SELECT SYSTIMESTAMP INTO v_curr_time FROM DUAL;
DBMS_OUTPUT.PUT_LINE('Start:' || v_curr_time);
LOOP
EXIT WHEN v_curr_time + (300 * (1/86400)) <= SYSTIMESTAMP;
END LOOP;
DBMS_OUTPUT.PUT_LINE('End:' || SYSTIMESTAMP);
INSERT INTO emp (emp_id,emp_name,crt_upd_date_time)
VALUES (p_emp_id,p_emp_name,systimestamp);
status := '0';
v_error := 'No error';
EXCEPTION
WHEN OTHERS THEN
status := '-1';
v_error := SQLERRM;
status_desc := v_error;
END;
END proc_insert_emp;
END PKG_EMP_DTLS;
/Need to edit the flow to point to the wrapper procedure and also change the mapping to procedure.
[c] Call DB Procedure (updating):
[b] Map to DB Procedure (updating):
Now check the EMP table if new record is inserted.
* Note: In case you face any issue while calling wrapper stored procedure, you can debug after making the “auto_drop” feature of wrapper procedure to “false”; so that it won’t delete the scheduler job. After the run, check the Status in “Run Log” tab as highlighted below.
Point to note, when “auto_drop” is “true”, you won’t be able to see the job under Scheduler > Jobs.
………………………………………………………………………………………………
