Run Child integration in batches

In this tutorial, we will see a detailed description of how to run an ATP procedure or a child integration in batches by invoking via OIC.

Use case: Let’s say we have an inbound integration that is used to load invoices in fusion and the integration is calling DB package and another child integration for performing certain validations and other tasks. So, from OIC we invoke this procedure and another child integration and in case of large input files the package and another child integration is taking longer time to execute hence the integration is getting timed out. The error message looks something like this:

Got error while calling PLSQL Inbound package - com.oracle.bpel.client.BPELFault: faultName: {{http://xmlns.oracle.com/cloud/adapter/atpdatabase/callInboundPkgMainProcedure_REQUEST}serviceInvocationError} 
parts: {{ null=faultsrc:serviceInvocationErrorICS runtime execution error Operation Timedout A timeout has occured while trying to perform the Database operation. The Database operation took more time to complete. 
serviceInvocationError Please tune the stored procedure to return the response with in the time limits. This exception is considered not retriable, likely due to a modelling mistake. LimitExceededException

Solution: We use grouping logic, assign groupID for each record and run it in groups.

Add two columns to your table. One will be UNIQUE_ID which will be a sequence and another column GROUP_ID which will be populated using the below procedure.

PROCEDURE assign_groupid (
    p_instance_id   IN    NUMBER,
    p_status_out    OUT   VARCHAR2,
    p_err_msg_out   OUT   VARCHAR2
    )
    AS
    x_group_size    NUMBER := 100;
    x_count         NUMBER;
    x_from_seq      NUMBER := 0;
    x_to_seq        NUMBER := 0;
    x_group_id      NUMBER;

BEGIN
    SELECT
        COUNT(*)
    INTO x_count
    FROM
        my_table
    WHERE
        FLOW_ID = p_instance_id;

    SELECT
        MIN(unique_id)
    INTO x_from_seq
    FROM
        my_table
    WHERE
        FLOW_ID = p_instance_id;

    SELECT
        MAX(unique_id)
    INTO x_to_seq
    FROM
        my_table
    WHERE
        FLOW_ID = p_instance_id;

    WHILE x_from_seq < x_to_seq LOOP
        x_group_id := my_seq.nextval;
        FOR i IN 0..x_group_size LOOP
            UPDATE my_table
            SET
               group_id = x_group_id
            WHERE
                unique_id = x_from_seq
                AND FLOW_ID = p_instance_id;

            x_from_seq := x_from_seq + 1;
            EXIT WHEN x_from_seq > x_to_seq;
        END LOOP;
    END LOOP;
    COMMIT;
    p_status_out := 'S';
  EXCEPTION
    WHEN OTHERS THEN
        p_status_out := 'E';
        p_err_msg_out := sqlerrm;
END assign_groupid;

Here, my_table is the name of the table in which OIC would have loaded data in the previous step, p_instance_id is the instance ID of that integration run, UNIQUE_ID is a sequence that will be provided while creating the table.

Now in order to run our main procedure or our child integration in groups first we have to call the above procedure to assign the GROUP_ID column and then use a for each loop and call the main procedure or our child integration in groups. The OIC flow will look as shown in the below diagram:

AssigngroupID will assign the GROUP_ID column, getgroupID will get the distinct GROUP_ID from the table and then we use a for each loop which will run for each unique value of GROUP_ID and inside this loop, we can invoke our main procedure or our child integrations by passing this GROUP_ID.

If you like the article please like, comment, and share.

Also, please subscribe to our YouTube channel to learn more about Oracle stacks.

Enter your keyword

post

Run Child integration or PL/SQL procedure in batches in Oracle Integration

Further Readings