Scroll to top
Please assign a menu to the primary menu location

Large Data Processing using OIC, Bulk Collect and For All


Dhruvil Pandya - November 21, 2022 - 0 comments

Reading Time: 4 minutes

In this blog, we have covered the Bulk Collect and For All option along with Context switches which shares a PL/SQL run time engine. So, let us start with Bulk Collect first.

What is BULK COLLECT?

The main purpose of using “BULK COLLECT” is to increase the performance of the process by reducing the interaction between database and PL/SQL engine. Which helps us when we have lots of data in that case, we can use BULK COLLECT to reduces context switches between SQL and PL/SQL engine which will allows SQL engine to fetch all the records at once.

It can be used with all three types of collections: associative arrays, nested tables, and arrays. You can fetch into individual collections (one for each expression in the SELECT list) or a single collection of records.

Syntax:

In the above given Syntax Bulk Collect is used collect the data from ‘SELECT’ and ‘FETCH’ statement.

syntax1

What is For All?

The FORALL Helps us to perform the Data Manipulation Language (DML) operations on data in bulk. It is very similar to the FOR loop statement except that in FOR loop process happen at record level whereas in FORALL there is no LOOP concept. Instead of that the entire data which is present in the given range is processed at the same time. Which helps us in performance tuning. By reducing the number of context switches.

Syntax:

syntax2

Context Switches

When a Pl/SQL procedure runs which incorporates both PL/SQL and SQL statements then all the PL/SQL statements are run by the PL/SQL statement executor and all the SQL statements are run by the SQL statement executor. So, when a specific statement is encountered by the PL/SQL run Time Engine it passes that statement to their respective Statement executor. This transfer of control between PL/SQL runtime Engine and SQL Engine is understood as Context Switches. and every time this context switching happening it slows down the whole process. For reducing this context Switches we use Bulk Collect and For All to stop overheat. With the help of the below diagram you can understand the concept of Context switches.

runtime

Limit Clause

If we are fetching a bulk load of data, then bulk collect can consume a lots of session memory depending on the size of data, and this can raise an error depending on the specification of the system on which you are running the process. To solve this problem in the Oracle Data base we have. A Limit Clause for bulk collect with the help of the limit clause you can easily overcome this problem by limiting the number of rows fetched during the bulk collect this will increase our performance and our section memory will never run out because of limit clause.

Related Article: How To Use Slack Adapter To Send Messages With OIC?

Syntax

syntax3

OIC Use cases of Bulk Collect and for All

In the given example we are fetching the data from OIC and

In the below integration I am getting the data from ERP and then inserting it into Data base then retrieving the data with help of bulk collect from the DB to write a file into SFTP location.

usecase

In this integration we are invoking two procedures stored in our DB (Marked in red) with help of DB adapter which are using For-all and bulk collect for inserting and retrieving the data from the DB.

oracle adapter

Procedure Code

PROCEDURE XXTRAINING_ERPTASK_PRC (P_ERP_TBL IN

XXTRANING_ERPTASK_TBL_TYPE, P_STATUS OUT VARCHAR2,P_ERROR_MESSAGE OUT VARCHAR2)

IS

l_error_code varchar2(255);

l_error_message varchar2(255);

BEGIN

FORALL i IN 1..P_ERP_TBL.COUNT

INSERT INTO XXTRAINING_ERPTASK (

PO_NUMBER,

INVOICE_NUMBER,

CREATED_BY,

INVOICE_AMOUNT,

INVOICE_DATE,

AMOUNT,

INSTANCE_ID)

VALUES (P_ERP_TBL(i).PO_NUMBER,

P_ERP_TBL(i).INVOICE_NUMBER,

P_ERP_TBL(i).CREATED_BY,

P_ERP_TBL(i).INVOICE_AMOUNT,

P_ERP_TBL(i).INVOICE_DATE,

P_ERP_TBL(i).AMOUNT,

P_ERP_TBL(i).INSTANCE_ID);

COMMIT;

p_status := ‘Successful’;

EXCEPTION

WHEN OTHERS

THEN

l_error_code := SQLCODE;

l_error_message := SQLERRM;

P_STATUS := ‘Error’;

P_ERROR_MESSAGE := ‘Error Code: ‘ || l_error_code || ‘ error message: ‘ || l_error_message;

END;

END XXTRAINING_ERPTASK_PKG;

In the above code we are using For all to preform the insert operation on table XXTRAINING_ERPTASK

oracle adapter2

create or replace PACKAGE BODY “XXTRAINING_ERPTASK_PKG” AS

PROCEDURE RETRIEVE_ERP_DATA( P_INSTANCE_ID IN VARCHAR2,

P_ERP_TBL  OUT XXTRANING_ERPTASK_TBL_TYPE,

STATUS OUT VARCHAR2)

IS

BEGIN

Select   XXTRAINING_ERPTASK_REC_TYPE

(

PO_NUMBER ,

INVOICE_NUMBER,

CREATED_BY,

INVOICE_AMOUNT ,

INVOICE_DATE ,

AMOUNT,

INSTANCE_ID

)

BULK COLLECT INTO P_ERP_TBL from xxtraining_erptask

where INSTANCE_ID=P_INSTANCE_ID;

STATUS:=’SUCCESS’;

COMMIT;

EXCEPTION WHEN OTHERS THEN

STATUS:=’FAIL’;

END;

END XXTRAINING_ERPTASK_PKG;

In the above code we are using For all to preform the insert operation on table XXTRAINING_ERPTASK

In the above code we are using Bulk-Collect to retrieve the data from table XXTRAINING_ERPTASK

Fact-We preformed one test of our own to check the speed of For-All which we are using to insert data of a file From sftp location to load it in the DB  and as it turns out that it takes only 14 sec to load the data to the database if the file contains 60945 rows.

final

 

final2We hope this blog was informational and provided the right guidance for Bulk Collect and For All in PL/SQL. If you have any suggestions or questions regarding the content of this blog, please get in touch with us at business@conneqtiongroup.com or Contact Us today.

Author avatar

Dhruvil Pandya

Dhruvil is a Marketing and Strategy Manager at Conneqtion Group, a Oracle iPaaS and Process Automation company. He comes with a vast experience of working in the Marketing, Branding and Content Marketing in various industries including IT service, SaaS, Natural Gases & Equipments, Food and United Nations. He has completed his MBA in Marketing from Western Sydney University and has worked for more than 7 years with Indian and Australian startups. He has a good acumen of business and marketing in the Indian startup ecosystem and has worked with BOC Gases, a leading Gas company handling their APAC Marketing.

Related posts

Post a Comment

Your email address will not be published. Required fields are marked *