FORALL is a key performance feature of PL/SQL. It helps you avoid row-by-row processing of non-query DML (insert, update, delete, merge) from within a PL/QL block. Best of all, almost always, is to do all your processing entirely within a single SQL statement. Sometimes, however, that isn't possible (for example, you need to sidestep SQL's "all or nothing" approach) or simply too difficult (not all of us have the insane SQL writing skills of a Tom Kyte or a Chris Saxon or a Connor McDonald ). To dive in deep on FORALL, check out any of the following resources: FORALL documentation Videos at Practically Perfect PL/SQL Tim Hall on Bulk Binds In this post, I am going to focus on special features of FORALL that make it easy to work with space collections: the INDICES OF and VALUES OF clauses. Typical FORALL Usage with Dense Bind Array Here's the format you will most commonly see with FORALL: the header looks just like a numeric FOR loop, but notice: no l...
For the last twenty years, I have managed to transform an obsession with PL/SQL into a paying job. How cool is that?