OIC - How to call DB Procedure Asynchronously

Usecase: Here, we will create a db package. In the package, we will create 2 procedures. 1st one, wrapper proc which will call the 2nd main proc asynchronously using dbms_scheduler.create_job() function.

**call the procedure via dbms_scheduler.create_job for a single immediate run. Parameter values for create_job to support single immediate run:

Job_name: a unique name say, 'async_main_proc' concatenated with attribute1 to prevent concurrency conflict

Job_type: PLSQL_BLOCK

Job_action: plsql block invoking main procedure async way.

Enabled: TRUE ( DEFAULT is false)

Auto_drop: TRUE ( default is TRUE)

Why we are creating/invoking async procedure:

Suppose we are invoking a db service procedure and if it takes more than 5 minutes to complete, then the process will be timed out. Even if the proc does not have OUT parameter, proc will be executed in synchronous mode and it waits for the completion of procedure execution. To overcome this limitation, first we need to create a wrapper procedure which calls the main procedure using dbms scheduler option inside the wrapper procedure.

Detailed steps:

Create a table:

Create table async_main_proc(

Attribute1 varchar2(200),

Attribute2 varchar2(200));

DB package specification:

Create or replace package XX_ASYNC_DEMO_PKG
As
     Procedure wrapper_main_proc(
       Attribute1 in varchar2,
       Attribute2 in varchar2);
     Procedure async_main_proc(
       Attribute1 in varchar2,
       Attribute2 in varchar2);
End xx_async_demo_pkg;

DB package Body:

Create or replace package body xx_async_demo_pkg
As
     Procedure wrapper_main_proc(
       Attribute1 in varchar2,
       Attribute2 in varchar2)
       As
         Begin
         Dbms_output.put_line('--- start wrapper proc ---' || systimestamp);
         --A separate procedure called asynchronously
         Dbms_output.put_line('before async_main_proc' || systimestamp);
         Dbms_scheduler.create_job(
           Job_name => 'async_main_proc'||Attribute1,
           Job_type => 'PLSQL_BLOCK',
           Job_action =>
           'BEGIN 
 xx_async_demo_pkg.async_main_proc('''||Attribute1||''','''||attribute2||''');
           End;',
           Enabled => TRUE,
           Auto_drop => TRUE,
           Comments => 'call main proc steps');
         Dbms_output.put_line('after async_main_proc' || systimestamp);
         Dbms_output.put_line('--- end wrapper_proc' || systimestamp);
End;

     Procedure async_main_proc(
       Attribute1 in varchar2,
       Attribute2 in varchar2)
       As
         Sql_stmt varchar2(200);
         Begin
           Dbms_output.put_line('--- start async_main_proc ---' || systimestamp);
           --Dbms_session.sleep(300);
           Sql_stmt := 'insert into      async_main_proc(attribute1,attribute2) values(:1,:2)';
           Execute immediate sql_stmt using attribute1,attribute2;
           Dbms_output.put_line('--- end async_main_proc ---' || systimestamp);
     End;
End xx_async_demo_pkg;

Test the procedure from db:

Exec xx_async_demo_pkg.wrapper_main_proc('test','abc')

OIC integration flow: Create the Database connection and call the package and wrapper procedure using db adapter.

Test from DB:

Reference:

• https://oratable.com/running-procedures-asynchronously-with-oracle-job-scheduler/
• https://techsupper.com/2022/10/how-to-call-db-procedure-asynchronously-from-oic.html