Oracle FORALL statement is introduced for executing multiple DML operations in a single SQL switch. This is comparatively faster than for loop statements. DML statements like INSERT, UPDATE, or DELETE have different values on WHERE and VALUES clauses.
In such cases, data bind with SQL and those queries are executed by the Oracle SQL engine. Oracle PL/SQL is used to write conditional programming. SQL contains between or inside the PL/SQL blocks. Oracle PL/SQL blocks are executed by the PL/SQL engine in Oracle.
But SQL DML statements executed by SQL engine therefore have to context switch between SQL and PL/SQL engines to complete the full code block. FOR LOOP is a PL/SQL statement but if there is a DML statement inside the for loop and if there are millions of records for this loop then millions of context switches to complete the loop. That takes a lot of time. There FORALL reduce lot of time when executing code blocks.
Let’s see what the context switch of a simple example is. There are a lot of examples below in the article explaining Oracle FORALL.
Context Switches and Performance
Oracle code is not as simple as just for SQL queries. In most cases, the PLSQL program includes both PL/SQL and SQL statements. PL/SQL engine executes pl/SQL blocks and SQL engine executes SQL statements.
When executing a PL/SQL block if the engine encounters a SQL statement, then stop the PLSQL engine and pass SQL statements to the SQL engine. Then SQL engine executes the statement and returns the information to the PL/SQL engine. This happens every time once meet a SQL statement inside the PL/SQL block.
This is called Context Switching between Engines. But reduces the overall performance in the code block. This is an increase in LOOPs in Oracle. Because loops are iterating a lot of circles executing SQL and PLSQL statements. FORALL and bulk collect in oracle are great solutions for those kinds of situations execute in a single fetch.
Syntax oracle FORALL
This is the syntax of executing FORALL statement for increasing the tax of all employees in the department. The example shows what is the minimum requirement of code to execute cthe ode block.
-- DECLARE TYPE collectionList IS VARRAY(15) OF NUMBER; -- define collection type of numbers employees NumList := collectionList (); -- create collection type of numbers. BEGIN ---- add required employee numbers who need to increase. ---- add lower and upper bound of limits. FORALL j IN 2..13 -- bulk-bind middle third of varray ---- DML statements to execute. Values are bind from collection. UPDATE emp SET tax = tax * 1.10 WHERE employee_id = employees(j); END; --
About Oracle FORALL
- FORALL is not a loop it is a statement that can be executed by a PL/SQL engine. It is to send a full statement in a single context switch.
- Each FORALL statement should contain a single DML statement.
- If you have multiple DML statements inside the For LOOP then you have to write separate FORALL for each.
- Index low and high values need to be defined.
- SAVE EXCEPTION is used to catch and trace errors inside the Oracle FORALL. If exceptions are handled already then FORALL executes until the final record. Failed records are caught by exception and those are available inside SQL%BULK_EXCEPTIONS.
Oracle FORALL example
Let’s have a simple example with Bulk Collect and FORALL. I take the Population Table to insert data and fetch data from there. My problem is to get all the people over 18 years and below age 60 and increase tax by 5% for each person.
Below is the sample table and insert script. I hope to insert 1000000 records.
--
CREATE TABLE population_table(
citizen_id VARCHAR2(50) ,
citizen_nic VARCHAR2(50) ,
citizen_tax NUMBER ,
citizen_city VARCHAR2(50),
citizen_name VARCHAR2(100) ,
citizen_age NUMBER ,
PRIMARY KEY(citizen_id)
);
declare
-- insert 1000000 records into database
i integer:=0;
type namesarray IS VARRAY(10) OF VARCHAR2(50);
names namesarray;
begin
names := namesarray('City_1', 'City_2', 'City_3', ' City_4', 'City_5','City_6',' City_7','City_8','City_9','City_10');
LOOP
i:= i+1;
INSERT INTO population_table (citizen_id, citizen_nic, citizen_tax,citizen_city, citizen_name,citizen_age)
VALUES ('CT-'||i,1000000+i, round(dbms_random.value(100,100000),0), names(round(dbms_random.value(1,10),0)),'CT-name'||i,round(dbms_random.value(1,100),0));
EXIT WHEN i > 999999; END LOOP;
end;
--How to update income tax by 5% in a regular way. We normally loop the selected dataset and update each record 1 by 1. We have millions of records in the table and want to execute DML operations.
Difference between for and FORALL in Oracle
--
DECLARE
BEGIN
DBMS_OUTPUT.PUT_LINE('Time Started: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
FOR population_rec
IN (SELECT citizen_tax,citizen_id
FROM population_table
WHERE citizen_age >18 AND citizen_age <60)
LOOP
UPDATE population_table pop
SET pop.citizen_tax = pop.citizen_tax +
pop.citizen_tax * 5/100
WHERE pop.citizen_id = population_rec.citizen_id;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Time Started: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
end;
--Think about how many context switches are in this code. If a query took 500000 records, there are over 500000 times of context switches. Below is the time for updating the record took time.
-- Time Started: 08-FEB-2025 06:43:30 Time ended: 08-FEB-2025 06:43:37 --
bulk collect Forall vs cursor in oracle
This example shows the use of curser and fetch keyword use to fetch data into the created variable. Then FORALL is used to update data like in the above example. The curser initiates in the declare section and is used inside the BEGIN.
--
DECLARE
TYPE population_ids_tab IS TABLE OF population_table.citizen_id%TYPE
INDEX BY PLS_INTEGER;
l_population_ids population_ids_tab;
CURSOR population_tab IS
SELECT citizen_id
FROM population_table
WHERE citizen_age >18 AND citizen_age <60;
BEGIN
DBMS_OUTPUT.PUT_LINE('Time Started: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
OPEN population_tab;
FETCH population_tab BULK COLLECT INTO l_population_ids;
CLOSE population_tab;
FORALL indx IN 1 .. l_population_ids.COUNT
UPDATE population_table pop
SET pop.citizen_tax = pop.citizen_tax + pop.citizen_tax * 5/100
WHERE pop.citizen_id = l_population_ids (indx);
DBMS_OUTPUT.PUT_LINE('Time ended: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
end;
Time Started: 08-FEB-2025 10:11:32
Time ended: 08-FEB-2025 10:11:33
--By looking at the time difference you can see time reduced to nearly 2 seconds. Earlier it was around 7 seconds. That means in this case it is improved more than 75% of the time. That is huge when you have millions of records in a table.
FORALL delete in Oracle
The below example shows the deletion of some of the population from the table. I show only people aged 1-5. This is executed from the table by a single call by the PLSQL engine.
--
DECLARE
TYPE population_ids_tab IS TABLE OF population_table.citizen_id%TYPE
INDEX BY PLS_INTEGER;
l_population_ids population_ids_tab;
CURSOR population_tab IS
SELECT citizen_id
FROM population_table
WHERE citizen_age >18 AND citizen_age <60;
BEGIN
DBMS_OUTPUT.PUT_LINE('Time Started: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
OPEN population_tab;
FETCH population_tab BULK COLLECT INTO l_population_ids;
CLOSE population_tab;
FORALL indx IN 1 .. l_population_ids.COUNT
UPDATE population_table pop
SET pop.citizen_tax = pop.citizen_tax + pop.citizen_tax * 5/100
WHERE pop.citizen_id = l_population_ids (indx);
DBMS_OUTPUT.PUT_LINE('Time ended: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
end;
--FORALL exception handling in oracle
SQL%BULK_EXCEPTIONS is used to track all the errors that raise when executing Oracle FORALL. If you would like to execute until the LOOP for bulk collect then use SAVE EXCEPTIONS enables an UPDATE, INSERT, or DELETE statement to continue
Then at least there is an error it will forward into %BULK_EXCEPTIONS and later be able to loop %BULK_EXCEPTIONS and able to track the issues on the code block. Below is a simple example for %BULK_EXCEPTIONS
Unhandled Exceptions in FORALL
IF the FORALL statement raises an error in the middle of the execution all the previous statements are rollback. There is nothing happens if your exceptions are not handled. The below example shows handle exceptions and those can be traced once after the FORALL execution.
--
DECLARE
TYPE population_ids_tab IS TABLE OF population_table.citizen_id%TYPE
INDEX BY PLS_INTEGER;
l_population_ids population_ids_tab;
CURSOR population_tab IS
SELECT citizen_id
FROM population_table
WHERE citizen_age >18 AND citizen_age <60;
errors NUMBER;
dml_errors EXCEPTION; -- declare our exception
PRAGMA EXCEPTION_INIT (dml_errors, -20001);
BEGIN
DBMS_OUTPUT.PUT_LINE('Time Started: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
OPEN population_tab;
LOOP
FETCH population_tab BULK COLLECT INTO l_population_ids LIMIT 60000;
EXIT WHEN l_population_ids.count = 0;
DBMS_OUTPUT.put_line(l_population_ids.count || ' rows');
FORALL indx IN 1 .. l_population_ids.COUNT
UPDATE population_table pop
SET pop.citizen_tax = pop.citizen_tax + pop.citizen_tax * 5/100
WHERE pop.citizen_id = l_population_ids (indx);
END LOOP;
CLOSE population_tab;
DBMS_OUTPUT.PUT_LINE('Time ended: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
RAISE_APPLICATION_ERROR(-20001, 'loop exeption.');
EXCEPTION
-- Figure out what failed and why
WHEN dml_errors THEN
errors := SQL%BULK_EXCEPTIONS.COUNT;
DBMS_OUTPUT.PUT_LINE('Number of errors that failed: ' || errors);
FOR i IN 1..errors LOOP
DBMS_OUTPUT.PUT_LINE('Error #' || i || ' occurred during '||
'iteration #' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
DBMS_OUTPUT.PUT_LINE('Error message is ' ||
SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;
end;
--Here I added code to manually raise an error. raise_application_error allows you to raise custom messages and error codes. The error code number should be between the -20000 to -20999 range.
DELETE and RETURN BULK COLLECT from FORALL
--
DECLARE
TYPE population_ids_tab IS TABLE OF population_table.citizen_id%TYPE
INDEX BY PLS_INTEGER;
l_population_ids population_ids_tab;
CURSOR population_tab IS
SELECT citizen_id
FROM population_table
WHERE citizen_age >1 AND citizen_age <8;
TYPE citizen_name_t IS TABLE OF population_table.citizen_name%TYPE;
citizen_name_ids citizen_name_t;
TYPE citizen_nic_t IS TABLE OF population_table.citizen_nic%TYPE;
citizen_nic_ids citizen_nic_t;
BEGIN
DBMS_OUTPUT.PUT_LINE('Time Started: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
OPEN population_tab;
FETCH population_tab BULK COLLECT INTO l_population_ids;
CLOSE population_tab;
FORALL indx IN 1 .. l_population_ids.COUNT
DELETE FROM population_table pop
WHERE pop.citizen_id = l_population_ids (indx)
RETURNING citizen_name, citizen_nic
BULK COLLECT INTO citizen_name_ids, citizen_nic_ids;
DBMS_OUTPUT.PUT_LINE ('Deleted ' || SQL%ROWCOUNT || ' rows:');
FOR i IN citizen_name_ids.FIRST .. citizen_name_ids.LAST
LOOP
DBMS_OUTPUT.PUT_LINE ( 'Person #' || citizen_name_ids(i) || ' with NIC #' || citizen_nic_ids(i) );
END LOOP;
DBMS_OUTPUT.PUT_LINE('Time ended: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
end;
--RECAP
FORALL design to reduce the number of context Switches. This is easily able to be used with BULK COLLECT. Also, this can be a good interview question on your interview or a good point to explain your knowledge about Oracle.
Oracle FORALL can execute millions of records at once. If you face a session memory problem, use LIMIT on BULK COLLECT. See our other article related to database indexes to improve your code performance.
Ref: Oracle DOC
