When running a forall loop in PL/SQL, is it necessary to commit afterwards?

plsql oracle10g oracle11g forall
kg. picture kg. · Mar 9, 2011 · Viewed 7.8k times · Source

Does a PL/SQL forall loop commit automatically at certain intervals, or do I need to commit after the loop?

Oracle 10g and 11g

FORALL i IN x.FIRST .. x.LAST
    delete from table where 1=1;

I've currently got a pl/sql script that does a bulk collect, and then runs 3 different forall loops that iterate over the collection. I am currently committing after each forall loop completes, with a commit statement in the script. Is this needed? Does it slow down execution, especially when the collection has several million entries?

Thanks

Answer

Eddie Awad picture Eddie Awad · Mar 9, 2011

You have to explicitly commit after a FORALL. After all, you are performing high speed DML using FORALL, and as you (should) know, DML does not commit automatically.

Also, even hough FORALL iterates through all the rows of a collection, it is not a loop, it is a statement. It has neither a LOOP nor an END LOOP statement.

Related questions

Oracle PL/SQL string compare issue

I have the following Oracle PL/SQL codes that may be rusty from you guys perspective: DECLARE str1 varchar2(4000); str2 varchar2(4000); BEGIN str1:=''; str2:='sdd'; IF(str1<>str2) THEN dbms_output.put_line('The two strings is …

Read More
How to use Oracle global temporary table?

I am attempting to use an Oracle global temporary table without physically creating a table in the database. The following code is not working. Can someone please explain the proper way to use global temporary tables? declare global temporary table …

Read More
What is the difference between USER() and SYS_CONTEXT('USERENV','CURRENT_USER')?

In an Oracle Database, what are the differences between the following: user() sys_context('USERENV', 'CURRENT_USER') sys_context('USERENV', 'SESSION_USER') Are these also possible related values to whatever 'the current user' is? sys_context('USERENV', 'CURRENT_SCHEMA') sys_…

Read More