Oracle PL/SQL: Using COMMIT in a PL/SQL loop - Oracle recommends that you COMMIT less frequently within a PL/SQL loop to prevent ORA-1555 (Snapshot

Quick Search:

Oracle PL/SQL: Using COMMIT in a PL/SQL loop

Jump to:

Category: >> Oracle PL/SQL >> Using COMMIT in a PL/SQL loop

<< last

next >>

Snippet Name: Using COMMIT in a PL/SQL loop

Description: Oracle recommends that you COMMIT less frequently within a PL/SQL loop to prevent ORA-1555 (Snapshot too old) errors.

The higher the frequency of commit, the sooner the extents in the undo/ rollback segments will be cleared for new transactions, causing ORA-1555 errors.

To fix this problem you can rewrite code as shown in the example.

Also see:
» Nested Loops: loop through tables

Comment: (none)

Language: PL/SQL
Highlight Mode: PLSQL
Last Modified: March 06th, 2009

-- original code, too many COMMITs
 
FOR records IN my_cursor LOOP
   ...DO something...
   COMMIT;
END LOOP;
COMMIT;
 
 
-- rewritten code that commits every 10000 records
 
FOR records IN my_cursor LOOP
   ...DO something...
   i := i+1;
   IF MOD(i, 10000) = 0 THEN    -- Commits once per 10000 records
      COMMIT;
   END IF;
END LOOP;
COMMIT;