Invoke long running PL/SQL asynchronously from OIC
Introduction:
There are some scenarios where we are supposed to call a PLSQL procedure, which may took 30min or more to execute. But in OIC we have a limitation of 5min timeout. The procedure execution will thereafter time out in that scenario. To overcome this limitation we have to call the package asynchronously. But we don’t have any such direct approach to achieve this. We are attempting to explain how we can accomplish it, in this blog.
Main Content:
We will be using DBMS Scheduler feature of database, where Package1 will call Package2 Asynchronously using DBMS_SCHEDULER.CREATE_JOB. Where Package1 is the wrapper package where Package2 is the actual long running package. Should it be necessary to verify whether Package 2’s execution was successful or not, you must keep track of the package’s state in a temporary table.
Prerequisites:
Before using Scheduler, we must first enable DBMS Scheduler for the desired schema. For that you must execute below command from admin user.
- grant create job to XXXX;
- grant execute on dbms_scheduler to XXXX;
- grant select on sys.dba_scheduler_job_run_details to XXXX;
where XXXX is the schema.
Step by Step Process:
In our usecase We have to call procedure XX_LOAD_DATA_PKG. XX_LOAD_DATA_PROC which may take more than an hour to execute. Thus, we will first develop a wrapper package to which OIC will provide the instance ID as an input. That instance ID is going to be used as identifier if the final package is completed or not. Then using DBMS_SCHEDULER.CREATE_JOB function, wrapper package will invoke child package asynchronously. Consequently, the wrapper package will initiate a task and reply to integration synchronously, regardless of the condition of the child package.
I am sharing a sample wrapper package XX_CALL_AYSNC_PKG, which calling the actual package using DB scheduler. Also you can see I am passing the instance id coming as input from OIC to child package.
Now we have created a temp table XX_ASYNC_JOB_STATUS which will keep tracking the status of latest execution. At the start of the package, we added one entry to the table with the instance ID and job name with status as “In Progress”. Also at the end of the package when all activity completed, we have updated the status as “Completed” for that instance id. In case of any failure, we are also updating the status as “Failed” in the Exception block.
So if we invoke the package multiple time with unique instance id, records will be stored like below. Where yellow marked “In Progress” row is current execution. Additional records that are described, with a status of either completed or failed execution. The execution time report for each scenario can be generated with the aid of this table.
Now lets create the integration, where we will invoke the wrapper package XX_CALL_AYSNC_PKG. Then will create a while loop till the status is Completed or Failed.
We will continuously verify the job’s state from the table XX_ASYNC_JOB_STATUS on each iteration. Once the job completed, we will be storing the value in a local variable and the flow will come out from the while loop.
The next course of action can be taken once you have the package’s operating state.
In Conclusion:
So this could be a simple solution to overcome the limitation of calling a long running packaged from OIC. Specially when we have to deal with large amount of data from Oracle DB, this kind of solution can be useful to achieve it.
I hope this blog will be helpful to you when you implement it in the future. Please feel free to share your thoughts. Thank You.
➡️ Please follow me on LinkedIn
I am an accomplished professional with 15 years of experience in Oracle Integration. Throughout my career, I have been deeply involved in architecting and implementing integration solutions for various organizations, enabling them to seamlessly connect and optimize their business requirements & Cloud Migration.
