About This Recipe
This recipe demonstrates how to import supplier data asynchronously from Oracle ERP Cloud to Oracle Autonomous Transaction
Processing database. To achieve this, a wrapper PL/SQL procedure is created in the database that employs DBMS_SCHEDULER.CREATE_JOB to invoke the main package asynchronously.
There might be situations where PL/SQL packages need to be invoked for data synchronization. Due to the complexity of the logic, these PL/SQL procedures might take 20 minutes or more to execute. As Oracle Integration has a timeout limit of 4 minutes for on-premises databases and 5 minutes for public databases, the integration will fail if the PL/SQL procedure exceeds these time limits. This recipe demonstrates strategies to avoid timing issues and ensure successful data synchronization.
To use the recipe, you must install the recipe and configure the connection and other resources within the recipe.
RUN_SCHEDULER_JOB: It accepts the data in a table type input parameter, inserts all the data into the table, and then calls the second package usingDBMS_SCHEDULER.CREATE_JOB. This procedure is called from Oracle Integration and accepts all the data in bulk.PROCESS_SUPPLIERS: It is being called from theRUN_SCHEDULER_JOBprocedure. This is the main PL/SQL procedure that performs all the complex logic on the data that is inserted by the first package, and finally updates the data with the statusVALID/INVALID.
A scheduled integration fetches the supplier records in chunks using the Oracle ERP Cloud Adapter and calls the RUN_SCHEDULER_JOB PL/SQL procedure. The invocation does not wait for the response and moves to the next iteration to get another chunk of supplier records. This works until all the supplier records are inserted into the database asynchronously.