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.
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:
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.
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
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.
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.
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
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.
We 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 [email protected] or Contact Us today.
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.
