The FORALL statement allows us to pass multiple SQL statements all together (in bulk) to the SQL engine.
Syntax:
FORALL index_row IN
[ lower_bound ... upper_bound |
INDICES OF indexing_collection |
VALUES OF indexing_collection
]
[ SAVE EXCEPTIONS ]
sql_statement;
Cursor Attributes :
SQL%FOUND : TRUE : If last execution of the sql statement modifies atleast one.
SQL%ROWCOUNT : Returns the total number of rows processed by all executions of the SQL statement,not just the last statement.
SQL%BULK_ROWCOUNT: Returns a collection that tells us the number of rows processed by each SQL statement via FORALL.
Difference between SQL%ROWCOUNT & SQL%BULK_ROWCOUNT:
SQL%ROWCOUNT : Grand total
SQL%BULK_ROWCOUNT: Individual Count for every sql statement in the FORALL.
SQL%BULK_ROWCOUNT pseudo-collection use the same subscripts derived from FORALL Statement.
If any update statement through FORALL, updates more than one row (in a specific iteration) then the SQL%BULK_ROWCOUNT value at that subscript will be equal to the number of rows updated.
EXAMPLE:
CREATE TABLE FORALL_TABLE_TEST(ITERATION_NUM NUMBER);
DECLARE
TYPE NUM_T IS TABLE OF NUMBER;
COLLECTION_NUM_T NUM_T := NUM_T ();
START_TIME NUMBER;
END_TIME NUMBER;
EXECUTION_TIME NUMBER;
BEGIN
FOR INDX IN 1 .. 10000
LOOP
COLLECTION_NUM_T.EXTEND;
COLLECTION_NUM_T (INDX) := INDX * 10;
END LOOP;
DBMS_OUTPUT.PUT_LINE ('COLLECTION COUNT:' ||
COLLECTION_NUM_T.COUNT
);
-- TABLE INSERT VIA REGULAR LOOPING
START_TIME := DBMS_UTILITY.GET_TIME ();
FOR I IN COLLECTION_NUM_T.FIRST .. COLLECTION_NUM_T.LAST
LOOP
INSERT INTO FORALL_TABLE_TEST (ITERATION_NUM)
VALUES (COLLECTION_NUM_T (I));
END LOOP;
END_TIME := DBMS_UTILITY.GET_TIME ();
DBMS_OUTPUT.PUT_LINE ('INSERT:SQL%ROWCOUNT:' ||
SQL%ROWCOUNT
);
COMMIT;
EXECUTION_TIME := (END_TIME - START_TIME);
DBMS_OUTPUT.PUT_LINE ('Time taken:LOOP INSERT:' ||
EXECUTION_TIME
);
DELETE FORALL_TABLE_TEST;
DBMS_OUTPUT.PUT_LINE ('DELETE:SQL%ROWCOUNT:' ||
SQL%ROWCOUNT
);
COMMIT;
-- TABLE INSERT VIA FORALL
START_TIME := DBMS_UTILITY.GET_TIME ();
FORALL INDX IN COLLECTION_NUM_T.FIRST .. COLLECTION_NUM_T.LAST
INSERT INTO FORALL_TABLE_TEST
VALUES (COLLECTION_NUM_T (INDX));
END_TIME := DBMS_UTILITY.GET_TIME ();
EXECUTION_TIME := (END_TIME - START_TIME);
DBMS_OUTPUT.PUT_LINE ('FORALL:SQL%ROWCOUNT:' ||
SQL%ROWCOUNT
);
DBMS_OUTPUT.PUT_LINE ('SQL%BULK_ROWCOUNT:' ||
SQL%BULK_ROWCOUNT (10000)
);
DBMS_OUTPUT.PUT_LINE ('Time taken:FORALL:' ||
EXECUTION_TIME
);
DELETE FORALL_TABLE_TEST;
DBMS_OUTPUT.PUT_LINE ('FORALL:SQL%ROWCOUNT:' ||
SQL%ROWCOUNT
);
COMMIT;
END;
What happens when one of the DML statement fails in FORALL:
1. FORALL stops executing.
2. Any Previous DML operations in that FORALL that already executed without an error are not rolledback.
EXAMPLE:
CREATE TABLE FORALL_TABLE_TEST (ITERATION_NUM NUMBER UNIQUE);
DECLARE
TYPE NUM_T IS TABLE OF NUMBER;
COLLECTION_NUM_T NUM_T := NUM_T ();
START_TIME NUMBER;
END_TIME NUMBER;
EXECUTION_TIME NUMBER;
BEGIN
-- Collection values will be 0 to 19 and it will have 5 sets of them.
FOR INDX IN 1 .. 100
LOOP
COLLECTION_NUM_T.EXTEND;
COLLECTION_NUM_T (INDX) := mod(INDX,20);
END LOOP;
-- TABLE INSERT VIA FORALL
START_TIME := DBMS_UTILITY.GET_TIME ();
FORALL INDX IN COLLECTION_NUM_T.FIRST .. COLLECTION_NUM_T.LAST
INSERT INTO FORALL_TABLE_TEST
VALUES (COLLECTION_NUM_T (INDX));
END_TIME := DBMS_UTILITY.GET_TIME ();
EXECUTION_TIME := (END_TIME - START_TIME);
DBMS_OUTPUT.PUT_LINE ('FORALL:SQL%ROWCOUNT:' || SQL%ROWCOUNT);
DBMS_OUTPUT.PUT_LINE ('Time taken:FORALL:' || EXECUTION_TIME);
Exception
When others then
NULL;
END;
Select * from FORALL_TABLE_TEST;
We see 0 to 19 got inserted and then it raised an exception (unique constraint violation) while executing the FORALL statement but all the successful insert statements didn't get rolled back.
Can we instruct oracle to pass the errors and process till the end Via FORALL ? YES
How? Using SAVE EXCEPTIONS Clause.
SAVE EXCEPTIONS:
When we use SAVE EXCEPTIONS with in the FORALL statement, it saves the exceptions and once completion on FORALL, it raises ORA-24381 exception.
All the exceptions will be saved to pseudo-collection, SQL%BULK_EXCEPTIONS.
SQL%BULK_EXCEPTIONS :
This pseudo-collection has two fields
1. ERROR_INDEX: This is same as the index at which the sql statement raised an error.
2. ERROR_CODE : The error code is saved as positive number.(multiply by -1)
EXAMPLE:
DECLARE
TYPE NUM_T IS TABLE OF NUMBER;
COLLECTION_NUM_T NUM_T := NUM_T ();
START_TIME NUMBER;
END_TIME NUMBER;
EXECUTION_TIME NUMBER;
BEGIN
-- Collection values will be 0 to 19 and it will have 5 sets of them.
FOR INDX IN 1 .. 100
LOOP
COLLECTION_NUM_T.EXTEND;
COLLECTION_NUM_T (INDX) := mod(INDX,20);
END LOOP;
-- TABLE INSERT VIA FORALL
START_TIME := DBMS_UTILITY.GET_TIME ();
FORALL INDX IN COLLECTION_NUM_T.FIRST .. COLLECTION_NUM_T.LAST
SAVE EXCEPTIONS
INSERT INTO FORALL_TABLE_TEST
VALUES (COLLECTION_NUM_T (INDX));
END_TIME := DBMS_UTILITY.GET_TIME ();
EXECUTION_TIME := (END_TIME - START_TIME);
DBMS_OUTPUT.PUT_LINE ('FORALL:SQL%ROWCOUNT:' || SQL%ROWCOUNT);
DBMS_OUTPUT.PUT_LINE ('Time taken:FORALL:' || EXECUTION_TIME);
Exception
When others then
FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT
LOOP
dbms_output.put_line('error_index:'||sql%bulk_exceptions(i).error_index);
dbms_output.put_line('error_code:'||sql%bulk_exceptions(i).error_code);
dbms_output.put_line('error message:'||sqlerrm(sql%bulk_exceptions(i).error_code*-1));
END LOOP;
END;
FORALL with NON-SEQUENTIAL COLLECTIONS:
Prior to Oracle 10g, the binding collections must be densely packed with no gaps in it.
we get the error:
ORA-22160: element at index [X] does not exist.
EXAMPLE:
DECLARE
TYPE NUM_T IS TABLE OF NUMBER;
COLLECTION_NUM_T NUM_T := NUM_T ();
START_TIME NUMBER;
END_TIME NUMBER;
EXECUTION_TIME NUMBER;
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE FORALL_TABLE_TEST';
FOR INDX IN 1 .. 100
LOOP
COLLECTION_NUM_T.EXTEND;
COLLECTION_NUM_T (INDX) := INDX;
END LOOP;
COLLECTION_NUM_T.DELETE(25);
COLLECTION_NUM_T.DELETE(50);
COLLECTION_NUM_T.DELETE(75);
COLLECTION_NUM_T.DELETE(100);
-- TABLE INSERT VIA FORALL
START_TIME := DBMS_UTILITY.GET_TIME ();
FORALL INDX IN COLLECTION_NUM_T.FIRST .. COLLECTION_NUM_T.LAST
INSERT INTO FORALL_TABLE_TEST
VALUES (COLLECTION_NUM_T (INDX));
END_TIME := DBMS_UTILITY.GET_TIME ();
EXECUTION_TIME := (END_TIME - START_TIME);
DBMS_OUTPUT.PUT_LINE ('FORALL:SQL%ROWCOUNT:' || SQL%ROWCOUNT);
DBMS_OUTPUT.PUT_LINE ('Time taken:FORALL:' || EXECUTION_TIME);
EXCEPTION
When others then
dbms_output.put_line(SQLERRM);
END;
ERROR: ORA-22160: element at index [25] does not exist
But starting ORACLE 10g, we can use
1.INDICES OF
2.VALUES OF
This is how it works:
1.INDICES OF
We build a driving collection, which will be populated with the index numbers same as index numbers of binding collection.
2.VALUES OF
We build a driving collection, which will be populate with the index numbers of the binding collection.
EXAMPLES:
DECLARE
TYPE NUM_T IS TABLE OF NUMBER;
COLLECTION_NUM_T NUM_T := NUM_T ();
START_TIME NUMBER;
END_TIME NUMBER;
EXECUTION_TIME NUMBER;
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE FORALL_TABLE_TEST';
FOR INDX IN 1 .. 100
LOOP
COLLECTION_NUM_T.EXTEND;
COLLECTION_NUM_T (INDX) := INDX;
END LOOP;
COLLECTION_NUM_T.DELETE(25);
COLLECTION_NUM_T.DELETE(50);
COLLECTION_NUM_T.DELETE(75);
COLLECTION_NUM_T.DELETE(100);
-- TABLE INSERT VIA FORALL
START_TIME := DBMS_UTILITY.GET_TIME ();
FORALL INDX IN INDICES OF COLLECTION_NUM_T
INSERT INTO FORALL_TABLE_TEST
VALUES (COLLECTION_NUM_T (INDX));
END_TIME := DBMS_UTILITY.GET_TIME ();
EXECUTION_TIME := (END_TIME - START_TIME);
DBMS_OUTPUT.PUT_LINE ('FORALL:SQL%ROWCOUNT:' || SQL%ROWCOUNT);
DBMS_OUTPUT.PUT_LINE ('Time taken:FORALL:' || EXECUTION_TIME);
EXCEPTION
When others then
dbms_output.put_line(SQLERRM);
END;
No comments:
Post a Comment